CFSCRIPT-based Query of Queries Gotcha

I'm hardly the first to blog about this (see here or here), but you're using the new-in-CF9 Query object to execute a query of queries (QofQ), you'll run into scope issues.  Specifically, the CFQUERY tag executes in the context it is written, but the Query object is a CFC and so follows all the normal CFC scoping rules.  For example:

q = queryNew('id,name');
new Query(dbtype='query', sql='select * from q').execute().getResult();

This code will throw an exception saying "Table named q was not found in memory".  Why?  Because Query is a CFC, and inside a CFC method invocation, you can't see the var-scoped variables from the calling method (nor the variables scope of the calling template).  This is standard CFC behaviour, but is quite different than using the CFQUERY tag.  The workaround is to do this:

q = queryNew('id,name');
qry = new Query(dbtype='query', sql='select * from q')
qry.setAttributes(q=q);
qry.execute().getResult();

That places the 'q' variable into the variables scope of the Query instance, so it can be found by the CFQUERY tag inside the execute method.  Note that this is NOT thread safe!!  If you take this approach, you must create a new Query instance for every thread of execution – you can't pool them.  Normally this isn't a big deal, but that setAttributes call is equivalent to assigning to the variables scope and carries all the lurking problems.

If you like chaining, you can do this (which is functionality equivalent, including the thread safety concerns):

q = queryNew('id,name');
new Query(dbtype='query', sql='select * from q', q=q).execute().getResult();

Here we're just passing the attribute in via the init method, instead of a separate setAttributes call.  Like I said, it's no different in terms of functionality, but it's a lot nicer (I think) in terms of readability and conciseness.

In my opinion, this is sort of broken.  ColdFusion is all about server-encapsulated hacks in encapsulation.  Think about your use of CFQUERY: you just use it, and CF takes care of getting the right database handles and such for you.  It just works.  It's totally unencapsulated, but it just works.  And it's glorious.  The Query CFC has the same characteristics, except for QofQ.  Not that I expect it to change, but I'm of the opinion that the Query object ought to work like CFQUERY in this regard.