Call stored procedure with OUT parameter

from the Artful MySQL Tips List

Stored procedures in MySQL 5 and 6 accept IN, OUT and INOUT parameters. The parameters work as documented in the mysql client. Do they work when called via MySQL language APIs?

MySQL Connector/NET supports OUT parameters, and the manual describes how to use them. That's so also for Connector/J. The C API acquired the capability with MySQL 5.5.3. Connector/ODBC and Connector/PHP have no syntax for fetching the value of an OUT or INOUT parameter. Mark Matthews threw some light on the issue in a 3 March 2025 bug note:

"Since there's no support in the protocol for an RPC call or binding directionality of parameters using server-side prepared statements for stored procedures, clients run into the issue that there is no standard or straightforward way to deal with OUTPUT or INOUT parameters, especially when a user wants to use a literal as the IN value of an INOUT parameter.

"We can work around it in the clients, but it means yet more parsing code (different in each connector), and the parsing code will begin to get complex as we'll have to support a subset of SQL."

Apparently only the .NET and JDBC connectors solve the problem, via client-side workarounds under the hood. If you read further down Mark's note, you see a hint that MySQL 6 might introduce proper server-side support for OUT parameters. Until then, what are we to do when a C, ODBC or PHP application needs an OUT parameter value?

One idea is to pass the name of a user variable in the OUT parameter slot and fetch its value with a subsequent query. In late 2005, Hasani Blackwell posted such a workaround in the MySQL C/C++ forum. FQ Ali posted a multiquery solution at http://forums.mysql.com/read.php?98,216895,218376#msg-218376.

That leaves PHP and ODBC. Connector/NET beats Connector/ODBC in many respects, so it's fair to say that needing OUT parameter values is just another reason to move a project from ODBC to .NET.

Then what about PHP? We start with the mysql PHP API. Given the need to call PROCEDURE myproc(IN i int, OUT j int), we enable multiple queries per function call by calling mysql_connect() with flags = 65536 (CLIENT_MULTI_STATEMENTS), and address the OUT parameter from the PHP script as ...

$result = mysql_query( "CALL myproc(1,@myvar);SELECT @myvar" );

But when we run this, we find that mysql_fetch_row() ignores the result of SELECT @myvar. Dead end.

Then can we put SELECT @myvar in a second query call?

$res1 = mysql_query( "CALL myproc(1,@myvar)" );
$res2 = mysql_query( "SELECT @myvar" );

The PHP parser accepts the second call, but the call does not execute because after the mysql API executes a stored procedure call, it refuses to execute any more queries! You have to close the current connection and open a new one--where, of course, @myvar no longer exists. Another dead end.

We have a clear answer to whether OUT parameter values can be fetched via mysql PHP API. They cannot.

What about the mysqli API? Like the C API, it is also missing a syntax for fetching OUT parameter values directly, but at least it continues to execute queries after a stored procedure call. And it has a function, multi_query(), which accepts multiple queries in a semicolon-separated string argument. So we can write ...

$mysqli = new mysqli( "HOST", "USR", "PWD", "DBNAME" );
$res = $mysqli->multi_query( "CALL PROCNAME(param,@outparam); SELECT @outparam" );

There is a syntax for retrieving results from all those queries: iterate calls to store_result(), fetch_row(), next_result() and more_results() until next_result() returns FALSE.

Beware that MySQL makes a bit of a liar out of the PHP manual page for store_result(): it says the function "transfers the result set from the last query ...". In fact next_result() starts with the first query called by multi_query() and proceeds to the last:

if( $res ) {
  $results = 0;
  do {
    if ($result = $mysqli->store_result()) {
      printf( "<b>Result #%u</b>:<br/>", ++$results );
      while( $row = $result->fetch_row() ) {
        foreach( $row as $cell ) echo $cell, " ";
      }
      $result->close();
      if( $mysqli->more_results() ) echo "<br/>";
    }
  } while( $mysqli->next_result() );
}
$mysqli->close();

As a test of this workaround, assume table names(id int, lastname char(20), firstname(char(20)), a bit of data in the table, and this stored procedure:

CREATE procedure t(IN i, OUT j)
BEGIN
  SELECT * FROM names WHERE id=i;
  SET j = 1;
END;

If we execute ...

$mysqli->multi_query( "CALL t(1,@myvar); SELECT @myvar" );

then the above code produces this result:

Result #1:
2 Smith Fran
Result #2:
1

Elegant it isn't. Direct access to OUT parameter values will be much better if it ever comes. But at least this works, and if you use stored procedures with PHP, it illustrates why you pretty much have to use the mysqli API.

In the MySQL PHP Forum, Mario Kosewski reports that this simple solution is possible with the PDO library:

$dhb = new PDO(....);
$stmt = $dbh->prepare("CALL YourStoredProc(@resultId)");
$stmt->execute();
$sql = "select @resultId as Id";
$stmt = $dbh->prepare($sql);
$stmt->execute();
$myResultId = $stmt->fetchColumn();

Return to the Artful MySQL Tips page