Ask a Jedi: Getting the SQL from a Query

This is a dupe, but as it comes up kind of often, I thought I'd blog it anyway. Doug asks:

After I perform a cfquery, is there a way to see what the query actually looked like? I'm not asking if there was an error, I know I can grab that through a cfcatch. But if the query was good (and I'm still not getting the results I expected) I want to see how the query was formed. I know I can great a variable and copy/paste the query contents into that, but I didn't know if there was some kind of easier way to just look at the query or something.

Absolutely. Since CF7 we've had two ways to introspect ColdFusion queries. To answer your main question, you can get the SQL (along with other bits of info) by using result= in your query:

<cfquery name="test" datasource="blogdev" result="result">
select   #limit# id, title
select   id, title
from   tblblogentries
where   title like <cfqueryparam cfsqltype="cf_sql_varchar" value="%foo%">
limit #limit#
</cfquery>

This will create a structure named result with these values:

  • Cached: Boolean that tells you if the query was cached
  • ColumnList: List of columns. This is NOT in the same order as your query. (More on that in a bit.)
  • ExecutionTime: Time it took to run the query.
  • RecordCount: Number of rows returned
  • SQL: The SQL, what you want!
  • SQLParameters: An array of values used in cfqueryparameter tags. Note that the SQL value will use a ? for each of these.

Another function you can use is getMetaData(). If you use it on a query you get an array of structs back for each column. This is in the order specified in your SQL, and even matches the case used in the SQL if you care about that. It contains a type name that tells you what type of column it is as well as a "IsCaseSensitive" flag. So if you do care about what order you selected your columns, this is what you would use.

Comments (11) May 30, 2008 9:46 AM Views: 1797

Comments

[Add Comment]
An other way is to enable the "Enable Request Debugging Output" in the CF-Administrator --> Debugging with the option "Database Activity".
# Posted By Stefan | 5/30/08 10:22 AM
The other method would be to use that thing SOMEONE did for FireBug :P
# Posted By Raul Riera | 5/30/08 10:38 AM
To both your comments - it was my impression he wanted the SQL for code reasons, not debugging.

If he wants it for debugging, you can just dump it. Even easier.
# Posted By Raymond Camden | 5/30/08 10:43 AM
awesome!!

I was working on a fairly complicated query awhile back. It had a bunch of LEFT JOINS and was pulling data from multiple tables. It wasn't throwing and error, but it wasn't returning the results I expected, so I was looking for a way to see the SQL string that was generated.

Also, most of my clients are on shared hosting, so I don't have access to any ColdFusion admin stuff.

Thanks Ray, you made my Friday!
# Posted By Doug | 5/30/08 11:14 AM
There is one other way a co-worker of mine showed me not too long ago. Throw an abort inside the CFQUERY. I posted about it <a href="http://tomdeman.com/blog/2008/2/1/Getting-more-the... </a>.
# Posted By TomdeMan | 5/30/08 11:54 AM
sorry bad link - http://tomdeman.com/blog/2008/2/1/Getting-more-the...
# Posted By TomdeMan | 5/30/08 11:55 AM
Something I also do is for viewing super quickly what was generated in the cfquery is to put an cfabort right before the end of the cfquery,

Like that

<cfquery>
"the query"
<cfabort>
</cfquery>

That is of course if you just want to have what was generated between the cfquery tags

also you can add a <pre></pre> around that, to keep to formating.
# Posted By Frederic Fortier | 5/31/08 12:54 PM
This is what I use CFDUMP for. <cfdump var="#queryvar#"> and it shows the query and the data received.
# Posted By Peter Hoopes | 6/2/08 3:24 PM
Is there a similar way to get the SQL in an insert/update/delete type query? I assume the solutions above (except for the cfabort) only work on Select queries.
# Posted By Ryan | 6/3/08 3:25 PM
No, I believe it works for all. Did you try? ;)
# Posted By Raymond Camden | 6/3/08 3:30 PM
One of the things I like personally about the SQL abstraction layer I built into the onTap framework (yes I'm biased) is that in addition to imo just plain nice syntax that allows you to do a lot of things easily that would be rather difficult with standard ad-hoc queries, is that once you've configured your SQL statement object, you can output just the SQL it will generate easily by calling the getSyntax() method. I.e. <cfoutput><pre>#datasource.getStatement("select").init("mytable").andOrFilter("some,columns",form.search).getSyntax()#</pre></cfoutput>

It's useful for generating SQL scripts and other automation type tasks like Ray mentioned.
# Posted By ike | 6/6/08 10:07 PM
[Add Comment]