Repository navigation
Implement callproc() for full DB-API 2.0 compliance #515
Description
Activity
Hi David Levy (@dlevy-msft-sql), thank you for opening this issue!
Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.
If you have additional information to share, please feel free to update the issue.
Thank you for your patience!
It isn't completely clear to me how one would go about implementing this in a way that both conforms to PEP 249 and preserves SQL Server's normal streaming behavior.
The PEP says:
Call a stored database procedure with the given name. The sequence of parameters must contain one entry for each argument that the procedure expects. The result of the call is returned as [a] modified copy of the input sequence. Input parameters are left untouched, output and input/output parameters replaced with possibly new values.
The procedure may also provide a result set as output. This must then be made available through the standard .fetch*() methods.
The Microsoft documentation says:
For some drivers, output parameters and return values are not available until all result sets and row counts have been processed. For such drivers, output parameters and return values become available when SQLMoreResults returns SQL_NO_DATA.
That appears to be true for Microsoft's SQL Server ODBC driver, because SQL Server sends output parameter values and return status only after all result sets and DONE tokens have been sent.
Given those semantics, I can only think of two possible implementation approaches:
- The
callproc()implementation internally consumes the results sets generated by the stored procedure in order to obtain the output parameter values before returning to the caller. That would make those results sets unavailable to the application, effectively rendering procedures created to stream results sets unusable. - The
callproc()implementation fetches and caches all results sets internally, then serves them back through the normal fetch APIs after the output parameters become available. In the general case that would require buffering arbitrarily large result sets client-side, potentially imposing very large memory and/or storage requirements and defeating normal streaming behavior.
Am I missing some third implementation strategy here, or is this simply a fundamental mismatch between PEP 249's
callproc()contract and SQL Server's protocol semantics?Reacted by Gord Thompson- The
It isn't completely clear to me how one would go about implementing this in a way that both conforms to PEP 249 and preserves SQL Server's normal streaming behavior.
The PEP says:
Call a stored database procedure with the given name. The sequence of parameters must contain one entry for each argument that the procedure expects. The result of the call is returned as [a] modified copy of the input sequence. Input parameters are left untouched, output and input/output parameters replaced with possibly new values.
The procedure may also provide a result set as output. This must then be made available through the standard .fetch*() methods.The Microsoft documentation says:
For some drivers, output parameters and return values are not available until all result sets and row counts have been processed. For such drivers, output parameters and return values become available when SQLMoreResults returns SQL_NO_DATA.
That appears to be true for Microsoft's SQL Server ODBC driver, because SQL Server sends output parameter values and return status only after all result sets and DONE tokens have been sent.
Given those semantics, I can only think of two possible implementation approaches:
- The
callproc()implementation internally consumes the results sets generated by the stored procedure in order to obtain the output parameter values before returning to the caller. That would make those results sets unavailable to the application, effectively rendering procedures created to stream results sets unusable. - The
callproc()implementation fetches and caches all results sets internally, then serves them back through the normal fetch APIs after the output parameters become available. In the general case that would require buffering arbitrarily large result sets client-side, potentially imposing very large memory and/or storage requirements and defeating normal streaming behavior.
Am I missing some third implementation strategy here, or is this simply a fundamental mismatch between PEP 249's
callproc()contract and SQL Server's protocol semantics?You're right, and pyodbc reached the same conclusion; They explicitly decline to implement .callproc(). The documented workaround is the same anonymous
EXEC ... @p = @out OUTPUT; SELECT @outpattern users already use here. The investigation thread (pyodbc#184) hits exactly the wall you describe.Drivers that speak TDS directly can take advantage of this. For example, Microsoft.Data.SqlClient (.NET) and microsoft/go-mssqldb (Go) expose output parameters and return status as first-class concepts (via SqlParameter.Direction and sql.Out{Dest: ...} respectively). At the TDS layer, those values arrive as discrete
RETURNVALUE (0xAC)andRETURNSTATUS (0x79)tokens associated with the bound parameters of the RPC request, rather than as something the client has to fish out of a final result set after draining everything else.For mssql-python, the current answer is the same as pyodbc's: keep raising NotSupportedError and document the workaround. Looking forward, the driver is already on trajectory toward speaking TDS more directly, and callproc() is one of the motivating factors to continue down that path. I'd frame this less as "PEP 249 vs. SQL Server" and more as "PEP 249 vs. ODBC".
Reacted by Gord Thompson and Bob Kline- The
Good to know, thanks. 👍
I guess I assumed—mistakenly, as it turns out—that Microsoft wouldn't have come up with an ODBC architecture which hobbled the capabilities of its own flagship DBMS. 🤔
Reacted by David Levy
Summary
callproc()currently raisesNotSupportedError. It is the only remaining gap to full PEP 249 (DB-API 2.0) cursor interface compliance.Current behavior
Workaround
Users must use
cursor.execute()with eitherEXECor the ODBC{CALL ...}escape sequence:Expected behavior
callproc()should execute a stored procedure and return modified copies of the input parameters, per the PEP 249 specification.Impact
The driver currently reports
apilevel = '2.0'and the cursor interface is listed as "Mostly compliant" solely because of this gap.