Getting more than just records from CFQUERY in CF7 and CF8

Author: TomdeMan
Related Categories: ColdFusion

February 1, 2008

This might be obvious to some, but the other day I drew a blank, or I thought I did...

I have both CF7 and CF8 running simultaneously on my machine and was doing some debug work with a monster dynamic query. I wanted to grab the rendered SQL from the CFQUERY and just throw it into the DB Client's Query window. So I dumped the query NAME variable. To my dismay, no SQL, just a resultset. Where'd it go?

It seems I have been spoiled by CF8, already!

CF7 doesn't deliver the processed query's information in the NAME attribute. Instead, you have to set a RESULT attribute to capture the rendered SQL, Execution Time, Record Count, Column List, and the Cached boolean.

<cfquery name="qName" result="qResult" datasource="blog_dsn">
select * from tblBlogEntries
</cfquery>

<cfdump var="#qResult#">
<cfdump var="#qName#">

CF8, on the other hand, returns both the extended info and the resultset with the NAME attribute. It still accepts a RESULT attribute, which returns the extended info only, as did CF7.

While I was sitting on this entry, a co-worker of mine brought up the same issue. He had found another interesting method of retrieving the generated SQL.

Place a CFABORT inside the CFQUERY. It will treat the CFQUERY tags like a CFOUTPUT, and render whatever you have up to the CFABORT and present it as plain-text.

Give it a try.

<cfquery name="qName" result="qResult" datasource="blog_dsn">
select #variables.columnList# from <cfabort> tblBlogEntries
</cfquery>

OUTPUT: select * from

<cfquery name="qName" result="qResult" datasource="blog_dsn">
select #variables.columnList# from tblBlogEntries
<cfabort>
</cfquery>

OUTPUT: select * from tblBlogEntries