Debugging Queries with CFQUERYPARAM

[Richard7777]

I have recently become a believer in using CFQUERYPARAM in all of my queries.
I fully understand the benefits of their use. There is, however, one
disadvantage that I have found. I was hoping to get some suggestions.

If I create a query WITHOUT using CFQUERYPARAM, and the query fails, I will
get a "nice" database error and the full text of the SQL that failed. I can
then take the SQL and run it through an SQL editor (Oracle or otherwise) and
get more information about the cause of the error.

Using CFQUERYPARAM, I cannot get a full SQL statement. It is full of
"Parameter 1", "Parameter 2", etc. Can anyone offer any suggestions of a
simple way to debug queries that use CFQUERYPARAM?

Given the benefits, I will use CFQUERYPARAM in all of my queries. It would,
however, be nice to see a "dynamic SQL" statement sometimes for debugging.

[Adam Cameron]

It's not exactly what you want, but read this:
"# Added result variables for the SQL statement executed (sql), the number
of records returned (recordcount), whether the query was cached (cached),
an array of cfqueryparam values (sqlparameters), and the list of columns in
the returned query (columnlist)."

From
[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=p art_cfm.htm[/url]

This will be a big help in a lot of situations, I think.
--

Adam

[tconley]

If you're wrapping you're queries in cftry's this can be added to the cfcatch
to
determine what the cfqueryparams where

<cfif StructKeyExists(cfcatch, "Sql")>
<cfoutput>#cfcatch.Sql#<P></cfoutput>
</cfif>
<cfif StructKeyExists(cfcatch, "where")>
<cfoutput>#cfcatch.where#<P></cfoutput>
</cfif>

The cfcatch.sql will contain something like:

SELECT *
FROM table
WHERE id = (param 1)
AND name = (param 2)

The cfcatch.where will contain something like:

(param 1) = [type='IN', class='java.lang.String', value='70',
sqltype='CF_SQL_INTEGER'] ,
(param 2) = [type='IN', class='java.lang.String', value='test',
sqltype='CF_SQL_VARCHAR']

It at least gives you all the information to put the queries back together.