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.