Issues

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

Issue Type: Bug Created: 2007-10-26T08:22:20.000+0000 Last Updated: 2011-05-07T17:24:45.000+0000 Status: Postponed Fix version(s): - Next Major Release ()

Reporter: Darby Felton (darby) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

Related issues: - ZF-2097

Attachments:

Description

Patrick Calkins writes:

{quote} Code:

<pre class="highlight">
$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

Posted by Darby Felton (darby) on 2007-10-26T08:23:14.000+0000

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

Posted by Wil Sinclair (wil) on 2008-03-21T17:05:32.000+0000

This issue should have been fixed for the 1.5 release.

Posted by Wil Sinclair (wil) on 2008-03-25T20:43:55.000+0000

Please categorize/fix as needed.

Posted by Wil Sinclair (wil) on 2008-04-18T13:11:53.000+0000

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

Posted by Simon Mundy (peptolab) on 2008-05-26T23:24:12.000+0000

Resolved in r9539

Posted by Ralph Schindler (ralph) on 2008-06-17T08:36:40.000+0000

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

Posted by Wil Sinclair (wil) on 2008-09-02T10:38:59.000+0000

Updating for the 1.6.0 release.

Posted by Brian Fisher (brian3f) on 2009-01-14T09:11:55.000+0000

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

Posted by John Simone (jsimone) on 2009-01-19T12:41:16.000+0000

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.

Posted by Sébastien Barbieri (scips) on 2009-07-22T06:46:26.000+0000

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.

Posted by Ralph Schindler (ralph) on 2009-07-22T12:13:52.000+0000

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

Posted by Oliver Kastler (1730) on 2009-07-22T14:10:30.000+0000

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...

Posted by Sébastien Barbieri (scips) on 2009-07-23T01:26:20.000+0000

$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

Posted by Sébastien Barbieri (scips) on 2009-07-23T01:40:04.000+0000

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

Posted by Ralph Schindler (ralph) on 2011-02-17T14:28:58.000+0000

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.

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts