adodbapi and output parameters in stored procedures

Discussion in 'Python' started by leesquare@yahoo.com, Nov 7, 2008.

  1. Guest

    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
    , Nov 7, 2008
    #1
    1. Advertising

  2. On 2008-11-07 15:04, 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 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
    M.-A. Lemburg, Nov 7, 2008
    #2
    1. Advertising

  3. Guest

    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
    , Nov 7, 2008
    #3
  4. Roger Upole Guest

    <> wrote in message
    news:...
    > 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=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
    Roger Upole, Nov 7, 2008
    #4
  5. On 2008-11-07 17:32, 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 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
    M.-A. Lemburg, Nov 8, 2008
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. .Net Sports
    Replies:
    3
    Views:
    592
    Robbe Morris [C# MVP]
    May 12, 2005
  2. Golawala, Moiz M (GE Infrastructure)

    using adodbapi - problem returning Values from Stored Procedure

    Golawala, Moiz M (GE Infrastructure), May 17, 2005, in forum: Python
    Replies:
    0
    Views:
    400
    Golawala, Moiz M (GE Infrastructure)
    May 17, 2005
  3. Sharon
    Replies:
    2
    Views:
    603
    Alexey Smirnov
    Feb 4, 2010
  4. Joe
    Replies:
    0
    Views:
    388
  5. Chris Hall
    Replies:
    0
    Views:
    115
    Chris Hall
    Nov 15, 2006
Loading...

Share This Page