On 2008-11-07 15:04, [EMAIL PROTECTED] 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 @[EMAIL PROTECTED]@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 @[EMAIL PROTECTED]@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 @[EMAIL PROTECTED]@theOtherInput
you would write
SELECT * From dbo.testtable
SELECT @[EMAIL PROTECTED]
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
--
http://mail.python.org/mailman/listinfo/python-list
