Ask a Jedi: Getting the SQL from a Query

This post is more than 2 years old.

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.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Stefan posted on 5/30/2008 at 7:22 PM

An other way is to enable the "Enable Request Debugging Output" in the CF-Administrator --> Debugging with the option "Database Activity".

Comment 2 by Raul Riera posted on 5/30/2008 at 7:38 PM

The other method would be to use that thing SOMEONE did for FireBug :P

Comment 3 by Raymond Camden posted on 5/30/2008 at 7:43 PM

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.

Comment 4 by Doug posted on 5/30/2008 at 8:14 PM

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!

Comment 5 by TomdeMan posted on 5/30/2008 at 8:54 PM

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/20...">here </a>.

Comment 6 by TomdeMan posted on 5/30/2008 at 8:55 PM
Comment 7 by Frederic Fortier posted on 5/31/2008 at 9:54 PM

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.

Comment 8 by Peter Hoopes posted on 6/3/2008 at 12:24 AM

This is what I use CFDUMP for. <cfdump var="#queryvar#"> and it shows the query and the data received.

Comment 9 by Ryan posted on 6/4/2008 at 12:25 AM

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.

Comment 10 by Raymond Camden posted on 6/4/2008 at 12:30 AM

No, I believe it works for all. Did you try? ;)

Comment 11 by ike posted on 6/7/2008 at 7:07 AM

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.

Comment 12 by dcs posted on 4/7/2009 at 12:49 AM

I wanted to take time out and thank you for this. I used it to execute a query that I had already executed previously (getting different results the second time, of course ;) as there were queries in between).

Comment 13 by Chris Ulrich posted on 5/7/2009 at 3:42 PM

Ray - I saw a technique you've posted using GetMetaData(queryname) - very neat. Didn't know it existed.

When I CFDUMP it I get the field name, the data type, and in the order it appears in the table (unlike a CFDUMP of a query which alpha sorts the columns). However, what I also need is the size of the field - varchar(50), char(6), etc...

I can see that it is not in the meta data. Is there another way to retrieve this?

Thank you!

Comment 14 by Chris Ulrich posted on 5/7/2009 at 4:21 PM

Found it ... for those interested ...

SELECT column_name 'Column Name',
data_type 'Data Type',
CHARacter_maximum_length 'Maximum Length'
FROM information_schema.columns
WHERE table_name = 'table-name-here'

Couple that with a list of all of your tables:

select TABLE_NAME from INFORMATION_SCHEMA.[TABLES]

and you've got a quick and dirty inspector to look up your tables, field names, data types and sizes.

Thanks

Comment 15 by dcs posted on 5/7/2009 at 4:34 PM

That will work for MySQL but that sort of query is DBMS-specific. MS SQL Server has a set of system tables and accompanying stored procedures and views if you need to query them. (I haven't used MS SQL in a while so I don't recall the names.) Similarly in Oracle - you have views like ALL_TABLES, USER_TABLES, ALL_TAB_COLUMNS, etc.

Comment 16 by Chris Ulrich posted on 5/7/2009 at 6:47 PM

You're right ... I'm using SQL 2000 and 2005 and it works with both, but it may not work for other databases.

Comment 17 by Jeff Reese posted on 7/8/2014 at 2:41 AM

You can also do a cfdump with format="text" and it will dump the actual SQL that is generated, so you can paste it right into your sql tool and run it.

Comment 18 by Raymond Camden posted on 7/8/2014 at 4:51 AM

Regular CFDUMP in CF10 and higher shows SQL too.