6

Update: According to Adobe, adding Update 1 will address this issue

Fixed Bug ID: 4126398 Description: SQL comments inside CFQuery are not ignored.

ColdFusion 2016 strips newlines and executes queries on a single line. Breaking all queries with SQL style comments. This behavior is not found in CF10 or CF11. 

<cfquery name="test" datasource="MSSQL_master">
      select
        UserID, --primary key field
        FirstName
        from Users
</cfquery>

yields:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','.

If anyone is aware of some sort of configuration setting or change to fix this I'd love to hear about it.

Community
  • 1
  • 1
gnarbarian
  • 2,622
  • 2
  • 19
  • 25
  • 1
    The issue was raised with Adobe over a month ago. https://bugbase.adobe.com/index.cfm?event=bug&id=4126398 - as far as I can tell, the only options you have are to remove all SQL comments or convert them to ColdFusion comments if you can. – TRose May 06 '16 at 01:56
  • @Leigh it is a different manifestation of something that may be the same bug. I was unable to find the post you linked after searching. – gnarbarian May 06 '16 at 09:55
  • @gnarbarian - Yes, it is a real doozy of a bug. As you say, the error messages differ depending on the query, but it is the same root cause. Not sure if they released the fix yet. Wish S.O. would allow dupe linking to threads that do not have an official answer, because in this case the answer is the comments: it is a bug. The question is a duplicate, but is definitely *on-topic*. – Leigh May 06 '16 at 16:25
  • Hey @Leigh, if you answer that question I will vote for it and it will be available for the next time. – Dan Bracuk May 06 '16 at 16:33
  • 1
    @DanBracuk have to say that I would hate to come across either one of these issues closed without an actual answer that leads to a solution. – gnarbarian May 06 '16 at 16:42
  • @DanBracuk - I left a comment for James asking if he could promote his comment to an answer, since he both found it and reported it. He may not have seen it yet though. – Leigh May 06 '16 at 17:12
  • 1
    There is currently no solution. Hopefully a service pack will be released soon. – James A Mohler May 06 '16 at 21:22
  • @JamesAMohler ya, there's nowhere to download CF11 from adobe either as a stop gap in the meantime. I've always found it odd how quickly they remove old versions from their download page. It makes it pretty difficult to set up dev environments that match production instances. – gnarbarian May 06 '16 at 21:33
  • 1
    I wish there was an easy find and replace to identify these t-sql comments. – James A Mohler May 06 '16 at 21:57
  • @JamesAMohler well, Depending on your editor you could use some regex capture groups to convert the comments into CF comments. something like: (--.*\n) then use something like: as the replace value. BUT there's no way in hell I'm going to update every query in every app for an issue that will be patched soon. – gnarbarian May 06 '16 at 22:00
  • 1
    FYI - [According to Adobe, adding Update 1 will address this issue](http://stackoverflow.com/a/37149701/104223): Fixed Bug ID: 4126398 Description: SQL comments inside CFQuery are not ignored. – Leigh May 11 '16 at 16:32

0 Answers0