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.
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:
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.
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.
You are correct. I added a comment in with my findings so far into the issue.
This issue should be addressed in the following pull request: