adodbapi and output parameters in stored procedures

[Roger Upole]

<leesquare at yahoo.com> wrote in message 
news:b100b7ac-44e5-407b-8c78-d60f87211945 at p35g2000prm.googlegroups.com...
> 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.
>
> Thanks,
> Li
> --
> http://mail.python.org/mailman/listinfo/python-list

Output parameters aren't actually retrieved until you've iterated
thru all record sets.  The below works using ADO objects
directly, not sure how it would translate into adodbapi.

import win32com.client

conn_str="Driver={SQL Server};Server=.\\SqlExpress;Trusted_Connection=yes;"
sp_name="sp_DeleteMeOnlyForTesting"

c=win32com.client.gencache.EnsureDispatch('adodb.connection',0)
c.Open(conn_str)

cmd=win32com.client.Dispatch('ADODB.Command')
cmd.ActiveConnection=c
cmd.CommandType = win32com.client.constants.adCmdStoredProc
cmd.CommandText = sp_name

cmd.Parameters('@theInput').Value = 'bork'
cmd.Parameters('@theOtherInput').Value = 'borkbork'
rs, rc = cmd.Execute()
rs.NextRecordset()
print (cmd.Parameters('@theOutput').Value)

If the NextRecordset line is commented out, the output parm
is None.

         Roger