Another CFScript Query Gotcha

There have been a few issues reported with using the new Query component from cfscript and today I ran into another. Well actually I had a co-worker ask about it, but it still led me to look into the issue. (Little did I know he was researching his own blog post on this. Sorry for stealing your post Adam.) Anyway, my co-worker was trying to run a Query of Queries in cfscript. Here is an example:

<cfscript>
//create an empty query to work with qryFoo = queryNew("a,b,c","varchar,varchar,varchar");

//add a row and fill it with some data queryAddRow(qryFoo);
querySetCell(qryFoo,"a","aaaaaa");
querySetCell(qryFoo,"b","aaaaaa");
querySetCell(qryFoo,"c","aaaaaa");

writeDump(var=qryFoo, label="qryFoo");

qryFoo2 = new query(dbtype="query", sql="select a, 'bbbbb' as b, 'ccccc' as c from qryFoo");
result = qryFoo2.execute();

writeDump(var=result, label="qryFoo2");
</cfscript>

This code resulted in the following error:

Error Executing Database Query.

Query Of Queries runtime error. Table named qryFoo was not found in memory. The name is misspelled or the table is not defined.

At first I was a bit baffled, but when I thought about it for a minute it actually made perfect sense. The script functions implemented as CFCs are just the plain old CFML tags wrapped in components. That means when the cfquery tag actually executes it does so within the context and scope of the component function, not the calling page. To help illustrate the issue consider the following component:

<!--- sample.cfc --->
<cfcomponent>
<cffunction name="doDump">
<cfdump var="#myVar#" />
</cffunction>
</cfcomponent>

You wouldn't really expect the following code to work and it won't. You will get an error stating that myVar is undefined:

<cfset myVar = "This is a test." />
<cfset CreateObject("component","sample").doDump() />

That is basically what is going on with the query of queries example above. One workaround I thought of would be to put the original query in a scope the CFC can access, say the request scope:

<cfscript>
//create an empty query to work with request.qryFoo = queryNew("a,b,c","varchar,varchar,varchar");

//add a row and fill it with some data queryAddRow(request.qryFoo);
querySetCell(request.qryFoo,"a","aaaaaa");
querySetCell(request.qryFoo,"b","aaaaaa");
querySetCell(request.qryFoo,"c","aaaaaa");

writeDump(var=request.qryFoo, label="qryFoo");

qryFoo2 = new query(dbtype="query", sql="select a, 'bbbbb' as b, 'ccccc' as c from request.qryFoo");
result = qryFoo2.execute();

writeDump(var=result, label="qryFoo2");
</cfscript>

This works, but it is obviously less than ideal. Given all of the issues with the current Query.cfc I'm really hoping Adobe can come up with a better solution for full cfscript support in the next version of CF.

Update

Adam Cameron pointed out another solution in the comments below. Apparently you can use the setAttributes method of the Query object to set arbitrary attributes. I didn't realize this as the documentation states that this method supports "all attributes supported by the cfquery tag." But if you look at the implementation for the method you can see it just adds any attributes to the variables scope of the component:

public void function setAttributes()
      {
         if(!structisempty(arguments))
         {
            structappend(variables,arguments,"yes");
         }
      }

Here is a complete example using setAttributes:

<cfscript>
//create an empty query to work with qryFoo = queryNew("a,b,c","varchar,varchar,varchar");

//add a row and fill it with some data queryAddRow(qryFoo);
querySetCell(qryFoo,"a","aaaaaa");
querySetCell(qryFoo,"b","aaaaaa");
querySetCell(qryFoo,"c","aaaaaa");

writeDump(var=qryFoo, label="qryFoo");

qryFoo2 = new query(dbtype="query", sql="select a, 'bbbbb' as b, 'ccccc' as c from qryFoo");
qryFoo2.setAttributes(qryFoo=qryFoo);
result = qryFoo2.execute();

writeDump(var=result, label="qryFoo2");
</cfscript>

Note that there are thread-safety issues with this approach. If you do use setAttributes make sure you create a new instance of the service component for each service call.

Comments
Mark Drew's Gravatar I presume once more people use the software, more issues will be made apparent (as is normal) and then in 9.1 a lot of these will be released.

Normal lifecycle of s/ware dev I guess
# Posted By Mark Drew | 10/16/09 4:23 AM
Henry Ho's Gravatar Last time I checked, QoQ is not supported in beta. Is it supported in final now?
# Posted By Henry Ho | 10/16/09 2:58 PM
Nathan Mische's Gravatar @Henry - I can't find any info in the documentation that says QoQ isn't supported.
# Posted By Nathan Mische | 10/17/09 10:58 AM
Adam Cameron's Gravatar It would be better practice, I think, to pass the query into the object, rather than reference an external variable:

variables.qryFoo2.setAttributes(qryFoo=variables.qryFoo);

The refer to qryFoo in the SQL string passed into the Query object.

I agree that the way these "services" have been implemented just as CFCs is poor.

--
Adam
# Posted By Adam Cameron | 10/18/09 5:59 AM
Nathan Mische's Gravatar @Adam - Thanks for the tip. I've updated the post to include your suggestion.
# Posted By Nathan Mische | 10/18/09 10:39 AM
Allen's Gravatar Adam's correct. For loose coupling you'll want to be passing that query into the function.

And at risk of being uppity, this shouldn't be an issue if you're using var to ensure variables are private to the method, right? If you were in a habit of doing that, you'd find yourself having to pass the query in. So the issues wouldn't end up being an issue. Or is my thinking a bit off on that?
# Posted By Allen | 10/18/09 2:54 PM
Nathan Mische's Gravatar @Allen - In general you are correct in your thinking, however in this case you cannot pass the query into the execute function. You can pass the query into the Query component's variables scope via the setAttributes function, as Adam suggests, but the fact that you are setting values in the variables scope means that there could be thread-safety issues. (For example if you are storing the Query component in a shared scope.)

My issue is that these service components have limitations because they are implemented as components. If they were implemented differently they may not have these limitations.
# Posted By Nathan Mische | 10/18/09 5:37 PM
Charlie's Gravatar Once you have executed the QofQ, does calling the clear() method flush the variables scope on the service object to avoid the thread safety issue?
# Posted By Charlie | 6/22/10 4:34 PM
Nathan Mische's Gravatar @Charlie: No.
# Posted By Nathan Mische | 7/7/10 11:37 AM
Dave's Gravatar It appears this is still a problem with 9.0.1. I that correct? I just bumped into the same issue. Are there any updates?
# Posted By Dave | 7/17/10 1:11 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.