When you're working on someone else's code and you get an error message (because I know you'd never write code that errors), CF usually does a pretty good job of giving you the info you need to debug the issue. But for some reason they don't show you query parameters, even though they're included in the exception object. Fortunately, it's easy to get them. Crack open /WEB-INF/exception/coldfusion/runtime/DatabaseException.cfm and apply this patch:
Index: WEB-INF/exception/coldfusion/runtime/DatabaseException.cfm
===================================================================
--- WEB-INF/exception/coldfusion/runtime/DatabaseException.cfm   (revision 5924)
+++ WEB-INF/exception/coldfusion/runtime/DatabaseException.cfm   (working copy)
@@ -11,6 +11,7 @@
    errorProperties = structNew();
    if(bRobustEnabled) {
       if( isdefined("error.sql")) errorProperties.sql = error.sql;
+Â Â Â Â Â Â if( isdefined("error.where")) errorProperties.parameters = REReplace(trim(error.where), ",\s*\(", ",#chr(10)#(", "all");
       if( isdefined("error.datasource")) errorProperties.datasource = error.datasource;
       if( isdefined("error.nativeerrorcode") AND error.nativeerrorcode NEQ 0) errorProperties.vendorErrorCode = error.nativeerrorcode;
       if( isdefined("error.sqlState") AND error.sqlState NEQ "n/a") errorProperties.sqlState = error.sqlState;
It's hardly an elegant format, but it at least gives you the parameter values, which can be invaluable in troubleshooting. But if you want to get even better, patch /WEB-INF/exception/detail.cfm as well. This will PRE your SQL, which makes it MUCH easier to read.
Index: WEB-INF/exception/detail.cfm
===================================================================
--- WEB-INF/exception/detail.cfm   (revision 5924)
+++ WEB-INF/exception/detail.cfm   (working copy)
@@ -227,8 +227,8 @@
               </cfcatch>
                </cftry>
       <tr>
-Â Â Â Â Â Â Â Â Â <td><font style="COLOR: black; FONT: 8pt/11pt verdana">#xmlFormat(m)#</font></td>
-Â Â Â Â Â Â Â Â Â <td><font style="COLOR: black; FONT: 8pt/11pt verdana"> #htmlEditFormat(val)#</font></td>
+Â Â Â Â Â Â Â Â Â <td valign="top"><font style="COLOR: black; FONT: 8pt/11pt verdana">#xmlFormat(m)#</font></td>
+Â Â Â Â Â Â Â Â Â <td><font style="COLOR: black; FONT: 8pt/11pt verdana"><cfif error.type EQ "java.sql.SQLException" AND listFindNoCase("sql,parameters", m) GT 0><pre>#htmlEditFormat(REReplace(val, "#chr(10)#\s*#chr(10)#", chr(10), "all"))#</pre><cfelse> #htmlEditFormat(val)#</cfif></font></td>
       </tr>
            </cfif>
        </cfloop>
With these patches applied, an error might generate a page like this.
If you wanted to go crazy, you could do what the Railo guys do and substitute the parameter values back into the SQL statement, so you get an "effective" SQL statement in the error, rather than the passed statement (with parameter placeholders). I'm not sure that's necessarily good, since knowing what values are parameters and what are static can be useful, but for the common use case (wanting to copy and paste the query to debug it in your DB tool), it makes a lot of sense.
Note: These patches were made against CF 8.0.1. They may or may not apply cleanly against other versions of CF.
Can you show an image of what the new output would look like? Trying to see this visually…
Sami,
I added a link to a screenshot in the post body. The error in this case is just the extra 'd' at the very end of the query; I just needed an exception.
Sharp, really helpful.
[...] few weeks ago I posted about fixing CF's DB error messages so that they include query params you passed as well as the raw SQL of the query. I also supplied [...]