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.

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:

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, II
July 30, 2020, 6:15 PM
Edited

According to what I’m reading, the MSSQL driver does support multiple queries. However, it looks like Lucee isn’t handling them correctly.

This is from Microsoft’s documentation:
https://docs.microsoft.com/en-us/sql/connect/jdbc/using-multiple-result-sets?view=sql-server-ver15

We have some queries that look like this in our application:

In ACF, the query will return a recordset containing the inserted query, but not Lucee.

The jTDS driver does work, but would like to use the MSSQL driver for performance reasons.

Dan Switzer, II
October 23, 2020, 5:31 PM

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.

Zac Spitzer
October 23, 2020, 9:16 PM

that’s

Dan Switzer, II
October 23, 2020, 9:33 PM

You are correct. I added a comment in with my findings so far into the issue.

Dan Switzer, II
October 29, 2020, 6:34 PM

This issue should be addressed in the following pull request:

Fixed

Assignee

Unassigned

Reporter

Nicholas Tunney

Priority

New

Labels

Fix versions

None

Affects versions

Configure