ColdFusion 9 Query of Query In CFScript

Posted: February 22, 2011 in ColdFusion

When doing a Query of Query using tag context you see:

<cfquery name="local.myQuery" dbtype="query">
SELECT *
FROM [local].myOtherQuery
WHERE Col1 = 'Chris'
</cfquery>

You would expect you could just do the same in CFScript under ColdFusion 9:

local.queryService = new query();
local.queryService.setName("myQuery");
local.queryService.setDBType("query");
local.objQueryResult = local.queryService.execute(sql="SELECT [Value] FROM [local].myOtherQuery WHERE Col1='Chris'");
local.queryResult= local.objQueryResult.getResult();

However this will produce an error:
coldfusion.sql.imq.imqException: Query Of Queries runtime error.
Table named local.myOtherQuery was not found in memory. The name is misspelled or the table is not defined.

Thanks to Jared Rypka-Hauer, I found a resolution to this lame error by using the setAttributes method:

local.queryService = new query();
local.queryService.setName("myQuery");
local.queryService.setDBType("query");
local.queryService.setAttributes(sourceQuery=local.myOtherQuery);
local.objQueryResult = local.queryService.execute(sql="SELECT [Value] FROM sourceQuery WHERE Col1='Chris'");
local.queryResult = local.objQueryResult.getResult();

This must be done because local.myOtherQuery is out of context at least until they release closures for ColdFusion.

If you would like to see the inner-works of this visit:
\CustomTags\com\adobe\coldfusion\query.cfc in your ColdFusion 9 install directory.

Update 7/7/2011:
Here’s an excellent blog from Ben Nadel also describing a solution to this issue:
http://www.bennadel.com/blog/2224-Performing-Query-Of-Queries-Using-ColdFusion-9-s-Query-cfc-Component.htm

Advertisements
Comments
  1. kris says:

    Your site didn’t work for me but I think I got this one working:
    http://cookbooks.adobe.com/post_Query_of_Query_with_CFSCRIPT-16492.html

  2. FWIW, Railo implemented an additional syntax that the CFML Advisory Committee considered (in addition to the somewhat lame CFC-based approach shown above.

    query name=”local.myQuery” dbtype=”query” {
    writeOutput( ”
    SELECT *
    FROM [local].myOtherQuery
    WHERE Col1 = ‘Chris’
    ” );
    }

    You can place any code in the body of the query { } statement, just like any other compound script statement. Anything that would be “output” becomes part of the SQL that is executed.

  3. ericdpierce says:

    Just what I needed.

    Thanks, Chris!

  4. @Sean … I owe you a f&*kin’ beer

  5. Richard Hughes says:

    THANKS! 🙂

  6. Martie Henry says:

    Absolutely awesome. That setAttribute was what I was missed.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s