Dumping CFQUERY (in ColdFusion 8)
Posted At : July 16, 2008 11:51 AM | Posted By : Bob Silverberg
Related Categories: ColdFusion
I just learned something new about CFQUERY, so I thought I'd share it. Now don't get excited. This is something that I'm guessing many of you already know, but I think it's cool nonetheless.
If you do a CFDUMP of a query, only in CF8 I believe, you don't just see the contents of the query, you can also see some interesting facts about the query as well. So, if I run the following code:
2 SELECT DISTINCT ProductId, ProductCode, ActiveFlag
3 FROM tblProduct
4 WHERE ProductId IS NOT NULL
5 AND Level1CategoryId =
6 <cfqueryparam cfsqltype="cf_sql_integer" value="1" />
7 AND Level2CategoryId =
8 <cfqueryparam cfsqltype="cf_sql_integer" value="15" />
9 AND Level3CategoryId =
10 <cfqueryparam cfsqltype="cf_sql_integer" value="17" />
11 ORDER BY ActiveFlag DESC, ProductId
12 </cfquery>
13 <cfdump var="#qryResult#">
I see:
So evidently the query object is made up of a number of other objects, one of which is the actual query ResultSet. What I found interesting about this is that you cannot actually use any of that information. If you try to refer to qryResult.ResultSet or qryResult.SQL ColdFusion throws an error. So really, you can only see that information via CFDUMP. And, if you want to use the ResultSet, as we all know, you just refer to the query itself. I guess that internally ColdFusion knows that when a template asks for qryResult, it's really asking for the ResultSet that's stored in qryResult.
My investigation into this also revealed to me that ColdFusion MX 7 added the result attribute to the CFQUERY tag, which does allow you to gain access to all of this information (except ResultSet, of course). For example, if I run this code:
2 SELECT DISTINCT ProductId, ProductCode, ActiveFlag
3 FROM tblProduct
4 WHERE ProductId IS NOT NULL
5 AND Level1CategoryId = <cfqueryparam cfsqltype="cf_sql_integer" value="1" />
6 AND Level2CategoryId = <cfqueryparam cfsqltype="cf_sql_integer" value="15" />
7 AND Level3CategoryId = <cfqueryparam cfsqltype="cf_sql_integer" value="17" />
8 ORDER BY ActiveFlag DESC, ProductId
9 </cfquery>
10 <cfdump var="#strResult#">
I see:
I didn't even know about that new result attribute until I stated digging into this issue. So, what does all of this mean? Well, if you didn't already know about the result attribute, perhaps you can think of some uses for it. And if your CFDUMP of a query doesn't look the way you expected, don't be scared like me, just chock it up to CF8.
http://www.anujgakhar.com/2008/02/01/cfquery-and-t...
Here's one that may be useful to you:
#qryResult.getColumnList()#
There is some interesting info about queries.
<cfdump var="#your_query_name#" metainfo="No">
result="info" parameter to the CFQUERY tag. Then, you
simply reference them as part of the "info" structure.
(Any variable name can be used, I just picked "info")
<cfoutput>
#info.executionTime#<br>
#info.SQL#
</cfquery>
This is a documented feature in Coldfusion 8 and can
be found in their reference guide under CFQUERY.