I am struggling to access the results of a stored procedure giving me the identity of the row just inserted using Turbodbc 4.1.2, Python 3.7, and SQL Server 2017.
My procedure runs along the following lines:
CREATE OR ALTER PROCEDURE [dbo].[testSP] @var INT
AS
INSERT INTO testTable VALUES (@var)
SELECT 4 --intermediate step to prove concept
--SELECT SCOPE_IDENTITY() as [scope_id] --final goal
--SELECT @@IDENTITY AS '[scope_id]'
My Turbodbc code looks like this:
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 testSP 1"
crsr.execute(cmd)
df = pd.DataFrame(crsr.fetchallnumpy())
When running the stored procedure without any inserts (ie, just "SELECT 4"), the result set returns fine. However, when running with the insert, which operates correctly, I receive an error "turbodbc.exceptions.InterfaceError: No active result set". The query runs fine in SSMS.
I am guessing that this is because I am receiving two result sets back - one for the insert, and one for the select. I saw from a couple questions on SO that nextset function is available in pymssql and pyodbc, but that the same functionality is not available in turbodbc.
How can I access the second part in my multi-statement query using turbodbc? This seems like a relatively simple issue, but I have been banging my head against the wall for a few hours.
