Query scope descrepancy between tag and script

[Steve Sommers]

I'm fairly certain I found a scope bug. I'm using CF11 and FW/1 and I have the following query -- the tag works, the cfscript equivilent triggers a "Table named rc.qSignerLinks was not found in memory" exception:

/* works*/

<cfquery name="local.qResultSet" result="local.qResult" dbtype="query">

    SELECT

        *

    FROM

        rc.qRSRecipient

    where

        [email] = <cfqueryparam value="#session.user.getEmail()#" cfsqltype="CF_SQL_VARCHAR" maxlength="50" />

</cfquery>

/* exception */

<cfscript>

    local.qObj = new query();

    local.qObj.setDBType("query");

    local.qObj.setSQL("

        SELECT

            *

        FROM

            rc.qRSRecipient

        where

            [email] = :email

    ");

    local.qObj.addParam( name="email",value="#session.user.getEmail()#",cfsqltype="CF_SQL_VARCHAR",maxlength="50" );

    local.qObjResult = local.qObj.execute();

    local.qResultSet= local.qObjResult.getResult().recordCount NEQ 0;

</cfscript>

[WolfShade]

Not sure if this will help, but there is a Ben Nadel post about CF9's "query.cfc" and using a cfscript qoq.  I hope it might present some insight.

I've never tried to run a standard query in cfscript, much less a QoQ in cfscript. 

HTH,

^_^

[Carl Von Stetten]

You might also try looking at the new queryExecute() function introduced in CF11 for script.  It usually works better than the query.cfc-based script approach.

[BKBK · Accepted solution]

Yes, Steve, that problem has been known for some years now. The usual fix is to add the resultset using setAttributes(), like this:

local.qObj.setAttributes(tbl = rc.qRSRecipient);

local.qObj.setSQL("SELECT  *  FROM tbl WHERE [email] = :email");

[BKBK]

Having said that, I still think you should file a bug report.

[Steve Sommers]

Thanks. I already recoded this particular occurrence but I'm sure I'll encounter this again as I move more of my code to cfscript.

[WolfShade]

I'm not sure I'd classify this as a bug.  I mean, granted, it does not act the same way as the tag-based CFQUERY, but not placing the query-to-be-queried within the same scope as the QoQ doesn't really seem like a bug, to me.

V/r,

^_^

[Carl Von Stetten]

I'm also not sure that's a bug - it's actually a sign of proper encapsulation.  Since new query() is instantiating an object from query.cfc (which comes with ColdFusion), the object won't know anything outside of itself.  So anything it needs to know must be explicitly passed to it (as BKBK​ indicated by using setAttributes() ).

Again, if possible, I'd switch over to using queryExecute() instead.  I don't think Adobe is planning on doing a lot to maintain the CFC implementations of script functions, as most if not all of them are now implemented as built-in functions.

-Carl V.

[Steve Sommers]

I am trying the queryExecute on a query of queries. How do I pass the table if I have it in the local scope? I have local.qTable as a query result set. I've tried the following:

SELECT * FROM local.qTable

I've tried adding table in the queryParams and queryOptions with the following:

SELECT * from :table

and

SELECT * from table

All have failed and the documentation I found leaves much to be desired in this area: ColdFusion Help | QueryExecute

[Carl Von Stetten]

Try this:

<cfscript>
    qTable = QueryNew("id,title" , "integer,varchar");
    queryAddRow(qTable, {"id"=1,"title"="Dewey defeats Truman"});
    qryResult = QueryExecute("SELECT * FROM qTable", {}, {dbtype="query"});
    WriteDump(qTable);
</cfscript>

Tested on CF11/CommandBox.

-Carl V.

[Steve Sommers]

That works but I'm using FW/1 and I believe the default variables scope is the CFC instance which is a shared instance. This might run into threading collisions. For now I copy the query result set to the request scope (rc in FW/1 context) and this is working, it just smells of slimy hack.

rc.qTable = local.qTable;

local.qryResult = QueryExecute("SELECT * FROM rc.qTable", {}, {dbtype="query"});

But then again, "rc" is using the default variables scope in this sample. Hmmm...

[Carl Von Stetten]

So your CFC is a singleton, like a service object?  In my FW/1 apps I would typically var qTable before using it, which puts it in the function's local scope.  I thought local scope was thread safe?

[Steve Sommers]

Unfortunately for me, it's a little more complex than that. I love FW/1 for it's leanest and low overhead but I incorporate "fuses" from fusebox using includes for small (at least I try to keep them small) qry and act modules. With includes for some reason you cannot declare "var xxx" as it squawks that var can only be used in a function -- even though the module is being included within a function. Another topic for another thread. I will experiment with this a little more. It seems close.

[Carl Von Stetten]

Can you "var" scope the variable above the include statement?  Will that carry through into your "fuse"?

[BKBK]

Definitely a bug. Following this code,

<cfquery name="rc.qRSRecipient" datasource="myDSN">

    SELECT

        *

    FROM

       myTbl

</cfquery>

<cfscript>

    local.qObj = new query();

    local.qObj.setDBType("query");

    local.qObj.setSQL("

        SELECT

            *

        FROM

            rc.qRSRecipient

        where

            [email] = :email

    ");

    local.qObj.addParam( name="email",value="#session.user.getEmail()#",cfsqltype="CF_SQL_VARCHAR",maxlength="50" );

    local.qObjResult = local.qObj.execute();

    local.qResultSet= local.qObjResult.getResult().recordCount NEQ 0;

</cfscript>

Coldfusion tells Steve Sommers, "Table named rc.qRSRecipient was not found in memory". This is at least inconsistent, if not wrong.

1) Coldfusion talks about a missing table. Whereas we're in a query of a query and so I would expect it to talk of a query.

2) The local variable rc.qRSRecipient should exist in memory as a result of the first query.

[WolfShade]

In a query of a query, since the "table" named is the first query, CF thinks of the query as a table, so, yeah, it'll say "table missing" instead of "query missing".  (Although I will admit, however, that since the dbtype is set to "query" instead of a DB, CF should be able to say "query missing" instead of "table missing".)

rc.qRSRecipient does exist in memory - but since the query.cfc has no reference to it (because query.cfc is outside the thread), you have to include it (in CFSCRIPT).  CFQUERY doesn't use the query.cfc component.  If CFQUERY is used for a QoQ, it's assumed that the first query is in the same scope as the QoQ query.

V/r,

^_^

[BKBK]

I do understand that the error occurs because the context of the local variable, rc.qRSRecipient, is absent from the Query CFC. My point is that the error message is inconsistent, confusing. To see this yourself, put it alongside a phrase from your last post:

rc.qRSRecipient does exist in memory - but... Table named rc.qRSRecipient was not found in memory.

[Carl Von Stetten]

I think this just confirms what a bad idea it was to try to implement script functions as CFCs, rather than as built-in functions.  I'm not sure how the error message could be fixed (and it is the error message that should be fixed - again the CFC is working as designed), since the error is not coming from inside the CFC - it's a ColdFusion runtime error.

It's actually coming from the execution of an actual CFQUERY call by the query.cfc (via inheritance from base.cfc, and the invokeTag() function).  And running an actual CFQUERY as a Query-of-Query also gives this error:

I tested this on CF11 via CommandBox.

So if any bug report needs to be filed, then the error message for QofQ CFQUERYs should be changed to "Query named xxx was not found in memory".  The bug report should not be against query.cfc.

-Carl V.

[BKBK]

@Carl Von Stetten

Hyear, hyear.

@Steve Sommers

It would be a good idea to refer in your bug report to this thread.