adodbapi and output parameters in stored procedures

[leesquare@yahoo.com]

Hello,

I need some help getting output values from my stored procedures when
using adodbapi. There's an example
testVariableRet urningStoredPro cedure 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_DeleteMeOnly ForTesting
@theInput varchar(50),
@theOtherInput varchar(50),
@theOutput varchar(100) OUTPUT
AS
SET @theOutput=@the Input+@theOther Input

Then, I can run in python:

>>cursor = db.conn.cursor( )
>>cursor.callpr oc('sp_DeleteMe OnlyForTesting' , ('hello', 'bye', ''))

[u'hello', u'bye', u'hellobye']


If I redefined the procedure as
CREATE PROCEDURE sp_DeleteMeOnly ForTesting
@theInput varchar(50),
@theOtherInput varchar(50),
@theOutput varchar(100) OUTPUT
AS
SELECT * From dbo.testtable
SET @theOutput=@the Input+@theOther Input

Then, the python comes out as :

>>cursor = db.conn.cursor( )
>>cursor.callpr oc('sp_DeleteMe OnlyForTesting' , ('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

[M.-A. Lemburg]

Re: adodbapi and output parameters in stored procedures

On 2008-11-07 15:04, leesquare@yahoo .com wrote:

Hello,
>
I need some help getting output values from my stored procedures when
using adodbapi. There's an example
testVariableRet urningStoredPro cedure 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_DeleteMeOnly ForTesting
@theInput varchar(50),
@theOtherInput varchar(50),
@theOutput varchar(100) OUTPUT
AS
SET @theOutput=@the Input+@theOther Input
>
Then, I can run in python:

>>>cursor = db.conn.cursor( )
>>>cursor.callp roc('sp_DeleteM eOnlyForTesting ', ('hello', 'bye', ''))

[u'hello', u'bye', u'hellobye']
>
>
If I redefined the procedure as
CREATE PROCEDURE sp_DeleteMeOnly ForTesting
@theInput varchar(50),
@theOtherInput varchar(50),
@theOutput varchar(100) OUTPUT
AS
SELECT * From dbo.testtable
SET @theOutput=@the Input+@theOther Input
>
Then, the python comes out as :

>>>cursor = db.conn.cursor( )
>>>cursor.callp roc('sp_DeleteM eOnlyForTesting ', ('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=@the Input+@theOther Input

you would write

SELECT * From dbo.testtable
SELECT @theInput+@theO therInput

and then fetch the data using:

cursor.callproc (...)
test_table_resu lt_set = cursor.fetchall ()
cursor.nextset( )
(output_variabl es,) = 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.D atabase.Adapter ... http://zope.egenix.com/
>>mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

_______________ _______________ _______________ _______________ ____________

:::: Try mxODBC.Zope.DA for Windows,Linux,S olaris,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

[leesquare@yahoo.com]

Re: adodbapi and output parameters in stored procedures

Thanks for that excellent pointer!

I was able to do just what you said with

But if my procedure has an insert statement in its midst, it doesn't
work. The cursor.fetchall () gets an exception.
Any ideas?

--Li

[Roger Upole]

Re: adodbapi and output parameters in stored procedures


<leesquare@yaho o.comwrote in message
news:b100b7ac-44e5-407b-8c78-d60f87211945@p3 5g2000prm.googl egroups.com...

Hello,
>
I need some help getting output values from my stored procedures when
using adodbapi. There's an example
testVariableRet urningStoredPro cedure 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_DeleteMeOnly ForTesting
@theInput varchar(50),
@theOtherInput varchar(50),
@theOutput varchar(100) OUTPUT
AS
SET @theOutput=@the Input+@theOther Input
>
Then, I can run in python:

>>>cursor = db.conn.cursor( )
>>>cursor.callp roc('sp_DeleteM eOnlyForTesting ', ('hello', 'bye', ''))

[u'hello', u'bye', u'hellobye']
>
>
If I redefined the procedure as
CREATE PROCEDURE sp_DeleteMeOnly ForTesting
@theInput varchar(50),
@theOtherInput varchar(50),
@theOutput varchar(100) OUTPUT
AS
SELECT * From dbo.testtable
SET @theOutput=@the Input+@theOther Input
>
Then, the python comes out as :

>>>cursor = db.conn.cursor( )
>>>cursor.callp roc('sp_DeleteM eOnlyForTesting ', ('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="Drive r={SQL Server};Server= .\\SqlExpress;T rusted_Connecti on=yes;"
sp_name="sp_Del eteMeOnlyForTes ting"

c=win32com.clie nt.gencache.Ens ureDispatch('ad odb.connection' ,0)
c.Open(conn_str )

cmd=win32com.cl ient.Dispatch(' ADODB.Command')
cmd.ActiveConne ction=c
cmd.CommandType = win32com.client .constants.adCm dStoredProc
cmd.CommandText = sp_name

cmd.Parameters( '@theInput').Va lue = 'bork'
cmd.Parameters( '@theOtherInput ').Value = 'borkbork'
rs, rc = cmd.Execute()
rs.NextRecordse t()
print (cmd.Parameters ('@theOutput'). Value)

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

Roger