Gwyn Howell Gwyn Howell - 1 year ago 107
MySQL Question

MySQLdb Stored Procedure Out Parameter not working

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;

If I call the SP from the command line like this:

CALL multiply(5, 5, @Result)
SELECT @Result

I correctly get the result:

| @Result |
| 25 |

But if I call it with python code using the MySQLdb package, like this:

args = (5, 5, 0) # 0 is to hold value of the OUT parameter pProd
result = cursor.callproc('multiply', args)
print result

then I do not get the out parameter in my result tuple:

(5, 5, 0)

So, what am I doing wrong here?

Just found this warning in the callproc code:

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.

And also note that the callproc function merely returns the same input arg tuple. So bottom line is this is not possible. Back to the drawing board then ...

Air Air
Answer Source

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')
>>> curs.fetchall()
((5L, 5L, 25L),)