Experiment in Tweaking ColdFire (for MS SQL)

Most of us love cfqueryparam .. except when it comes to debugging. Unfortunately, when you use cfqueryparam, the basic CF debugging template displays parameter values separate from the actual sql statements. Understandable, given how queries are handled behind the scenes. But as a developer you typically want to see human readable queries, with the parameter values.

This limitation sent me searching for a new debugging tool last week. The search led me to the fantastic Firefox plugin ColdFire (by Raymond Camden, Adam Podolnick and Nathan Mische). It has been around for a while, but I only started using it a few days ago. So far I think it is an amazing tool!

Now since my main interest was database queries, I installed it and jumped right to the DB Queries tab. At first glance the results looked perfect: sql you can copy and paste into an query window. After running a few more queries I noticed a few small things I wanted to tweak. (Yes I know ... give them the moon and they still want more).

1. CfSQLTypes appear to be case sensitive. So upper case types like "CF_SQL_VARCHAR" are not recognized
2. NULL values are inserted as an empty string instead of the keyword "NULL"
3. Binary data is displayed as a comma delimited list
   

Since I was really curious about the plugin, I took a shot at customizing it.

Item number 1 was easily changed. I looked inside the debug template (WEB-INF\debug\coldfire.cfm) and found a few lines that perform a case sensitive search on cfsqltypes. A simple change from ListFind to ListFindNoCase was all that was needed.

<cfset parameters[x][1] = ListFind(cfsqltypes,attr.sqltype)>

Item 2 was not as easily solved. In fact, I do not really think it can be solved at all. ColdFire retrieves information from the ColdFusion debugger. The problem is the ColdFusion debugger treats NULL parameter values as an [empty string]. That is fine for non-character cfsqltypes like dates, numbers, etcetera. An empty string is obviously not a valid date or number. So you can determine when the parameter is NULL. But with character types, like cf_sql_varchar, it is impossible to determine when the parameter value is really an empty string and when it is really a NULL. So my partial solution was to always treat empty strings as NULL. This was achieved by making a small modification to one of the plugin's javascript files

File: coldfire_v1.2.51.70.xpi\chrome\coldfire.jar\content\lib.js

 formatParamValue: function( param ) {
     var type = param[0];
     var value = param[1];
     var tmpVal = value;
     if (value.length == 0) {
         // treat empty strings as NULL
         tmpVal = "NULL";
     } else if (type == 2){
     // ... etcetera
 },

I used the MS SQL Profiler to help with item 3 (binary values). The profiler showed that binary parameters were passed to MS SQL as hex. So I made a few small tweaks to the debug template (WEB-INF\debug\coldfire.cfm) to encode any binary values as hex. I also added a few of the other binary types to the list of cfsqltypes.

...
<cfif isBinaryType AND isBinary(attr.value)>
<cfset parameters[x][2] = "0x"& binaryEncode(attr.value, "hex")> 
<cfelse>
<cfset parameters[x][2] = attr.value> 
</cfif>

The new results were a query I could copy and paste into an MS SQL query window.



Now I doubt these tweaks would be suitable for everyone, but they produced the results I needed. It was also a lot of fun learning about the plugin and trying to understand how it works. While I liked the tool before, delving into the code really increased my appreciation for all of the great work put into it by the creators: Adam Podolnick, Nathan Mische and Raymond Camden.