Bob's Blog

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:

view plain print about

1<cfquery name="qryResult" datasource="myDSN">
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: cfdump sample 1

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:

view plain print about

1<cfquery name="qryResult" datasource="myDSN" result="strResult">
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: cfdump sample 2

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.

Comments (6) |

Print | 5902 Views |

TweetBacks

Comments

[Add Comment] [Subscribe to Comments]

There must be a way to get to that data by using the Query's Java methods. I haven't been able to locate the correct method for the SQL or execution time, but this blog article is a good starting point.
http://www.anujgakhar.com/2008/02/01/cfquery-and-t...

Here's one that may be useful to you:
#qryResult.getColumnList()#

# Posted By Dan Sorensen | 7/16/08 1:19 PM

Check java.sql.ResultSet and java.sql.ResultSetMetaData.
There is some interesting info about queries.

# Posted By radekg | 7/17/08 3:04 AM

Thanks for the tips guys. I'm sure all sorts of goodies are hidden in the underlying Java objects.

# Posted By Bob Silverberg | 7/18/08 10:08 AM

to display just the result set try:
<cfdump var="#your_query_name#" metainfo="No">

# Posted By Marco | 11/4/08 2:42 PM

You can access these other paramters by adding the
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.

# Posted By GD | 12/8/08 10:07 AM

Found how to access the SQL, executionTime, etc here http://blog.pengoworks.com/index.cfm/2008/11/17/Sh...