[DB-SIG] Using callproc with OUTPUT parameters

Henrik Ekelund jagarenbraperson at yahoo.se
Tue Feb 10 05:02:51 EST 2004


Yes to both questions: you are using callproc
correctly, and adodbapi works with output paramters
and SQL Server.

The problem is with the driver. Your connectionstring
uses the ODBC driver for SQL Server. If you use the
OLE DB driver instead it works correctly.
Change your connect statement to this:

db=connect('Provider=SQLOLEDB.1;Data Source=%s;
Initial Catalog=%s;User ID=%s;Password=%s;'
%('SERVER','ipuat6','x','x'))

and I'm pretty sure it will work. 
There is a testcase for output parameters called
testVariableReturningStoredProcedure in the file
adodbapitest.py

More about the connect string syntax:
http://www.connectionstrings.com

/Henrik Ekelund



 --- James Briggs <James.Briggs at unisuper.com.au>
skrev: > 
> 
> 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."
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig 

Höstrusk och grå moln - köp en resa till solen på Yahoo! Resor på adressen http://se.docs.yahoo.com/travel/index.html



More information about the DB-SIG mailing list