ID: 44081
Comment by: webmaster at rdwonline dot com
Reported By: abouzekry at gmail dot com
Status: Open
Bug Type: PDO related
Operating System: Windows XP SP2
PHP Version: 5.2.9
New Comment:
I am using XAMPP (PHP 5.3.0 on Windows XP) and I am able to execute
succeeding prepared statements with no problem as long as I call
$PDOStatement->closeCursor() after I'm done with the previous one.
The problem arises with MySQL stored procedures. PDO will execute and
successfully retrieve records from the first stored procedure but all
other statements afterwards WILL NOT EXECUTE, and I too get the error
below:
"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 tried adding in the aforemention attribute to no avail.
Here is a snippet from a script I used to test this:
$PDOStatement = $PDO->prepare('SELECT * FROM MagicCards WHERE cardID =
?');
$PDOStatement->bindValue(1, 1600);
$PDOStatement->execute();
$PDOStatement->setFetchMode(\PDO::FETCH_ASSOC);
$rows = $PDOStatement->fetchAll();
$PDOStatement->closeCursor();
$PDOStatement = NULL;
print_r($rows); // works as expected
$PDOStatement = $PDO->prepare('CALL uspGetMagicCardByKey(?)');
$PDOStatement->bindValue(1, 10);
$PDOStatement->execute();
$PDOStatement->setFetchMode(\PDO::FETCH_ASSOC);
$rows = $PDOStatement->fetchAll();
$PDOStatement->closeCursor();
$PDOStatement = NULL;
print_r($rows); //works as expected
$PDOStatement = $PDO->prepare('SELECT * FROM MagicCards WHERE cardID =
?');
$PDOStatement->bindValue(1, 1600);
$PDOStatement->execute(); //errors out right here with the HY000 error
$PDOStatement->setFetchMode(\PDO::FETCH_ASSOC);
$rows = $PDOStatement->fetchAll();
$PDOStatement->closeCursor();
$PDOStatement = NULL;
print_r($rows); //doesn't get this far
The workaround would be to drop in your SQL from your stored procedure
directly as an argument to $PDO->prepare(). But if you are doing any
other modifications before or after that main SELECT statement, this is
not a feasible solution.
This is a critical bug that needs to be fixed ASAP.
Previous Comments:
------------------------------------------------------------------------
[2009-07-23 08:30:58] sebastien dot barbieri at gmail dot com
Same issue with PHP 5.2.10 (Linux/Mac/Windows) with a similar code:
$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();
However no problem at all if I install php 5.2.10 Zend Community
Edition ... :-)
------------------------------------------------------------------------
[2009-06-10 21:49:52] stewart dot duncan at rocketmail dot com
Confirmed, still broken in latest snapshot.
------------------------------------------------------------------------
[2009-05-16 20:12:05] abouzekry at gmail dot com
the bug wasn't resolved in the latest code snapshot, it's more than a
year now!!!
------------------------------------------------------------------------
[2009-05-08 20:11:04] xfurious at gmail dot com
Unless I'm losing my mind, this is happening on PHP 5.2.9-2/windows as
well.
------------------------------------------------------------------------
[2009-05-03 01:00:09] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/44081
--
Edit this bug report at http://bugs.php.net/?id=44081&edit=1
