adodbapi and output parameters in stored procedures

[M.-A. Lemburg]

On 2008-11-07 15:04, leesquare at yahoo.com wrote:
> Hello,
> 
> I need some help getting output values from my stored procedures when
> using adodbapi.  There's an example
> testVariableReturningStoredProcedure in adodbapitest.py, and that
> works for my system.  But my stored procedure also inserts and
> accesses a table in the database.  Here's what I have it boiled down
> to:
> 
> So, when I have
>             CREATE PROCEDURE sp_DeleteMeOnlyForTesting
>                 @theInput varchar(50),
>                 @theOtherInput varchar(50),
>                 @theOutput varchar(100) OUTPUT
>             AS
>                 SET @theOutput=@theInput+ at theOtherInput
> 
> Then, I can run in python:
>>>> cursor = db.conn.cursor()
>>>> cursor.callproc('sp_DeleteMeOnlyForTesting', ('hello', 'bye', ''))
> [u'hello', u'bye', u'hellobye']
> 
> 
> If I redefined the procedure as
>             CREATE PROCEDURE sp_DeleteMeOnlyForTesting
>                 @theInput varchar(50),
>                 @theOtherInput varchar(50),
>                 @theOutput varchar(100) OUTPUT
>             AS
>                 SELECT * From dbo.testtable
>                 SET @theOutput=@theInput+ at theOtherInput
> 
> Then, the python comes out as :
>>>> cursor = db.conn.cursor()
>>>> cursor.callproc('sp_DeleteMeOnlyForTesting', ('hello', 'bye', ''))
> [u'hello', u'bye', u'']
> 
> My search on the web found a couple of posts with similar problems,
> but no solutions.  I am using SQLOLEDB.1 as Provider, connecting to
> SQL Server 2005.
> 
> Any help appreciated.  I just need one method of passing an output
> parameter back to python.

Note that if you can, you should try to avoid output parameters
in stored procedures.

It's much more efficient to use multiple result sets for these,
so instead of doing

    SELECT * From dbo.testtable
    SET @theOutput=@theInput+ at theOtherInput

you would write

    SELECT * From dbo.testtable
    SELECT @theInput+ at theOtherInput

and then fetch the data using:

cursor.callproc(...)
test_table_result_set = cursor.fetchall()
cursor.nextset()
(output_variables,) = cursor.fetchone()

I don't know whether the above works for adodbapi. It does for mxODBC
and most other DB-API compatible modules that support .nextset().

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Nov 07 2008)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611