有 tsql 注释时 cfquery 崩溃 [英] cfquery crashes when there are tsql comments

问题描述

这在 ColdFusion 11 中不会崩溃，但会在 ColdFusion 2016 中崩溃

This does not crash in ColdFusion 11, but does crash in ColdFusion 2016

SELECT  *
FROM    dbo.Roles WITH (NOLOCK)
WHERE   Code IS NOT NULL
AND     Active = 1
AND     RoleID IN (SELECT RoleID FROM dbo.Emp WITH (NOLOCK)) -- It's ok to look at termed employees

这两个都可以正常工作

SELECT  *
FROM    dbo.Roles WITH (NOLOCK)
WHERE   Code IS NOT NULL
AND     Active = 1
AND     RoleID IN (SELECT RoleID FROM dbo.Emp WITH (NOLOCK)) 

是否有恢复原始行为的设置?

Is there a setting to restore the orginal behavior?

更新

我以为我有这个问题的最小示例，但我没有.这是完整的查询

I thought I had a minimal example of the issue, but I didn't. Here is the complete query

    SELECT '<ul>' + STUFF (
        (
        SELECT  MIN(Role) AS "li/code", Code AS "li/span/b", 'Unsorted' AS "li/span/var"
        FROM    dbo.Roles WITH (NOLOCK)
        WHERE   Code IS NOT NULL
        AND     Active = 1
        AND     RoleID IN (SELECT RoleID FROM dbo.Emp WITH (NOLOCK)) -- It's ok to look at termed employees
        GROUP BY Code
        FOR XML PATH ('')
        ),
        1,0,''

    ) + '</ul>' AS xmlRole

当我打开数据库调试时:我得到:

When I turn on DB debugging: I get:

spy(http-nio-8500-exec-5)(2016/02/17 10:21:13.807)>> OK

spy(http-nio-8500-exec-5)(2016/02/17 10:21:13.807)>> Statement[4].execute(String sql, int autoGeneratedKeys)
spy(http-nio-8500-exec-5)(2016/02/17 10:21:13.807)>> sql = SELECT '<ul>' + STUFF ( ( SELECT MIN(Role) AS "li/code", Code AS "li/span/b", 'Unsorted' AS "li/span/var" FROM dbo.Roles WITH (NOLOCK) WHERE Code IS NOT NULL AND Active = 1 AND RoleID IN (SELECT RoleID FROM dbo.Emp WITH (NOLOCK)) -- It's ok to look at termed employees GROUP BY Code FOR XML PATH ('') ), 1,0,'' ) + '</ul>' AS xmlRole
spy(http-nio-8500-exec-5)(2016/02/17 10:21:13.807)>> autoGeneratedKeys = 1
spy(http-nio-8500-exec-5)(2016/02/17 10:21:13.807)>>    
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ')'. ErrorCode=102 SQLState=HY000
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ')'.
    at macromedia.jdbc.sqlserverbase.ddcw.b(Unknown Source)
    at macromedia.jdbc.sqlserverbase.ddcw.a(Unknown Source)

请注意，SQL 字符串位于一行中.当评论开始时，它不会结束.-- 之后的所有内容都保留为注释掉.

Note that the SQL String is on a single line. When the comment starts, it does not end. Everything after the -- remains as a commented out.

空白管理已开启.关闭它不会改变行为.生成的SQL是一样的

White space management is turned on. Turning it off does not change the behavior. The generated SQL is the same

推荐答案

根据 Adob​​e 的说法，添加 Update 1 将解决此问题

According to Adobe, adding Update 1 will address this issue

更新 1 中修补的问题列表:https://helpx.adobe.com/coldfusion/kb/bugs-fixed-coldfusion-2016-update-1.html#main-pars_minitoc

List of issues patched in Update 1: https://helpx.adobe.com/coldfusion/kb/bugs-fixed-coldfusion-2016-update-1.html#main-pars_minitoc

更新 1 的详细信息:https://helpx.adobe.com/coldfusion/kb/coldfusion-2016-update-1.html

Details of Update 1: https://helpx.adobe.com/coldfusion/kb/coldfusion-2016-update-1.html

这篇关于有 tsql 注释时 cfquery 崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！