Highlighted

Query scope descrepancy between tag and script

Advocate ,
Jul 15, 2016

Copy link to clipboard

Copied

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>

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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");

Views

792

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Query scope descrepancy between tag and script

Advocate ,
Jul 15, 2016

Copy link to clipboard

Copied

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>

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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");

Views

793

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 15, 2016 0
LEGEND ,
Jul 15, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 15, 2016 0
Most Valuable Participant ,
Jul 15, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 15, 2016 2
Adobe Community Professional ,
Jul 17, 2016

Copy link to clipboard

Copied

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");

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 17, 2016 2
Adobe Community Professional ,
Jul 17, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 17, 2016 0
Advocate ,
Jul 18, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 18, 2016 0
LEGEND ,
Jul 18, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 18, 2016 0
Most Valuable Participant ,
Jul 18, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 18, 2016 0
Adobe Community Professional ,
Jul 18, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 18, 2016 0
LEGEND ,
Jul 18, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 18, 2016 0
Adobe Community Professional ,
Jul 18, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 18, 2016 1
Most Valuable Participant ,
Jul 18, 2016

Copy link to clipboard

Copied

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:

2016-07-18_15-42-42.png

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 18, 2016 0
Adobe Community Professional ,
Jul 18, 2016

Copy link to clipboard

Copied

@Carl Von Stetten

Hyear, hyear.

@Steve Sommers

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 18, 2016 0
Advocate ,
Jul 19, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 19, 2016 0
Most Valuable Participant ,
Jul 19, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 19, 2016 0
Advocate ,
Jul 19, 2016

Copy link to clipboard

Copied

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...

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 19, 2016 0
Most Valuable Participant ,
Jul 20, 2016

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 20, 2016 0
Advocate ,
Jul 20, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 20, 2016 0
Most Valuable Participant ,
Jul 20, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 20, 2016 0