Getting more than just records from CFQUERY in CF7 and CF8

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.

select * from tblBlogEntries

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.

select #variables.columnList# from tblBlogEntries

OUTPUT: select * from

select #variables.columnList# from tblBlogEntries

OUTPUT: select * from tblBlogEntries

Leave a Reply

Your email address will not be published. Required fields are marked *