Stored procedure call failing without CLIENT_MULTI_RESULTS flag set

[William Kral]

When calling a stored procedure I get the following error:

PROCEDURE database.procedure_name can't return a result set in the given context

The procedure is called through SQLAlchemy but ultimately ends up in a cursor.execute call.

... /sqlalchemy/dialects/mysql/oursql.py", line 92, in do_execute
    cursor.execute(statement, parameters)
  File "cursor.pyx", line 122, in oursql.Cursor.execute (oursqlx/oursql.c:15820)
  File "statement.pyx", line 400, in oursql._Statement.execute (oursqlx/oursql.c:10255)
  File "statement.pyx", line 126, in oursql._Statement._raise_error (oursqlx/oursql.c:7425)
ProgrammingError: (ProgrammingError) (1312, "PROCEDURE database.procedure_name can't return a result set in the given context", None) u'CALL procedure_name();' ()

http://bugs.mysql.com/bug.php?id=24485 explains that the error is because the CLIENT_MULTI_RESULTS flag needs to be set when connecting with mysql_real_connect()

Which is further explained in the mysql documentation http://dev.mysql.com/doc/refman/5.1/en/mysql-real-connect.html

"If your program uses CALL statements to execute stored procedures, the CLIENT_MULTI_RESULTS flag must be enabled. This is because each CALL returns a result to indicate the call status, in addition to any result sets that might be returned by statements executed within the procedure. Because CALL can return multiple results, you should process them using a loop that calls mysql_next_result() to determine whether there are more results."

It looks like you're aware of this as I found the following in the code in connection.pyx:

# I'll come back to this later. Dealing with multiple result sets is
# such a pain.
#if multi_results:
# flags |= CLIENT_MULTI_RESULTS
#if multi_statements:
# flags |= CLIENT_MULTI_STATEMENTS

Seems to be the only way to call a stored proc from what I can tell from the documentation.

OSX 10.6
MySQL 5.1.48
Python 2.6.1
SQLAlchemy 0.6.5