[DB-SIG] Using callproc with OUTPUT parameters

[James Briggs]

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:

DROP PROCEDURE test_inc
GO

CREATE PROCEDURE test_inc (@a int, @b int OUTPUT)
AS
BEGIN
  SET @b=@a+1
END
GO

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))
1
>>> 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
    self._raiseCursorError(Error,None)
  File "D:\PYTHON23\Lib\site-packages\adodbapi\adodbapi.py", line 407, in _raiseCursorError
    eh(self.conn,self,errorclass,errorvalue)
  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
 subsidiaries."