implementation of callproc?

[andreacassioli]

Hi,
I am considering migrating from pymssql to pyodbc. The only impediment is the lack of an implementation of the callproc cursor function.

The docs says

This is not yet supported since there is no way for pyodbc to determine which parameters are input, output, or both.

I was wondering whether you could use the same approach used in pymssql.

[mkleehammer]

I'll look. If your stored procedure only has input parameters, you can call it quite easily:

cursor.execute('exec sp_test ?, ?', 'one', 'two')

I use a lot of stored procedures and return results by simply selecting the results in the stored procedure. They are then available using the fetch methods. In fact, you can return multiple result sets which you can fetch and then move to the next using nextset().

[andreacassioli]

Hi,
you are right, exec will make the job. However I find callproc very neat and expressive. But I understand that it might not be a priority!

[gordthompson]

In my experience, most of the interest in a callproc-type method (also CallableStatement in JDBC) is for handling output parameters from the stored procedure. In this case, we can use a bit of T-SQL code to retrieve those results.

Example: For a stored procedure

CREATE PROCEDURE [dbo].[test_for_pyodbc] 
    @param_in nvarchar(max) = N'', 
    @param_out nvarchar(max) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- set output parameter
    SELECT @param_out = N'You said "' + @param_in + N'".';
    
    -- also return a couple of result sets
    SELECT N'SP result set 1, row 1' AS foo
    UNION ALL
    SELECT N'SP result set 1, row 2' AS foo;
    
    SELECT N'SP result set 2, row 1' AS bar
    UNION ALL
    SELECT N'SP result set 2, row 2' AS bar;
END

our Python code can do this

conn = pyodbc.connect(conn_str)
crsr = conn.cursor()

sql = """\
DECLARE @out nvarchar(max);
EXEC [dbo].[test_for_pyodbc] @param_in = ?, @param_out = @out OUTPUT;
SELECT @out AS the_output;
"""
params = ("Burma!", )
crsr.execute(sql, params)
rows = crsr.fetchall()
while rows:
    print(rows)
    if crsr.nextset():
        rows = crsr.fetchall()
    else:
        rows = None

to produce this:

[('SP result set 1, row 1', ), ('SP result set 1, row 2', )]
[('SP result set 2, row 1', ), ('SP result set 2, row 2', )]
[('You said "Burma!".', )]

[andreacassioli]

Well, to me is more about write clean and portable code. In particular I like the possibility to pass the parameters just as a simple Python array, leaving to the driver to correctly feed them to the SP including their type.

It is a neat one-liner that makes the code more concise and possibly less error prone.

[gordthompson]

I understand your interest in callproc as "a neat one-liner", but a generic implementation of callproc for any arbirtary ODBC driver might get complicated. For example, you ask

I was wondering whether you could use the same approach used in pymssql.

pymssql tried to use the same approach as the MySQL Connector/Python implementation of callproc which was to have it return the sequence of arguments that were passed to it with the OUTPUT parameters replaced by their updated values. As it turned out, that didn't really work so well for pymssql because under the TDS protocol the output parameter values are sent after all of the result sets generated by the stored procedure itself.

So for an MS_SQL stored procedure, in order to have the output parameter values available immediately after invoking callproc, pyodbc might have to retrieve and cache all of the result sets produced by the stored procedure. That would presumably prevent our Python application from retrieving result sets one-at-a-time and incrementally (via fetchone() or fetchmany()), and could greatly increase the memory requirements of that application if the result sets were large.

[mkleehammer]

@gordthompson is right, as usual. I'm not seeing a clean way to do this. I'm going to close this for now but I'll keep it the back of my mind - maybe something will click.

BTW, @gordthompson thanks for all the good work on the questions and wiki.