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#
FROM MyTable

This would send the following SQL to SQL Server:

--/admin/myfile.cfm; UserID=12
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.
This solves the problem. The only remaining issue is that the JDBC driver doesn't pass the header comments. Our example query would be sent to the database like so:

FROM MyTable

This is still better than having to rewrite every query in which you are using SQL comments. If you move your comments after the select statement (either at the end of a line or at the end of the query), they will still be passed to SQL Server.

All credit for finding the problem and the solution goes to Will Spurgeon at Tech-It-Easy.

Good luck!

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Thank-you, just ran into this problem perparing to move to a new server ( rule: always leave LOTS of days when transferring to new server before actual switch over - lot less stressful LOL )

Guy Who is Losing More Hair Everyday!
# Posted By John Skrotzki | 12/18/06 3:01 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.