CFQuery value defined in name attribute is not returned when result is used

Description

When including the "result" attribute in a cfquery, the variable used in the "name" attribute does not get returned. Result should display information about the result and query call whereas the variable defined in "name" should return the actual data columns and rows.

When including result, Lucee will throw an error that the name variable does not exist. i.e.

<cfquery name="qryGetData" datasource="mydsn" result="qryGetDataResult">

Lucee will error saying qryGetData is not defined.

Environment

Linux

Activity

Show:
Pothys - MitrahSoft
October 21, 2020, 3:04 PM

I've checked this ticket and confirmed the issue happened lucee affected and the latest version 5.3.8.84-SNAPSHOT also. And I've added a test case for this ticket too.

Pull Request: https://github.com/lucee/Lucee/pull/1082

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

I’ve been looking into the issue and here’s what I’ve found.

When the result attribute is requested, it sets the createGeneratedKeys flag to true. This causes the QueryImpl.execute() method to trigger the setGeneratedKeys() method when updating the query count.

This ultimately calls the SQLServerStatement.getGeneratedKeys(). The problem with this is the source code shows this ends up calling the SQLServerStatement.getNextResult() method if there are no results (see https://github.com/microsoft/mssql-jdbc/blob/276d2ca654c6a4315829bab7df499b194cf0f32c/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerStatement.java#L2205). Because of this call, the resultset is advanced and so when stat.getMoreResults() is called later in the QueryImpl.execute() method, it goes to the next recordset (or sees that it’s at the end).

I think I’m pretty close to a fix for this, but I’m still working my way through all the logic.

Dan Switzer, II
October 29, 2020, 3:50 PM

Ok, I’ve submitted a pull request for this fix:



,

I re-did your test cases in my pull request, because I added a number of different test cases. I also wrote the test case so it would run the queries against both the MSSQL driver and the JTDS driver to make sure both drivers were producing the same results.

Pothys - MitrahSoft
November 4, 2020, 2:37 PM

I've checked this ticket with 's PR. Yes, it works fine now. cfquery name attribute returns the query when the result attribute is used. will confirm about this

KabutoTX
November 10, 2020, 7:02 PM
Edited

Fixes my problem with Query.cfc failing but cfquery and queryexecute working (not using resultset on those).

https://dev.lucee.org/t/mssql-7-2-2-driver-exec-sp-not-working/7608

Assignee

Michael Offner

Reporter

Adam Webster

Priority

New

Labels

Fix versions

None

Affects versions

Configure