[python-win32] Re: adodbapi and stored procedure output parameters on

[Roger Upole]

Randy Syring wrote:
>I found this post:
>
> http://bytes.com/groups/python/38259-sql-server-stored-prcedures-output-parameters
>
> which was helpful in getting me started.  However, that method does not
> yield the output parameters if a recordset is output in the SP.  So, if
> my SP is:
>
>    CREATE PROCEDURE sp_test_only_output
>    @param INTEGER OUTPUT
>    AS
>    BEGIN
>    select @param = 10
>    END
>
> Then:
>
>    assert [10] == self.cur.callproc('sp_test_only_output',(999,))
>
> But, if my SP is:
>
>    CREATE PROCEDURE sp_test_output_and_select
>    @param INTEGER OUTPUT
>    AS
>    BEGIN
>    select 'one', 'two'
>    select @param = 10
>    END
>
> then:
>
>    self.cur.callproc('sp_test_output_and_select',(999,)) == [999]
>
> A full test that can be run with Nose: http://paste.pocoo.org/show/118219/
>
> Can you please help me figure out what is going on.  I realize output
> parameters are not the best solution, but I am working with systems that
> I can't change the SPs on and would like to use Python rather than be
> forced into using a different language.
>
> Thank you!
>

I've run into this somewhere before.  It turns out that the output parms are
actually returned as an extra recordset, so you need to call NextRecordset
before trying to access them.

>From digging into the source, if you add
rs.NextRecordset() at line 741 of adodbapi.py, the output parm
is returned as expected.

           Roger