Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Issues with multiple SQL statements in a single <cfquery /> (MSSQL)

Description

I was attempting to grab a new ID from an MS SQL database server (2008) when I came across this.

<cfquery name="q"> DECLARE @id UNIQUEIDENTIFIER; SET @id = NEWID(); INSERT INTO MYTABLE (id) VALUES (@id); SELECT @id as id; </cfquery>

At the end of the statement q is a string (not a Query object), and ID does not exist. Now if I place the SELECT statement before the INSERT it works:

<cfquery name="q"> DECLARE @id UNIQUEIDENTIFIER; SET @id = NEWID(); SELECT @id as id; INSERT INTO MYTABLE (id) VALUES (@id); </cfquery>

I can get the ID at q.id, and the INSERT successfully completes as well. Here is where it got very weird. If an error is thrown in the INSERT statement in the last example where the INSERT is the second query run, the program continued to run and a db error was never thrown (and the record was of course not inserted since an error was thrown). I found this only when the ID written in this query was not available in an FK constraint a few method calls later.

Environment

None

Activity

Show:

dan.switzer@givainc.com 18 May 2021 at 18:33

This appears fixed in 5.3.8.175-RC.

Dan Switzer, II 29 October 2020 at 18:34

This issue should be addressed in the following pull request:

https://github.com/lucee/Lucee/pull/1095

Dan Switzer, II 23 October 2020 at 21:33

You are correct. I added a comment in https://luceeserver.atlassian.net/browse/LDEV-3102 with my findings so far into the issue.

Zac Spitzer 23 October 2020 at 21:16

Dan Switzer, II 23 October 2020 at 17:31

So I’ve been looking into resolving this issue. In our case, the problem is a bug within Lucee when the <cfquery /> tag has a result attribute. When the result attribute is added, then the MSSQL driver does not get the resultset. Leave the attribute off and everything works fine. Both work correctly when using the JTDS driver.

Fixed

Details

Assignee

Reporter

Priority

Labels

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Affects versions

Created 29 April 2016 at 17:42
Updated 18 May 2021 at 18:58
Resolved 11 May 2016 at 06:49

Flag notifications