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 <rwup...@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@python.org
> http://mail.python.org/mailman/listinfo/python-win32
>
_______________________________________________
python-win32 mailing list
python-win32@python.org
http://mail.python.org/mailman/listinfo/python-win32
