Photo from Chile

Capturing the SQL Generated by CFQUERY

Posted At : January 20, 2009 9:40 PM | Posted By : Bob Silverberg
Related Categories: ColdFusion

Have you ever wanted to capture the SQL that was generated by a CFQUERY? It's pretty well known that you can see the SQL if you do a CFDUMP on your query result, but what if you want to capture it programmatically? Maybe you want to display it on a page, or log it, or compare it to an expected value.

Well, we can accomplish this feat using the query's underlying coldfusion.sql.QueryTable object. Here's an example:

view plain print about
1<cfquery datasource="myDSN" name="qryTest">
2    SELECT myId FROM myTable
3</cfquery>
4
5<cfoutput>
6The SQL generated is: #qryTest.getMetaData().getExtendedMetaData().sql#
7</cfoutput>

Obviously in the above example we don't need to capture the generated SQL as it's hardcoded inside the CFQUERY tag. But what if we're generating our SQL statement dynamically? What if we're using an ORM that generates queries for us?

As well, if we're using CFQUERYPARAM (and who isn't?), we can also get a structure of the parameter values passed into the query using qryTest.getMetaData().getExtendedMetaData().sqlparameters.

Cool, eh?

Comments Comments (13) | Print Print | 8951 Views | Bookmark and Share
TweetBacks
Comments
[Add Comment] [Subscribe to Comments]
Very cool, I am going to have to play around with this.
# Posted By Justin Slamka | 1/20/09 10:57 PM
Another way to do it is:

<cfquery datasource="myDSN" name="qryTest" result="my_query_result">
SELECT myId FROM myTable
</cfquery>

<cfoutput>my_query_result.sql</cfoutput>
# Posted By Adedeji Olowe | 1/21/09 2:56 AM
Yes! That means that using this the query debug info in Coldfusion can be improved when using CFQUERYPARAMs. Now all params show up beneath the query, and as ? in the query itself. Please build this into CF9 ;-)
# Posted By Sebastiaan | 1/21/09 4:08 AM
@Adedeji: That is true, but the use case that reminded me of this method was trying to capture the SQL generated by Transfer, which is an ORM that generates all of the CFQUERY calls for me. So I don't want to have to edit the CFQUERY tag itself.

This is also handy if your CFQUERY is buried in your model, but you're trying to debug a view. It's much easier to just add the single line of code into the view that makes use of the query object, than digging into the model and changing the CFQUERY tag.

One last note: I mispoke when I said that sqlparameters returns a structure. It actually returns an array ;-)
# Posted By Bob Silverberg | 1/21/09 9:32 AM
@Sebastiaan: I'm already doing this in CF7/CF8. You only have to replace 1 file on your server and it replaces the ? with the actual values, plus it highlights them. If you are interested, send me a request to pat (at) datanotion [dot] com
# Posted By Pat Buchanan | 1/21/09 1:43 PM
That sounds cool Pat. I'd be interested to hear about that as well. Do you have a blog on which you can post that info?
# Posted By Bob Silverberg | 1/21/09 2:43 PM
Magnificent, thanks! I just needed this.
# Posted By Jeroen | 1/28/09 12:09 PM
I sent this example to Bob if anyone is interested in it. He might write up a blog post on it as well.
# Posted By Pat Buchanan | 1/28/09 2:31 PM
For those who are interested, Railo users can get this information using:

query.getSQL().toString()

This returns the sql statement with ? replaced by their values.

If you need access to the parameters, you can call:

query.getSQL().getItems()

which returns an array. You can then call getValueForCF() on each of the items to get the value.
# Posted By Jeroen | 3/20/09 7:34 PM
Anyone know how to use getExtendedMetaData on a cfquery that was an UPDATE/INSERT/DELETE statement?
# Posted By brien8cake | 4/15/09 11:38 AM
Is there any way to capture the SQL for action queries? (insert, update, delete?)
# Posted By Heather | 10/30/09 4:01 PM
@bob,

thank you so much for posting this! I was having problem with serialzejson crashing if a query contain a binary column. Your information enable me to find a work around.


@jeroen

thanks for posting how to do this with railo :)

you all rock!
# Posted By tony petruzzi | 7/15/10 3:14 PM
Thanks for sharing this! I've found that dumping "#shippingRate.getMetaData().getExtendedMetaData()#" will allow me to see my sql along with the cfqueryparameters in an array below.

Paul Alkema
# Posted By Paul Alkema | 7/22/10 1:38 PM
[Add Comment]