The stored procedure calls "Commands out of sync" in the following query

Question

I run a query with the mysql stored procedure:

$AddProf_qr = mysql_query("call AddStudent('$d_Pass', '$d_Titl', '$d_Firs', '$d_Midd', '$d_Last', '$d_Addr', '$d_City', '$d_Stat', '$d_County', '$d_Zipc', $d_Gend, '$d_Birh', '$d_Phom', '$d_Phoh', '$d_Phoo', '$d_Email', '$d_Webs', '$d_Natn', '$d_Profsn', '$d_Compny', '$d_Desig', $d_ProfAcc)", $this->c_remote) or die ("first call" . mysql_error($this->c_remote)); 

I should get only one result of the call: @@ IDENTITY = number;

 $AP_result = mysql_fetch_array($AddProf_qr); $CurrentSID = $AP_result['@@IDENTITY']; 

which works great. but when I run another mysql update request right after that, it gives an error message:

Error: 2014 (CR_COMMANDS_OUT_OF_SYNC) Message: commands are not synchronized; you cannot run this command now

I tried to insert:

 mysql_free_result($AddProf_qr); 

but still the same.

The MySQL call runs fine as well, the rest of the script works without the problems described above. but they do not work at the same time. My best guess is, the challenge is doing something that messed it up.

Answer 1

Your stored procedure returns multiple result sets. See this post

Decision?

• Use mysqli_multi_query
• Stop using the ancient mysql library - I in mysqli means "Improved" - with good reason.

Answer 2

@DMin Yes, it will work, but sooner or later you will break the server. Just do the math, one request per page, which does 3 * the number of procedures in the database! Just think about it!

[UPDATE] solution:

 $aCategory = array(); $it=0; $res = $mysqli->multi_query( "call ListCategory();" ); if( $res ) { do { if ($result = $mysqli->store_result()) { while( $row = $result->fetch_row() ) { $aCategory[$it] =$row; $it= $it + 1; } $result->close(); } } while( $mysqli->next_result() ); } foreach($aCategory as $row){ echo . $row[0] . " - " . $row[1] . "<br />"; } 

Just wanted to add that you are ready to call the following procedure.

PS: So I could not use

 echo $aCategory['category_id'] ; //or echo $aCategory->category_id; //just echo $aCategory[0] 

Answer 3

Check out here: http://us3.php.net/manual/en/function.mysql-query.php In the comments, one guy claims that he did this by setting the connection flag in MYSQL_MULTI_RESULTS (131072),

But it would be much better to use mysqli ...

Answer 4

mysql_free_result (client-> Res);

 while (mysql_more_results(client->conn)) { mysql_next_result(client->conn); } 

It made a charm for me :)

Answer 5

The result sets returned from the stored procedure cannot be loaded correctly using mysqli_query (). The mysqli_query () function combines the execution of statements and fetching the first result set into a buffer result set, if any. However, there are additional control sets of stored procedures that are hidden from the user, because of which mysqli_query () refuses to return the expected results of the user.

The result sets returned from the stored procedure are retrieved using mysqli_real_query () or mysqli_multi_query (). Both functions allow you to get any number of result sets returned by the operator, for example CALL.

see the official guide