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."

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 https://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" );

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

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

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" );

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 {
    // STORE
    if ($result = $mysqli->store_result()) {
      printf( "Result #%u:", ++$results );
      echo nl2br("\n"};
      // FETCH
      while( $row = $result->fetch_row() ) {
        foreach( $row as $cell ) echo $cell, " ";
      }
      // CLOSE
      $result->close();
      if( $mysqli->more_results() ) echo nl2br("\n");
    }
  // NEXT
  } while( $mysqli->next_result() );
}
$mysqli->close();

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

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

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

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();

Last updated 15 Jun 2024