Stored procedure causes "Commands out of sync" on the next query.

Question

I am running a query with a 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 am supposed to get just one result from the call : @@IDENTITY = a number;

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

which works fine. but when i run another mysql update query right after this, it gives an error saying :

Error: 2014 (CR_COMMANDS_OUT_OF_SYNC) Message: Commands out of sync; you can't run this command now

i have tried inserting :

mysql_free_result($AddProf_qr);

but still the same.

The MySQL call executes fine also the rest of the script runs without issues the above is commented out. but they don't run at the same time. My best guess is, the call is doing something that's messing this up.

Answer 1

Your stored procedure is returning multiple resultsets. See this post

Solution?

• Use mysqli_multi_query
• Stop using the ancient mysql library - the i in mysqli stands for "Improved" - with good reason.

Answer 2

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

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