Issues

ZF-2101: Can't execute multiple stored procedures with Pdo_Mysql

Description

Patrick Calkins writes:

{quote} Code:


$pdoParams = array(
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
);

$params = array(
    'host'           => '127.0.0.1',
    'username'       => 'webuser',
    'password'       => 'xxxxxx',
    'dbname'         => 'test',
    'driver_options' => $pdoParams
);

$db = Zend_Db::factory('Pdo_Mysql', $params);

// Stored procedure returns a single row
$stmt = $db->prepare('CALL get_customer_by_id(:customerId)');
$stmt->bindParam('customerId', $customerId, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll();
print_r($result);

$stmt->closeCursor();

// Stored procedure returns a single row
$stmt = $db->prepare('CALL get_address_by_id(:customerId)');
$stmt->bindParam('customerId', $customerId, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll();
print_r($result);

This will consistently throw this error: '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.'

If you insert $stmt->nextRowset(); before $stmt->closeCursor(); it will throw this error: 'SQLSTATE[HYC00]: Optional feature not implemented'

This appears to be a bug, and I haven't found any more info as to its status lately. Is this correct?? {quote}

Comments

Good unit tests that execute multiple stored procedures would hopefully expose the problem.

This issue should have been fixed for the 1.5 release.

Please categorize/fix as needed.

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Resolved in r9539

I think perhaps this issue needs to be merged into the 1.5 branch. Can someone from the community check to see that this works as they expect in their application (the fix is in trunk)?

-Ralph

Updating for the 1.6.0 release.

I've just tested this in version 1.7.2 and I'm still getting the error.

It seems to be, at least partially, related to MySql version. Specifically I have the issue with 5.1.3 while a few co-workers and our integration server have no issues running the same code with MySql 5.0.45.

This issue is present for those who are not using Zend Server CE. If you install Zend Server CE (which replace php5 PDO lib right?), the issue disapear, don't seems to be linked to ZF itself.

Could someone double check this? Because I'm going insane.

Thanks.

It seems like the same version of ZF on different versions of PHP showing different behaviors is PHP (PDO) specific, how can we solve this in ZF?

Do you have any PDO code (non-zf) that can demonstrate this problem running on all PHP platforms?

-ralph

I use to add this to the Db/Adapter/Pdo/Abstract.php _connect() method, inside the try/catch block: $this->_connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

That solves this issue for me, I had to put that into place for the last one or two years, into every new update...

$this->dbh = new PDO($this->options['dsn'], $this->options['username'], $this->options['password'], array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES '.$this->options['charset']));
$this->dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$this->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
    $sth = $this->dbh->prepare($_stored_proc_1);
            $sth->bindValue('I_bind', $value, PDO::PARAM_STR);
        $sth->execute();
        $object = $sth->fetchAll(PDO::FETCH_ASSOC);
        $sth->closeCursor();
    $sth = $this->dbh->prepare($_stored_proc_2);
            $sth->bindValue('I_bind', $value, PDO::PARAM_STR);
        $sth->execute();
        $object = $sth->fetchAll(PDO::FETCH_ASSOC);
        $sth->closeCursor();

The code above causes the issue

with any PHP version windows/linux/mac os x (WAMP 2.0f / ubuntu 9.4 default PHP5 / macport default PHP 5 ) but not with the PHP provided with Zend Server CE on linux, I still have to test it on windows and mac ... I don't think I'll do that this week, probably next week.

However I'm pretty sure it's not ZF related at all... I'll post this in PHP 5 bugs if you can confirm

Seems already there: #44081 http://bugs.php.net/bug.php?id=44081

I am postponing this issue to be addressed with ZF 2.0.

It seems there are too many moving parts involved here to provide a backwards compability way to ensure that ZF's Db Abstraction works the same in both PHP 5.2 and PHP 5.3.