Stored procedure call failing without CLIENT_MULTI_RESULTS flag set

Bug #723497 reported by William Kral on 2011-02-23
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
oursql
Undecided
Unassigned

Bug Description

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers