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

[Randy Syring]

Vernon,

I have now also tested this with SQL Server Express 2005 on vista and am 
still getting an error. 

I am adding my previous email to you here for the benefit of the list:

    On both XP and Vista, I am using 2.2.5.  I don't see a 2.2.6 on
    sourceforge. 

    As for the SQL Server versions, I have tested on both 2000 and
    2005.  I am currently in the process of installing the SQL Express
    2005 version to see if I get a different result.

    FYI, you can get SQL Server Management Studio Express for free
    (replaces Query Analyzer in 2005):

    http://www.microsoft.com/Sqlserver/2005/en/us/express-down.aspx#SP3

    Direct download for the studio:

    http://go.microsoft.com/fwlink/?linkid=65110

--------------------------------------
Randy Syring
RCS Computers & Web Solutions
502-644-4776
http://www.rcs-comp.com

"Whether, then, you eat or drink or 
whatever you do, do all to the glory
of God." 1 Cor 10:31



Vernon Cole wrote:
> Randy:
> Just to make sure...
> what version of adodbapi are we talking about?
>
>   
>>>> import adodbapi
>>>> adodbapi.version
>>>>         
> 'adodbapi v2.2.6 '
>   
> --
> Vernon
>
> On Tue, May 26, 2009 at 1:16 PM, Randy Syring <randy at rcs-comp.com> wrote:
>   
>> Vernon,
>>
>> I didn't mention this in my last email, but I also tried the:
>>
>> SET  @param = 10
>>
>> syntax, but that didn't change anything.  I am still getting a failing test
>> on 2.5 (XP) and (2.6) Vista.
>>
>> --------------------------------------
>> Randy Syring
>> RCS Computers & Web Solutions
>> 502-644-4776
>> http://www.rcs-comp.com
>>
>> "Whether, then, you eat or drink or
>> whatever you do, do all to the glory
>> of God." 1 Cor 10:31
>>
>>
>> Vernon Cole wrote:
>>
>> 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
>>
>>
>>
>> _______________________________________________
>> python-win32 mailing list
>> python-win32 at python.org
>> http://mail.python.org/mailman/listinfo/python-win32
>>
>>
>>     
>
>   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-win32/attachments/20090526/168462dc/attachment.htm>