在PHP中，当我使用PDO调用MySQL存储过程，然后另一个查询时，我收到一个错误：[重复]

Question

This question already has an answer here:

• PDO Cannot execute queries while other unbuffered queries are active 4 answers

In PHP, when I call a MySQL stored procedure using PDO, and then another PDO query, just like this:

$dbh = new PDO('mysql:host=localhost;dbname=db1','user1','password1');

$query = "CALL get_token()";
$stmt = $dbh->query($query);
$array = $stmt->fetchAll();

$query = "SELECT * FROM `table1`";
$stmt = $dbh->query($query);
$array = $stmt->fetchAll();

The MySQL stored procedure is about like this:

CREATE PROCEDURE `get_token`()
BEGIN
    DECLARE token CHAR(64);
    DECLARE expire SMALLINT;

    SELECT `token`, `expire` INTO token, expire FROM `token`;

    SELECT token, expire;
END$$

And I got the following error message (using try...catch to catch it):

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.

Even if I followed the instructions described in the above error message (that means using fetchAll() and setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute), I still got the same error message.

If I change the first query to a normal SELECT SQL query, instead of a stored procedure, I won't get this error. So it seems that the problem arises from the stored procedure.

But how can I fix this?

</div>

Answer 1

That's because you are not freeing the cursor of the first query. It still waits for another fetchAll. From http://php.net/manual/en/pdo.query.php

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. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().

So $stmt->closeCursor(); after first $array = $stmt->fetchAll(); should be sufficient.