I wrote this on my company’s wiki couple of days ago.
Sometimes it can be quite hard to get SQL out of CFQUERY especially if the SQL is dynamically generated AKA LOTS OF CFIF/CFSWITCH statements inside the CFQUERY (this should be another discussion on why it is necessary to do something like this)
I have encountered an example of a such CFQUERY whereby the query has 395 lines of code to generate ONE update statement to jobs table (CFIF/CFSWITCH galore!!). Debugging hell for sure.
Below are some of the ways to get SQL out of the CFQUERY:
- Use CFDUMP, it will contain the generated SQL query
- If Enable Request Debugging Output is enabled in CF admin, look at CF debugging info at the bottom the page
- If the Enable Robust Exception Information is enabled in CF admin, when the query contains errors, the errors will be shown on that blue CF error page.
- Leverage the underlying Java awesomeness in CF via Bob’s post
[code=“cf”]The SQL generated is: #qryTest.getMetaData().getExtendedMetaData().sql# The CFQUERYPARAMs passed in is: #qryTest.getMetaData().getExtendedMetaData().sqlparameters.# [/code]
- This a crude way of debugging (and a little bit time consuming too), I don’t usually need to resort to this, put the SQL block inside a CFSAVECONTENT and dump it. As my colleague noted, if the block has CFQUERYPARAMS (and it should!) it will throw an error, the quick way to get around this, is to do a word replace in your favourite editor.
- Use MS SQL Profiler (if you know how to use it well, coz I don’t :))