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.
