Bug #67710 Results after calling a stored procedure with arguments are not kept
Submitted: 26 Nov 2012 12:54 Modified: 3 Jan 2013 13:10
Reporter: Geert Vanderkelen Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:1.0.7 OS:Any
Assigned to: Geert Vanderkelen CPU Architecture:Any

[26 Nov 2012 12:54] Geert Vanderkelen 
Description:
A stored procedure which produces multiple results set is not retaining these results when arguments are used.

How to repeat:
import mysql.connector

PROC = ("CREATE PROCEDURE multiply(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)"
        " BEGIN SET pProd := pFac1 * pFac2; SELECT 1; END")

cnx = mysql.connector.connect(user='root', host='localhost', database='test')
cur = cnx.cursor()

cur.execute("DROP PROCEDURE multiply")
cur.execute(PROC)

print cur.callproc('multiply', (5, 5, 0))

for result in cur.stored_results():
	print result.fetchall()

cnx.close()

Suggested fix:
The stored results are removed when doing an operation on the cursor (this is documented). When the stored procedure using arguments and needs to store the result, it need does a SELECT, and consequently removes the other results. We need to make sure the results are stored _after_ doing this SELECT.
[26 Nov 2012 12:59] Geert Vanderkelen 
Verified using Connector/Python 1.0.7
[3 Jan 2013 13:10] Geert Vanderkelen 
Fixed with MySQL Connector/Python v1.0.8.