Maybee some other have the same problem than me. I run over the error:
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.
on PDO. As in many threads mentioned the error can at be at least one of the following problems:
closeCursor()
as mentioned here; Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active
In my case all above did not help and it took some time till i solved the problem. this was my code (pseudo-code):
$stmt->startTransaction();
$stmt = db::getInstance()->prepare("CALL phones(:phone)");
$stmt->prepare('SELECT * FROM database');
$stmt->execute();
$aData = $stmt->fetchAll();
$stmt->closeCursor();
$stmt->query("USE sometable;");
After I changed it to:
$stmt->startTransaction();
$stmt = db::getInstance()->prepare("CALL phones(:phone)");
$stmt->prepare('SELECT * FROM database');
$stmt->execute();
$aData = $stmt->fetchAll();
$stmt->closeCursor();
$stmt->exec("USE sometable;");
It worked for my. What is the difference between query and exec?
PDO::exec() - "Execute an SQL statement and return the number of affected rows"
PDO::query() - "Executes an SQL statement, returning a result set as a PDOStatement object"
Why in this case PDO::query() does not work? The cursor IS closed, when called.
While it could conceivably be true that you've encountered the mysql driver bug here, we can't be sure of that because you've not given us that information (what version of PHP are you using? Does it use mysqlnd
=> check with php -i | grep mysqlnd
? What does the rest of your code look like?).
There are many other possible explanations for your problem. I suspect the issue is actually your failing to close all the cursors, and/or fetch all the results, because $stmt
is being reused heavily:
Quoted directly from the PDO::query
manual page:
If you do not fetch all of the data in a result set before issuing your next call to
PDO::query()
, your call may fail. CallPDOStatement::closeCursor()
to release the database resources associated with thePDOStatement
object before issuing your next call toPDO::query()
.
You call closeCursor
on $stmt
, that's true, but you've not closed all cursors that have been created by you:
//<-- what is $stmt here?
$stmt->startTransaction();
//no matter, you've reassigned it a PDOStatement instance
$stmt = db::getInstance()->prepare("CALL phones(:phone)");
//Huh? You're preparing yet another query on an instance of PDOStatement?
$stmt->prepare('SELECT * FROM database');
//you're executing this one, though
$stmt->execute();
//and fetching all data
$aData = $stmt->fetchAll();
//and closing this last statement
$stmt->closeCursor();
But what about the first statement you assigned to $stmt
(the stored procedure call)? That cursor isn't closed anywhere
Now for the major difference between PDO::query
and PDO::exec
. Again, quoting the manual:
PDO::exec()
does not return results from a SELECT statement.
Whereas:
PDO::query()
executes an SQL statement in a single function call, returning the result set (if any) returned by the statement as aPDOStatement
object.
I came across this problem too. It is likely to be a bug. If we take the following code, then you will see how it fails with the message 'General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll().'
$pdo = new \PDO("mysql:host=localhost", "root", "");
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
$pdo->query("USE test");
If you change $pdo->query("USE test");
to $pdo->exec("USE test");
it will work. If you change $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
to $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);
it will also work. I haven't been able to find a proper solution yet though.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With