by Bouton Jones
Writing SQL is valuable for ColdFusion developers. But being able to debug SQL is essential. Here are some tips and techniques I've learned over the years that have helped me fix logic and programming errors quickly and efficiently.
This is meant to be an overview. I'm not trying to be comprehensive here. I'm just covering the most basic aspects of as many techniques as I can. For more detailed explanations and examples I offer links for the resources that go into more depth.
The following covers ColdFusion versions 7 through 10. Even though as of 2013 the most recent version of ColdFusion is number 10, some ColdFusion shops use ColdFusion 8 and at least one large organization uses ColdFusion 7 for some applications.
For the benefit of new developers, it's worth mentioning in passing the most common debugging techniques.
General ColdFusion Debugging Practices
General SQL Debugging Practices
Here are some links that cover standard CFML debugging in more detail:
CFML books will also explain the basic of debugging. For example, see page 493 (Chapter 17) of the Macromedia ColdFusion MX 7 Web Application Construction Kit By Ben Forta, Raymond Camden, Leon Chalnick, and Angela C. Buraglia. It reads in part:
The keys to successfully debugging SQL statements are as follows:
- Isolate the problem. ...
- ... If you are using ColdFusion fields within your statement, verify that you are enclosing them within quotation marks when necessary. ...
- Look at the bigger picture. ...
- Break complex SQL statements into smaller, simpler statements.
(BTW, you can Buy this book on Amazon.)
Here's the most recent edition:
Before deleting any data, you should check your logic to make sure you are only deleting the data you intended. One quick method for doing so is writing your DELETE statements in the format of SELECT statements. If every row in your table appears in your result set instead of the single record you intended, it's likely that your statement needs some work.
To illustrate: Running this query might delete all of the records the developer doesn't want deleted. (Perhaps the developer mean "=" instead of "!=".
DELETE
FROM tblImportantRecs
WHERE isDeleted != 'Y'
But if you run this next query first, the mistake will be apparent without losing any records in the process.
SELECT *
FROM tblImportantRecs
WHERE isDeleted != 'Y'
This is a preventative measure. Except for the simplest DELETE statements, it's my policy to always write them as SELECT statements first.
CFTRY and CFCATCH are tags that any ColdFusion developer who writes SQL should know. I'm not going into the details of their implementation. If you're not familiar with these tags here's a link: http://livedocs.adobe.com/coldfusion/8/Errors_13.html
And here is an example of their use.
<cftry>
<cfquery name="insComnt" datasource="#request.db#">
INSERT INTO tblComnt
(COMNT_ID
,CREATE_TMSTMP
,COMNT_TXT)
VALUES
(#MyRandomNumber#
,SYSDATE
,<cfqueryparam
value="#TRIM(ATTRIBUTES.comnt_txt)#"
cfsqltype="cf_sql_varchar">)
</cfquery>
<!--- Database Errors. --->
<cfcatch type="Database">
<!--- The message to display. --->
<h3>You've Thrown a Database <b>Error</b></h3>
<div style="text-align: left">
<cfoutput>
<ul>
<li><strong>Message:</strong> #cfcatch.Message#</li>
<li><strong>Native error code:</strong>
#cfcatch.NativeErrorCode#</li>
<li><strong>SQLState:</strong> #cfcatch.SQLState#</li>
<li><strong>Query Error:</strong> #cfcatch.queryError#</li>
<li><strong>Detail:</strong> #cfcatch.Detail#</li>
</ul>
<cfif structKeyExists(cfcatch,"sql")>
<pre>#cfcatch.Sql#</pre>
</cfif>
<cfif StructKeyExists(cfcatch, "where")>
<cfoutput>#cfcatch.where#<P></cfoutput>
</cfif>
</cfoutput>
</div>
<cfif isDefined("cfquery") OR NOT StructIsEmpty(cfquery)>
<cfdump var="#cfquery#" label="cfquery" expand="no"><br />
<br />
</cfif>
<cfdump var="#cfcatch#" label="cfcatch" expand="no"><br />
<br />
<p>The contents of the tag stack are: <cfdump
var="#cfcatch.tagcontext#" label="cfcatch.tagcontext" expand="yes"></p>
</cfcatch>
<!--- unexpected exceptions. --->
<cfcatch type="Any">
<cfsetting requesttimeout="#(CreateObject('java', 'coldfusion.runtime.RequestMonitor').GetRequestTimeout() + 3)#"/>
<cfoutput>
<h1>Other Error: #cfcatch.Type#</h1>
<div style="text-align: left">
<ul>
<li><strong>Message:</strong> #cfcatch.Message#</li>
<li><strong>Detail:</strong> <cfif len(cfcatch.Detail)>#cfcatch.Detail#<cfelse>NULL</cfif></li>
<li><strong>Extended Info:</strong> <cfif len(cfcatch.ExtendedInfo)>#cfcatch.ExtendedInfo#<cfelse>NULL</cfif></li>
</ul>
</div>
</cfoutput>
<cfdump var="#cfcatch#" label="cfcatch" expand="no"><br />
<CFIF structKeyExists(application,"name")>
<p>application.name: #application.name#</p>
</CFIF>
<cfabort>
</cfcatch>
</cftry>
I'd suggest borrowing the CFCATCH code above for your own SQL development. It's an amalgamation of CFML code from around the web.
If you use SQL in a ColdFusion environment you must already be familiar with the CFQUERY tag. But are you familiar with the CFQUERY tag's Result attribute? It can offer valuable information about your queries.
Again, I offer a link --- http://livedocs.adobe.com/coldfusion/8/Tags_p-q_17.html --- and an illustration of it's use.
<cfquery name="insComnt" datasource="#request.db#"
result="queryResult">
INSERT INTO tblComnt
(COMNT_ID
,CREATE_TMSTMP
,COMNT_TXT)
VALUES
(#MyRandomNumber#
,SYSDATE
,<cfqueryparam
value="#TRIM(ATTRIBUTES.comnt_txt)#"
cfsqltype="cf_sql_varchar">)
</cfquery>
<cfdump
var="#queryResult#"
label="queryResult"
expand="yes"><br /><br />
<cfif structKeyExists(queryResult,"sql")>
<div style="border-style:solid; border-color: ##000; width:95%">
<tt>
<cfoutput>
<pre>#queryResult.Sql#</pre>
</cfoutput>
</tt>
</div>
</cfif>
Unfortunately, the params appear as question marks (?) and are listed below the query. That necessitates additional cutting and pasting.
When something goes wrong in SQL, ColdFusion error message aren't always as helpful as we would like. Some important details can be lost in the errors that the ColdFusion server returns. Sometimes you need to run the same queries in the SQL developer's environment --- E.G. TOAD for Oracle or SQL Management Studio for SQL Server --- to get valuable details. But the exact same query --- including the same parameters --- might be difficult to reproduce --- especially if the code is dynamic with a lot of conditional logic like CFIF statements and passed variables.
As suggested earlier, having debug on can help. But debug displays the queries and the parameters separately and it can be a pain to combine them. Plus, debug will only display queries that are successful. The queries that you most need to understand are the queries that won't display in the debug.
For those situations, the following method will help you reproduce your problem SQL code quickly and accurately.
To illustrate:
<cfoutput>
<pre>
INSERT INTO tblComnt
(COMNT_ID
,CREATE_TMSTMP
,COMNT_TXT)
VALUES
(#MyRandomNumber#
,SYSDATE
,'#TRIM(ATTRIBUTES.comnt_txt)#';)
</pre>
</cfoutput>
A more programmic technique is to use the GetMetaData function.
<cfquery name="insComnt" datasource="#request.db#">
INSERT INTO tblComnt
(COMNT_ID
,CREATE_TMSTMP
,COMNT_TXT)
VALUES
(#MyRandomNumber#
,SYSDATE
,<cfqueryparam
value="#TRIM(ATTRIBUTES.comnt_txt)#"
cfsqltype="cf_sql_varchar">)
</cfquery>
<pre><cfoutput>#insComnt.getMetaData().getExtendedMetaData().sql#</cfoutput></pre>
You can learn more about using the getMetaData function at the following links.
Unfortunately, one disadavantage of using the GetMetaData function --- and the CFQUERY's Result Attribute which I covered earlier --- is that the params appear as question marks (?) and are listed below the query. The simpler technique --- with the PRE and CFOUTPUT tag paird --- integrates the params into the statement without additional cutting and pasting.
Ben Nadel offers a JavaScript based technique ("Merging ColdFusion SQL Debugging And Query Params With Javascript") at http://www.bennadel.com/blog/459-Merging-ColdFusion-SQL-Debugging-And-Query-Params-With-Javascript.htm. His bookmarklet combines the SQL statement with the param values dynamically.
With SELECT statements it's easy to gauge the success of your work. No so much with INSERT, UPDATE, or DELETE statements. But there are techniques for seeing the results of your non-SELECT statement queries.
The simplest way is to run a SELECT query immediately after the INSERT, UPDATE, or DELETE query. Both queries must be inside a CFTRANSACTION tag pair to prevent changes to the database between queries.
<cftransaction>
<cfquery name="insComnt" datasource="#request.db#"
result="queryResult">
INSERT INTO tblComnt
(COMNT_ID
,CREATE_TMSTMP
,COMNT_TXT)
VALUES
(#MyRandomNumber#
,SYSDATE
,<cfqueryparam
value="#TRIM(ATTRIBUTES.comnt_txt)#"
cfsqltype="cf_sql_varchar">)
</cfquery>
<cfquery name="chkComnt" datasource="#request.db#">
SELECT *
FROM tblComnt
WHERE COMNT_TXT = <cfqueryparam
value="#TRIM(ATTRIBUTES.comnt_txt)#"
cfsqltype="cf_sql_varchar">
AND CREATE_TMSTMP = SYSDATE
</cfquery>
</cftransaction>
<cfdump var="#chkComnt#" label="chkComnt"><br />
<br />
The various flavors of SQL have functions that accomplish the same thing --- but more elegantly and with fewer lines of code. Often it can be done with a single pair of CFQUERY tags and without a CFTRANSACTION tag pair.
With Oracle you can use the returning clause. Alistair Lattimore explains its use in an article at http://www.lattimore.id.au/2006/04/06/oracle-returning-clause/.
The article by Abram Adams at http://tutorial480.easycfm.com/ offers functions such as the LAST_INSERT_ID() function specific to MySQL and the Scope_Identity() function for SQL Server.
As I've written elsewhere, it's preferable to let SQL do the heavy lifting for ColdFusion and these functions are a perfect examples.
The isDebugMode() function will allow you to run code only when debugging is enabled.
<cfif IsDebugMode()>
<cfdump var="#getComnt#" label="getComnt">
</cfif>
In this example I don't need to worry about accidentally removing the CFDUMP tag before moving my code into testing or production. Because debugging is disabled in those environments, the CFDUMP tag won't run.
To review, here are a few tips and techniques for ColdFusion developers to debug their SQL statements:
Hopefully these tips and techniques are helpful. While I didn't cover the topic in detail, the links I included should provide the missing information.
If you have any other techniques to suggest, please send them to me. I can always afford to improve my debugging skills. Unless you tell me otherwise, I intend to properly credit the people who supplied me with the tips and techniques that I include. So let me know how you wanted to be credited (or not.)