Skip to main content
S
Steve Sommers
Legend
July 15, 2016
Answered

Query scope descrepancy between tag and script

  • July 15, 2016
  • 3 replies
  • 2112 views

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>

    This topic has been closed for replies.
    Correct answer BKBK

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

    3 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    July 17, 2016

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

    WolfShade
    WolfShade
    Legend
    July 18, 2016

    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,

    ^_^

    WolfShade
    WolfShade
    Legend
    July 18, 2016

    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.


    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
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    July 17, 2016

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

      WolfShade
      WolfShade
      Legend
      July 15, 2016

      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
      Carl Von Stetten
      Legend
      July 15, 2016

      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.

        Terms & ConditionsAccessibility statement
        Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices