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

[Vernon Cole]

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
>
>