Matt asks:
I am looking for a function that will return the SQL of a given query name. Do you know of any?
While there are hacks to do this, the "official" way, under ColdFusion MX 7, is to simply use the new result attribute:
<cfset name = "e">
<cfquery name="getIt" datasource="cfartgallery" result="result">
select artistid
from artists
where lastname like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#name#%" maxlength="255">
</cfquery>
<cfdump var="#result#">
Notice I added a result="result" attribute to the query. The result structure contains these keys:
- cached: A boolean that indicates if the query was cached.
- columnlist: A lit of columns for the query. This is not in the order you specified. If you want that, simply call getMetaData(getIt). This returns an array of structs. You not only get the right order of columns, but the same case that was used in SQL. (If case matters, but to be honest, you probably shouldn't depend on that.)
- executionTime: How long it took the query to execute.
- recordCount: How many records the query returned.
- sql: The SQL that was used in the query. If you had dynamic parts of the sql, you will see the values that were passed to the query.
- sqlparameters: If you used cfqueryparam in your query (and if you didn't, you are asking for trouble), then this will be an array of each cfqueryparam item that was used in query.
You may notice a ? or two in your sql. This represents the cfqueryparam blocks. To get an idea of the "real" SQL, you would need to replace the first ? with the first item in sqlparameters. And so on and so on. In Starfish, one of the things I did was write a little tool to replace the ? marks so I wouldn't have to do so in my head. This is especially useful for large queries with lots of queryparams. (If folks want the code, let me know and I'll post it as a followup entry;.)
So obviously none of this works if you are using an earlier version of ColdFusion. If you want, you could write a query where the SQL is generated outside the query. So for example, you may use cfsavecontent to save the sql and then pass that inside the query. Unfortunately this would prevent the use of cfqueryparam.
Archived Comments
I guess I should have looked in my documentation, a little better. Thanks
What are some of the "hacks" to do it pre cfmx 7?
I'd be interested in seeing the code that replaces the question marks.
Pre MX 7, you could get the SQL by turning debugging on and using the serviceFactory.
Good post, please add this to the cookbook.
Hey Scott, you are right. This would be a good cookbook question . I'll probably make it a bit shorter and just focus on the two important keys to result.
Nathan: I just realized that the code I have probably isn't perfect. If I remember right I just replaced all the ?. Well obviously if your sql had something hard coded like,
where name like 'eh?'
then it wouldn't work. You could do a regex search for ? NOT inside quotes and that would probably work better.
Entry posted. Thanks again for the idea Scott!
Just a stupid question...
If you do use that "result" parameter in your cfquery, do you get some of the returned variables twice, and if yes, does it create a lot of overhead?
For example, columnList and recordCount are returned in queryName.* already.
Um, I'd say yes, it does take more work. Is it significant, probably not. That being said, I only use result= when I'm testing stuff.
Don't know about CF7, but in 6.1 you could use <cfabort> right before the </cfquery>: it'll display the query. Not so good code but it works... But there is no way to bypass <cfqueryparam>, that still appears as question marks (?).
Best wishes!