I having an interesting issue. I can't get PHP to execute two stored procedures from MySQL in a row. Here is a sample procedure (we simplified it a bit for this example)

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_proc`$$
CREATE PROCEDURE  `test_proc`(i int(3))
BEGIN
  select 1+i;
END $$

DELIMITER ;

Works from inside of MySQL all day.

Now, I tried everything Pear DB, Pear MDB2 mysql, pear Mdb2 mysqli and have been unable to get it to run twice in a row. I am using pear Mdb2 mysqli

$res3 = $mdb2->executeStoredProc("test_proc",array('3'));
if (PEAR::isError($res3)) {
print_r($res3->getMessage());

which works and returns - 4 ... however if the page has

$res2 = $mdb2->query("select count(*) from test");  //this works
if (PEAR::isError($res2)) {
    print_r($res2->getMessage());

$res3 = $mdb2->executeStoredProc("test_proc",array('3')); //this works
if (PEAR::isError($res3)) {
    print_r($res3->getMessage());


}  print_r($res3->fetchRow());

   $res4 = $mdb2->executeStoredProc("test_proc",array('3'));  //this doesn't
if (PEAR::isError($res4)) {
    print_r($res4->getMessage());

the second procedure fails SOMETIMES, I tried changing the second call object to be other names, no luck ... Basically my issue is
It work the first time, but not the second time or if you reload the page quickly which makes it fail too.

Error:
Array ( [0] => 931302 ) Array ( [0] => 4 ) MDB2 Error: Array
Fatal error: Call to undefined method MDB2_Error::fetchRow() in C:\xampp\xampp\htdocs\dev\test.php on line 34 <-- which is the line of the 2nd proc call .. but it works the first time so its defined...

How can we call multiple stored procedures in a row from PHP?
Thank you for any help!

    Probably you're running into the "not consuming the results from the previous query" issue.

    This happens with naive client applications/drivers which don't correctly consume the previous result set before issuing another query.

    I don't know about this $mdb2 nonsense, but in any case, your error handling is inadequate - please fix it (I recommend you have PEAR throw an exception when any error happens at all - read the PEAR docs). You'll see the error then.

    The true error will probably be something about connection state or protocol error.

    Some versions of PHP have a bug in some of the client layer(s) with some versions of the MySQL client library, but it shouldn't affect you if your SPs only return a single result set, and you read it fully before trying to invoke another one.

    Mark

      If you fully read the result set, including any additional ones which are returned from the procedure call, then tidy up all outstanding resources, I think the subsequent query should succeed.

      Or at least, that is my impression from this bug report.

      (Hint: use "unset" to delete a local variable)

      You may want to consider using PDO instead of mysqli, maybe it handles this more gracefully (But still not ideally) ?

      Mark

        Write a Reply...