Thursday, November 09, 2006

"mySQL server has gone away" in PHP when using mySQL stored procedures

There seems to be a lot of conflicting information floating around about stored procedures in PHP, and I spent a bit of time myself grappling with a "mysql server has gone away" error when using the CALL statement to run a stored procedure. I think I've managed to get things squared away, both in the code and in my head, as to the caveats of mySQL 5 stored procedures in PHP. Here's the lowdown:

Per mySQL's own documentation for the C API of mysql_next_result:
"each CALL returns a result to indicate the call status, in addition to any results sets that might be returned by statements executed within the procedure."

This means that you should use mysqli_multi_query() when calling your stored procedure.

Additionally, this means that you cannot simply call mysqli_store_result() to store your buffered result in a variable then move on to your next query, which is perfectly reasonable for normal queries. Even for procedures that return a single resultset, you need to make sure you iterate through the "call status" result even though you won't be using it for anything. In fact, this status result seems to be kind of nebulous in that you can't use_result() or store_result() on it (as far as I can tell), but you need to iterate through it (via next_result()) before executing another query.

My problem with the way this all works is that since all of my procedures only returned a single result set, I wanted to be able to access them exactly as I would any other query (including storing the result so I could use it later), without having to manually iterate through this extra status result each time I called a procedure. Below is what I came up with.

Note: I'm using the procedural functions for the mysqli interface because people who are just getting acquainted with mysqli will recognize the syntax more easily, but you should definitely look into the object oriented approach for using mysqli functions.


function &procedure_get_result( $procedure_call ) {

//
//note: error handling has been removed from this function
//for the sake of the example, but you'll want to check the
//return values of functions like mysqli_connect(), etc...
//

$result_count = 0;
$result_loop_silence = 0;
$false_ret = false;

$dbh = mysqli_connect("localhost", "my_user", "my_password", "my_dbname");

//
// if the word CALL was included in the parameter, just strip it out.
// We'll add it later.
//
if ( substr(strtoupper($procedure_call), 0, 4) == 'CALL' ) {
$procedure_call = substr( $procedure_call, 4 );
}

$procedure_call = trim($procedure_call);

$query = "CALL {$procedure_call}";

if ( mysqli_multi_query($dbh, $query) ) {

$result = mysqli_store_result($dbh); //this is the result we want!

while ( mysqli_more_results($dbh) ) {

//
// iterate through the call status result set
//
if ( $result_count > 0 ) {
//
// this function only supports a 0th and a 1st result set
// so show a warning if we have more
//
if ( !$result_loop_silence ) {
trigger_error( 'Too many results returned for ' . __METHOD__ . '. Use multi query.', E_USER_WARNING );
$result_loop_silence = 1;
}
}

mysqli_next_result($dbh);

if ( $status_result = mysqli_use_result($dbh) ) {

//
// this never seems to get executed with
// CALL that only return one result set plus the
// status resultset, but we'll leave it in
//

$status_result->close();
mysqli_free_result($status_result);

}

$result_count++;
}

return $result;

}

return null;
}

3 comments:

BUY WOW GOLD said...

Nice blog. I a also ardent player of WOW GOLD. I love this game. Nice posting about wow gold. Thanks

Bruce said...

Today,we are proud to announce the launch of the new wedding support service sell ffxi gil,packed with features sure to sell ffxi gils delight adventurers across Vana'diel looking to exchange eternal vows with their beloved!Responding to player demands for greater customization,the new service will grant brides and grooms freedom in choosing location,timing,dialogue,and sell Final Fantasy XI Gil more for their ceremony,allowing them to create a truly memorable event all their own.Information on all the features,including in-game sell ffxi gil item vendors and wedding certificates,can be found on the new wedding support site,so head on over sell ffxi gils and get started planning the wedding of your dreams sell Final Fantasy XIGil!

qishaya said...

Milan create week ended last night with donatella christian louboutin london disregard presenting the ending show of the autumn christian louboutin online frost 2003 italian collections.Donatella, the creative chief of christian boots the house her delayed brother founded, delivered a collection that was, christian louboutin uk as they say, very christian louboutin shoes.First up was the versus limit, louboutin boots the cheaper christian louboutin line. Girls stomped christian louboutin 2010 out with backcombed tresses bearing turquoise leather trousers, christian louboutin uk sale patchwork pullover and blonde fur sliced into stoles and active jackets. christian boots uk But this aggressive hell’s angels look almost seemed a caricature of the christian louboutin boots christian louboutin boots comfort. louboutin sandals Next up was gianni christian louboutin, buy christian louboutin the main collection