out parameters with callproc

[Jeff Norton]

I can't get OUT or INOUT parameters to work with stored procedures using callproc. IN parameters and result sets work fine (well once you set cursor._executed). Has anybody tried OUT parameters?

Small example:
cursor = cx.cursor()
p1 = "K9-RT-01"
p2 = .1
values = [p1, p2]
print "with values:", values
result = cursor.callproc("CHEAPPET", values)
rows = cursor.fetchall()
cursor.close()
print "Query returned %d rows" % len(rows)
print "values =", values
print "result =", result

Produces:

Running: CHEAPPET
with values: ['K9-RT-01', 0.10000000000000001]
Query returned 27 rows
values = ['K9-RT-01', 0.10000000000000001]
result = ['K9-RT-01', 0.10000000000000001]

(the correct answer for result would be: ['K9-RT-01', 225.0])

[Jeff Norton]

Gotcha. Does make MySQL support look kind of wimpy though. Has there been any move to improve the C API?

[Andy Dustman]

I guess you didn't read the built-in documentation (use help() or pydoc):

Help on method callproc in MySQLdb.cursors.Cursor:

MySQLdb.cursors.Cursor.callproc = callproc(self, procname, args=()) unbound MySQ
Ldb.cursors.Cursor method
Execute stored procedure procname with args

procname -- string, name of procedure to execute on server

args -- Sequence of parameters to use with procedure

Returns the original args.

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.

Compatibility warning: The act of calling a stored procedure
itself creates an empty result set. This appears after any
result sets generated by the procedure. This is non-standard
behavior with respect to the DB-API. Be sure to use nextset()
to advance through all result sets; otherwise you may get
disconnected.

[Jeff Norton]

Both cx_Oracle and DB2 function according to PEP-249 (i.e. the return from callproc is a sequence of parameters -- the IN parameters are just copied, the OUT and INOUT parameters are the result values. If you want to implement it under the covers as you describe that is your perogative, but the user should program to the PEP specification so it works the same for all databases -- that's why we have a PEP.

But thanks for explaining what was going on (didn't realize there was built-in doc, thanks for that pointer).

[Andy Dustman]

If I could make it follow PEP-249, I would, but it's not technically possible. I didn't do it this way on a whim; it's the best I can do with MySQL's C API. Which is to say, there is not a specific API call for calling stored procedures. The OUT and INOUT variables aren't available until all the result sets are returned, and there's no way to know how many will be returned.