agesys-dev team mailing list archive

[William Kral <723497@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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

** Affects: oursql
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Agesys
Team, which is subscribed to oursql.
https://bugs.launchpad.net/bugs/723497

Title:
  Stored procedure call failing without CLIENT_MULTI_RESULTS flag set

Status in oursql:
  New

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