SQL Server Comments and ColdFusion MX 7

This problem (and solution) comes from my good friend Will Spurgeon of Tech-It-Easy. He has long been using SQL comments when developing for SQL Server. The advantage of this approach is that you can see these comments when reviewing transactions in SQL Server Profiler. It can really help in a lot of trouble-shooting situations.

For example:

<cfquery name="qMyQuery" datasource="#request.mydsn#">
--#CGI.SCRIPT_NAME#; UserID=#Session.UserID#
SELECT *
FROM MyTable
</cfquery>

This would send the following SQL to SQL Server:

--/admin/myfile.cfm; UserID=12
SELECT *
FROM MyTable

If you run a trace in SQL Profiler, this would allow you to see which file a query was run from and by which user. The first line doesn't execute because the "--" represents a single-line comment in SQL Server (that is, it comments everything from the "--" to the end of the line).

In versions of ColdFusion prior to version 7, the only problem with this approach is that the comments are removed from the SQL code sent to the database when using <cfqueryparam> or stored procedures. Personally, I really like the extra documentation provided by SQL comments, but I prefer the security provided by <cfqueryparam>.

Back to our story. When this code is moved to ColdFusion 7 (using the "ODBC Connector" driver), a terrible surprise occurs. The ODBC connector puts all of the SQL code on one line (no problems if you are using the default SQL Server driver). So, our example query becomes:

--/admin/myfile.cfm; UserID=12 SELECT * FROM MyTable

SQL Server interprets this as one big comment and doesn't execute any code or send any result back to ColdFusion. ColdFusion gets no result and therefore doesn't assign a value to the variable (thereby leaving it undeclared). Any code trying to use "qMyQuery", throws an error saying that "qMyQuery" isn't defined. Nice.

What's a poor programmer to do? So says Will:

The Fix to this is to use the MS SQL driver within ColdFusion, which uses JDBC instead of ODBC. Iâ¬"ve had a lot of trouble getting this driver connected, but the problem can be solved by enabling TCP/IP in the SQL Server Properties | Network Configuration, setting itâ¬"s port# to 8433, and then using that port# within the ColdFusion datasource. The default port# is 1433, which every slammer program in the world knows, so Windows XP sp2 and lots of other things have a block on that port. So use a different number, like 8433.