This is a dupe, but as it comes up kind of often, I thought I'd blog it anyway. Doug asks:
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: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.
<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.
Archived Comments
An other way is to enable the "Enable Request Debugging Output" in the CF-Administrator --> Debugging with the option "Database Activity".
The other method would be to use that thing SOMEONE did for FireBug :P
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.
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!
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>.
sorry bad link - http://tomdeman.com/blog/20...
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.
This is what I use CFDUMP for. <cfdump var="#queryvar#"> and it shows the query and the data received.
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.
No, I believe it works for all. Did you try? ;)
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.
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).
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!
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
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.
You're right ... I'm using SQL 2000 and 2005 and it works with both, but it may not work for other databases.
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.
Regular CFDUMP in CF10 and higher shows SQL too.