Result Set from Stored Procedure Returns Only First Result

[alexdavis24]

Running the following code:

# some params defined
cnxn = connect(driver='{ODBC Driver 17 for SQL Server}', server=srv, database=db, uid=user, pwd=password,
                            turbodbc_options=options)
crsr = cnxn.cursor()
cmd = "EXEC someDB.dbo.TEST_PROC"
crsr.execute(cmd)
result = crsr.fetchallnumpy()
print(result)

where someDB.dbo.TEST_PROC is defined as

CREATE PROC TEST_PROC
AS
SELECT 1
SELECT 2

Rather than getting the final result 2, or both results, only 1 is returned.

Came across this issue when inserting into a temp table in a proc, followed by a select statement, which executed fine using SSMS. When moving to Python/turbodbc, however, I received an error that there was no available result set. Tried fetchall, fetchmany, etc. without much success. Any advice on how to work around this or explicitly pull the last result set would be greatly appreciated.

I am running ODBC Driver 17 for SQL Server, turbodbc 4.1.2 installed through conda-forge, and python 3.9.4. I've been using a turbodbc interface for a couple years, so I am moderately familiar with the package, but this behavior had me stumped!

[pacman82]

For reference: https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/multiple-results

One idea I could imagine is calling SQLMoreResults a configurable amount of times, before creating the cursor.

[alexdavis24]

Thanks - from the webpage/reviewing Turbodbc documentation I didn't see a place where I could call/configure this. Would this be called from turbodbc.make_options or turbodbc.connect?

[pacman82]

@alexdavis24 Oh sorry, there seems to be a misunderstanding here. The feature you are looking for is to my knowledge not implemented in turbodbc. I've been making a suggestion of how to enable your usecase to the contributers.

To my knowledge turbodbc assumes there is one ResultSet per query. So it just greps the first one.

Cheers, Markus

[alexdavis24]

Does returning the this violate the Principal of Least Surprise - running a query in SSMS such as

select 1
create table #tmptbl(val int)

or flipped

create table #tmptbl(val int)
select 1

will return a result set of 1, ie, a result set is always prioritized over a row count or return message. Wouldn't we expect the same behavior to be mirrored in our ODBC connections?

[pacman82]

It's not up to the turbodbc contributers to decide in which order ODBC Driver and Driver manager return result sets. It makes sense though for ODBC to return results in order of execution. This way it gives the application the option to process the first results as the others are still processing.

In terms of these bindings, I feel the tested usecases revolve around a single result set. Due to how ODBC works it is easiest to just always process the first (and in most use cases only) result set.

At the top of my hat I have no idea how to just skip to the last result set without processing each one. This is why I suggested the user provides the domain knowledge of how much results sets should be skipped.

Yet this is only my opinion/knowledge, if others know better I am happy to learn. Just wanted to give an implementation hint from the sidelines. It is unlikely I'll make a PR myself, as I my spare time is pretty much used up by the ODBC rust bindings.

Cheers, Markus

[alexdavis24]

Ok, thank you. We will make sure to keep this in mind when building our queries to interact with Python/Turbodbc.

[david-engelmann]

This should be closed out due to #369