I have a database hosted on Google Cloud SQL, and a python script to query it.
I am trying to call a Stored Procedure that has an Out Parameter. The SP is called successfully, but the value of the Out Parameter doesn't seem to be returned to my python code.
For example, here is the example taken from here:
Definition of the multiply stored procedure:
CREATE PROCEDURE multiply(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
SET pProd := pFac1 * pFac2;
CALL multiply(5, 5, @Result)
| @Result |
| 25 |
args = (5, 5, 0) # 0 is to hold value of the OUT parameter pProd
result = cursor.callproc('multiply', args)
(5, 5, 0)
Compatibility warning: PEP-249 specifies that any modified
parameters must be returned. This is currently impossible
as they are only available by storing them in a server
variable and then retrieved by a query. Since stored
procedures return zero or more result sets, there is no
reliable way to get at OUT or INOUT parameters via callproc.
The server variables are named @_procname_n, where procname
is the parameter above and n is the position of the parameter
(from zero). Once all result sets generated by the procedure
have been fetched, you can issue a SELECT @_procname_0, ...
query using .execute() to get any OUT or INOUT values.
All you need is an additional
SELECT to access the output values:
>>> curs.callproc('multiply', (5, 5, 0)) (5, 5, 0) >>> curs.execute('SELECT @_multiply_0, @_multiply_1, @_multiply_2') 1L >>> curs.fetchall() ((5L, 5L, 25L),)