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

[Vernon Cole]

According to PEP 249...
        .callproc(procname[,parameters])
            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 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.

In this case, the proceedure outputs TWO record sets, since there are
two SELECT statements.
If the second select statement, "select @param = 10" were changed to
"SET  @param = 10" then, I believe, the result
would have been as expected.
  As it stands, the Python programmer should be required to do a
cursor.nextset() and a .fetch() in order to read the result of the
second SELECT statement.

     .nextset()
            This method will make the cursor skip to the next
            available set, discarding any remaining rows from the
            current set.

            If there are no more sets, the method returns
            None. Otherwise, it returns a true value and subsequent
            calls to the fetch methods will return rows from the next
            result set.

Having said all that, I ran Randy's test program using pywin32 v213 on
Python 2.6 and Vista, and it ran correctly.
--
Vernon

On Fri, May 22, 2009 at 6:50 AM, Roger Upole <rwupole at msn.com> wrote:
> 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
>
> _______________________________________________
> python-win32 mailing list
> python-win32 at python.org
> http://mail.python.org/mailman/listinfo/python-win32
>