MySQL Bugs: #16982: Stored procedure OUT parameters can't be used until after other results fetched

Bug #16982	Stored procedure OUT parameters can't be used until after other results fetched
Submitted:	31 Jan 2006 16:24	Modified:	10 Feb 2006 8:36
Reporter:	Andy Dustman	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.18	OS:	independent
Assigned to:		CPU Architecture:	Any

Description:
Consider a stored procedure similar to the one here (simpleproc): http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

It's not possible to get the value of param1 until after any result sets generated by the procedure are fetched.

In contrast, it appears to be possible to get the value of output parameters before fetching any result sets using ODBC:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_odbcht10_7t2s...

How to repeat:
In the command-line client:

mysql> delimiter //
mysql> CREATE PROCEDURE lesssimpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    ->   SELECT * FROM t;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL lesssimpleproc(@foo);
/* prints result set from SELECT * FROM t */

Query OK, 0 rows affected (0.01 sec)

mysql> select @foo;
+------+
| @foo |
+------+
| 7    |
+------+
1 row in set (0.00 sec)

However, I think it is possible for a stored procedure calling another stored procedure to make use of output values before result sets are processed.

Suggested fix:
It seems like the only way to fix this is to extend the C API to add a function like:

MYSQL_RES *mysql_callproc(MYSQL *mysql, const char *query, unsigned long length)

returning a result set (as from mysql_store_result()) of one row containing the parameters, with column names set to the parameter names as defined, i.e. the above example would return a one-row result set of a column named param1.

There could also be an extention to the prepared statements API to do this, where parameters are bound, but I am less familiar with that interface, so I'm not going to try an example. According to the documentation, CALL is not currently supported through the prepared statements API.

My perspective on this is that I am the creator of the Python API for MySQL. The standard (PEP-249) calls for stored procedure access like this:

        .callproc(procname[,parameters])
          
            (This method is optional since not all databases provide
            stored procedures. [3])
            
            Call a stored database procedure with the given name. The
            sequence of parameters must contain one entry for each
            argument that the procedure expects. The result of the
            call is returned as modified copy of the input
            sequence. Input parameters are left untouched, output and
            input/output parameters replaced with possibly new values.
            
            The procedure may also provide a result set as
            output. This must then be made available through the
            standard fetchXXX() methods.

The above section was probably strongly influenced by ODBC; the primary author of the spec is also the author of an ODBC module for Python.

Currently it's still possible to call stored procedures using the standard .execute() method used for other SQL statements, but the output parameters cannot be accessed until any result sets generated by the stored procedure have been fetched. This is not exclusively a Python issue: Since there's not a way to do this in the C API, this apparently affects (I am told) PHP and Perl as well.

I'm not sure how practical it is to try to fix this, or if it would be better to simply advise against using output parameters in stored procedures that return result sets, or at least note that the output values are not available until after the result sets have been read, so that people developing stored procedures can plan appropriately.

Thank you for a problem report. I think, it is a reasonable C API/protocol feature request. So, I mark it as verified.

This is, to put it bluntly, impossible.

This is how it works:

1) The client sends CALL simpleproc(@foo) to the server, and waits for a response.
2) The server executes simpleproc:
  2.1) SELECT INTO param1 is executed, only the local parameter is yet set, not @foo
  2.2) SELECT * FROM t is executed. The result set is now being sent back to the client.
  2.3) Since execution was successful, OUT parameters are set in the caller's context - in this case the user variable @foo.
  2.4) The response is finalized (send_ok).
3) The client processes the result set.

It's after 3 it's possible to send new queries to the server.
It's not possible to make the output value available until the entire procedure has finished successfully; the parameter might be set several times during execution, and if an error aborts execution it should not be set at all.

Even if it was possible to send a request to get @foo before processing the result set (which would require a redesign of the client protocol), it wouldn't help, since @foo has has not yet been set; it can't be set until the procedure returns.

Even if the server were to send out parameters as a result set, it can only be done when the procedure execution has succeeded, i.e. after any other result sets have been sent,
so it doesn't help to add any special C API function at the client end for this.

It's actually not possible for another procedure, calling simpleproc, to make use of out parameters before the result set is "processed" (in the server it's "sent"), the calling procedure wont "see" the value until it's resuming execution after the CALL.

So, looks like this will not be implemented.