Call Multiple Stored Procedures with the Zend Framework

Question

I'm using Zend Framework 1.7.2, MySQL and the MySQLi PDO adapter. I would like to call multiple stored procedures during a given action. I've found that on Windows there is a problem calling multiple stored procedures. If you try it you get the following error message:

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

I found that to work around this issue I could just close the connection to the database after each call to a stored procedure:

if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
  //If on windows close the connection
  $db->closeConnection();
}

This has worked well for me, however, now I want to call multiple stored procedures wrapped in a transaction. Of course, closing the connection isn't an option in this situation, since it causes a rollback of the open transaction. Any ideas, how to fix this problem and/or work around the issue.

• More info about the work around
• Bug report about the problem

Answer 1

I has same errors when called queries like this(variables to use in next query)

$db->query("SET @curr = 0.0;");

To fix this I've changed my config file to

'database' => array(
        'adapter' => 'mysqli',

Answer 2

hi waney, I tried your solution. But it doesnt work. Any other way to do it??

Thanxs

Answer 3

Hey,

This pattern of preparing, executing and then closing each $sql statement that calls a stored procedure does work.

public function processTeams($leagueid,$raceid,$gender)
{
    $db = Zend_Db_Table::getDefaultAdapter();
    $sql = $db->prepare(sprintf("CALL addScoringTeams(%d,%d,'%s')",$leagueid,$raceid,$gender));
    $sql->execute();
    $sql->closeCursor();
    $this->logger->info(sprintf("CALL addScoringTeams(%d,%d,'%s')",$leagueid,$raceid,$gender));

    $sql1 = $db->prepare(sprintf("CALL updateScoringTeamTotals(%d)",$raceid));
    $sql1->execute();
    $sql1->closeCursor();
    $this->logger->info(sprintf("CALL updateScoringTeamTotals(%d)",$raceid));

    $sql2 = $db->prepare(sprintf("CALL updateScoringTeamClasses(%d,'%s')",$raceid,$gender));
    $sql2->execute();
    $sql2->closeCursor();
    $this->logger->info(sprintf("CALL updateScoringTeamClasses(%d,'%s')",$raceid,$gender));
}