Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > adodbapi and output parameters in stored procedures

Reply
Thread Tools

adodbapi and output parameters in stored procedures

 
 
leesquare@yahoo.com
Guest
Posts: n/a
 
      11-07-2008
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+@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+@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
 
Reply With Quote
 
 
 
 
M.-A. Lemburg
Guest
Posts: n/a
 
      11-07-2008
On 2008-11-07 15:04, http://www.velocityreviews.com/forums/(E-Mail Removed) 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+@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+@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+@theOtherInput

you would write

SELECT * From dbo.testtable
SELECT @theInput+@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 200
>>> 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
 
Reply With Quote
 
 
 
 
leesquare@yahoo.com
Guest
Posts: n/a
 
      11-07-2008
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
 
Reply With Quote
 
Roger Upole
Guest
Posts: n/a
 
      11-07-2008

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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+@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+@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=ye s;"
sp_name="sp_DeleteMeOnlyForTesting"

c=win32com.client.gencache.EnsureDispatch('adodb.c onnection',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




 
Reply With Quote
 
M.-A. Lemburg
Guest
Posts: n/a
 
      11-08-2008
On 2008-11-07 17:32, (E-Mail Removed) wrote:
> 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?


Try this (I haven't checked that it works, but it's a possibility),
assuming your query uses the order SELECT, INSERT, SELECT:

# Fetch results from first SELECT
test_table_result_set = cursor.fetchall()
cursor.nextset()

# Ignore "empty" result set
cursor.nextset()

# Fetch results from second SELECT
(output_variables,) = cursor.fetchone()

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Nov 08 200
>>> 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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
adodbapi integer parameters and MS Access Joe Python 0 03-13-2011 10:04 PM
How to send consecutive stored procedures and its parameters Sharon ASP .Net 2 02-04-2010 07:50 AM
ruby odbc and binding OUT parameters in stored procedures Chris Hall Ruby 0 11-15-2006 04:41 PM
using adodbapi - problem returning Values from Stored Procedure Golawala, Moiz M (GE Infrastructure) Python 0 05-17-2005 06:39 PM
ASp.net stored procedures parameters for search functions .Net Sports ASP .Net 3 05-11-2005 11:43 PM



Advertisments