subscribe

MySQL Stored Procedure in PHP gotcha

If you're getting a 'Commands out of sync' message after calling a Stored Procedure in MySQL 5+, it means there's some empty resultset in still in the resultset buffer. I'm not sure exactly why this is happening, but here's the workaround:

<?php

  // Assuming $connection is your MySQLI object

  while($connection->next_result()) $connection->store_result();

?>

Web mentions

Comments

  • krteQ •

    The point is that you must retrieve ALL results of the resultset before sending another SQL query to the server. For example, in PEAR:MDB2 you can use method $result->nextResult() multiple times in a while cycle, until all results are retrieved.
    SP returns always at east one result - the execution result. Any other select inside SP causes another result etc.

    Hope it helps...
  • Evert

    Evert •

    That does make sense, as a first-time stored procedure (ab)user it took me a bit to figure out.
  • hartmut

    hartmut •

    A stored procedure always returns an extra empty result set that is needed to transport the exit status of the procedure itself.
  • Dave Marshall

    Dave Marshall •

    I've been nailed by this one a few times, seem to forget everytime!
  • smartov

    smartov •

    Thank you VERY much! I was close to getting INSANE :-E trying to understand whats going on and why do I get this error...
  • Folly

    Folly •

    Thank you!