[DB-SIG] Using callproc with OUTPUT parameters

James Briggs James.Briggs at unisuper.com.au
Tue Feb 10 00:27:31 EST 2004

I am connecting to a MS SQL Server database, and I wish to execute a stored procedure that can perform various transactions in the database.
The tricky bit is I wish the stored procedure to return a value to a python variable.

As a very simple example,  the SQL Server code could be:


CREATE PROCEDURE test_inc (@a int, @b int OUTPUT)
  SET @b=@a+1

I have happily used mx.ODBC in the past, but this API doesn't provide the cursor.callproc method.
I can call the procedure with code like this, but how can I access the output value?

>>> from mx.ODBC.Windows import connect
>>> db=connect('IPUAT6',user='x',password='x')
>>> a=1
>>> b=0
>>> c=db.cursor()
>>> c.execute('exec dbo.test_inc ?, ? output', (a,b))
>>> c.fetchall()
Traceback (most recent call last):
  File "<interactive input>", line 1, in ?
ProgrammingError: missing result set

Alternatively using callproc in the adodbapi module the procedure executes, but I do not see any variables updated:

>>> from adodbapi import connect
>>> db=connect('Driver={SQL Server};server=%s;Database=%s;UID=%s;PWD=%s;'%('SERVER','ipuat6','x','x'))
>>> a=1
>>> b=0
>>> c=db.cursor()
>>> c.callproc('test_inc',[a,b])
[1, 0]
>>> (a,b)
(1, 0)
>>> c.fetchone()
Traceback (most recent call last):
  File "<interactive input>", line 1, in ?
  File "D:\PYTHON23\Lib\site-packages\adodbapi\adodbapi.py", line 654, in fetchone
    ret = self._fetch(1)
  File "D:\PYTHON23\Lib\site-packages\adodbapi\adodbapi.py", line 623, in _fetch
  File "D:\PYTHON23\Lib\site-packages\adodbapi\adodbapi.py", line 407, in _raiseCursorError
  File "D:\PYTHON23\Lib\site-packages\adodbapi\adodbapi.py", line 38, in standardErrorHandler
    raise errorclass(errorvalue)
Error: None

My questions in a nutshell are:
Am I using callproc correctly here?
Is there API that connects to SQL Server where callproc works with output parameters?

James Briggs

"This e-mail message is intended only for the addressee(s)
 and contains information which may be confidential. If
 you are not the intended recipient please advise the
 sender by return email, do not use or disclose the
 contents, and delete the message and any attachments
 from your system. Unless specifically indicated, this
 email does not constitute formal advice or commitment
 by the sender or UniSuper (ABN 54 006 027 121) or its

More information about the DB-SIG mailing list