Skip to main content
Participating Frequently
July 29, 2011
Question

Single Line T-SQL Comment using double dash causing error in CF9

  • July 29, 2011
  • 2 replies
  • 3284 views

I am having an issue that appears to be a difference between CF 8.0.1 and CF 9.0.1

it manifests sometimes when a single line T-SQL comment is used within a CFQUERY tag

We run a Windows Server O/S and SQL Server 2005.

Use the commented out SQL below to create the test table, then run a CFM page with the rest of the code.

The first two queries work fine in both CF 8.0.1 and CF 9.0.1, but the third query fails in CF9

I know that the work around is to use /*THIS KIND OF COMMENT*/ instead of --This Kind Of Comment

but it would take alot of work to go thru our codebase to hunt down and change all these lines of code.

What happened to CFQUERY between CF8 and CF9 that is is acting as if it executes a SQL string that disobeys carriage returns?

<!---

CREATE TABLE [dbo].[ztblTest](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [field1] [int] NULL,
    [field2] [int] NULL
) ON [PRIMARY]
go

set identity_insert dbo.ztblTest on
go

INSERT INTO dbo.ztblTest
  (id, field1, field2)
  VALUES (1, 1, 2)
INSERT INTO dbo.ztblTest
  (id, field1, field2)
  VALUES (2, 2, 4)
INSERT INTO dbo.ztblTest
  (id, field1, field2)
  VALUES (3, 3, 6)
INSERT INTO dbo.ztblTest
  (id, field1, field2)
  VALUES (4, 4, 😎
INSERT INTO dbo.ztblTest
  (id, field1, field2)
  VALUES (5, 5, 10)
go

set identity_insert dbo.ztblTest off
go

--->

<br /><br />

<cfset varDoubleMe = 4>

<cfquery name="qTest2" datasource="#application.dsn#">

select *
from ztblTest--field1 = #varDoubleMe#
where 1=1
and --comment

<cfif varDoubleMe EQ 2>field1 = #varDoubleMe#
<cfelse>2=2
</cfif>
order by id

</cfquery>

QTEST2<br /><br />
<cfoutput query="qTest2">
field1:#field1#,field2:#field2#<br />
</cfoutput>

<!--- XXXXXXXXXXXXXXXXXXXX  --->


<br /><br />

<cfset varDoubleMe = 1>

<cfquery name="qTest3" datasource="#application.dsn#">

select *
from ztblTest--field1 = #varDoubleMe#
where --comment
2=2
order by id

</cfquery>

QTEST3<br /><br />
<cfoutput query="qTest3">
field1:#field1#,field2:#field2#<br />
</cfoutput>

<!--- XXXXXXXXXXXXXXXXXXXX  --->

<br /><br />

<cfset varDoubleMe = 2>

<cfquery name="qTest" datasource="#application.dsn#">

    select *
    from ztblTest
    where 1=1
    and --field1 = #varDoubleMe#

    <cfif varDoubleMe EQ 2>field1 = #varDoubleMe#
    <cfelse>2=2
    </cfif>
    order by id

</cfquery>

QTEST<br /><br />
<cfoutput query="qTest">
field1:#field1#,field2:#field2#<br />
</cfoutput>

    This topic has been closed for replies.

    2 replies

    BenFusedAuthor
    Participating Frequently
    August 1, 2011

    Work around is to change ColdFusion Administrator setting Whitespace Management to OFF.

    Thanks to Avatar for the work around, and to cfSearching for being helpful and responsive.

    Inspiring
    July 29, 2011

    I am running 9.0.1, and cannot test it with 9.

    What is the actual error message? Have you tried running the sql profiler to see what sql is actually being sent to the database? That might give you some clue what is going on.

    BenFusedAuthor
    Participating Frequently
    July 29, 2011

    Here is the error message

    Error Executing Database Query.

    [SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'order'.

    The error occurred in test.cfm: line 60

    58 : and --field1 = #other#

    59 :

    61 : order by id

    62 :

    Inspiring
    July 29, 2011

    That does not look like it came from running the samples above. The variables are different.

    I think the best way to know what is really happening is to view the raw statements being sent to the database. Did you try using the sql profiler?

    --Leigh