Raymond Camden's Blog Rss

Ask a Jedi: Getting the SQL from a Query

16

Posted in ColdFusion | Posted on 05-30-2008 | 4,855 views

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:

view plain print about
1<cfquery name="test" datasource="blogdev" result="result">
2select    #limit# id, title
3select    id, title
4from    tblblogentries
5where    title like <cfqueryparam cfsqltype="cf_sql_varchar" value="%foo%">
6limit #limit#
7</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

[Add Comment] [Subscribe to Comments]

Stefan said on 05-30-2008 at 10:22 AM #
An other way is to enable the "Enable Request Debugging Output" in the CF-Administrator --> Debugging with the option "Database Activity".
Raul Riera said on 05-30-2008 at 10:38 AM #
The other method would be to use that thing SOMEONE did for FireBug :P
Raymond Camden said on 05-30-2008 at 10:43 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.
Doug said on 05-30-2008 at 11:14 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!
TomdeMan said on 05-30-2008 at 11:54 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>.
TomdeMan said on 05-30-2008 at 11:55 AM #
sorry bad link - http://tomdeman.com/blog/2008/2/1/Getting-more-the...
Frederic Fortier said on 05-31-2008 at 12: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.
Peter Hoopes said on 06-02-2008 at 3:24 PM #
This is what I use CFDUMP for. <cfdump var="#queryvar#"> and it shows the query and the data received.
Ryan said on 06-03-2008 at 3:25 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.
Raymond Camden said on 06-03-2008 at 3:30 PM #
No, I believe it works for all. Did you try? ;)
ike said on 06-06-2008 at 10:07 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.
dcs said on 04-06-2009 at 3:49 PM #
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).
Chris Ulrich said on 05-07-2009 at 6:42 AM #
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!
Chris Ulrich said on 05-07-2009 at 7:21 AM #
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
dcs said on 05-07-2009 at 7:34 AM #
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.
Chris Ulrich said on 05-07-2009 at 9:47 AM #
You're right ... I'm using SQL 2000 and 2005 and it works with both, but it may not work for other databases.

[Add Comment] [Subscribe to Comments]