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

问题描述：

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>

此问题已经存在这里有一个答案： p>

PDO当其他无缓冲的查询处于活动状态时无法执行查询 4 answers span> li> ul> div>

在PHP中，当我调用时 MySQL code> 存储过程 code>使用 PDO code>，然后是另一个 PDO code>查询，如下所示： p> $ dbh = new PDO（'mysql：host = localhost; dbname = db1'，'user1'，'password1'）; $ query =“CALL get_token（）”; $ stmt = $ dbh-＆gt; query（$ query）; $ array = $ stmt-＆gt; fetchAll（）; $ query =“SELECT * FROM`table1`”; $ stmt = $ dbh- ＆gt; query（$ query）; $ array = $ stmt-＆gt; fetchA ll（）; code> pre>

MySQL code> 存储过程 code>大致如下： p>

  CREATE PROCEDURE`get_token`（）
BEGIN 
 DECLARE令牌CHAR（64）; 
 DECLARE到期SMALLINT; 
 
 SELECT`token`，`expire` INTO令牌，到期FROM  token`; 
 
 SELECT令牌，到期; 
END $$ 
  code>  pre> 
 
 我收到以下错误消息（使用 try ... catch   code>来捕获它）： p> 
 
 
 
 常规错误：2014当其他未缓冲的查询处于活动状态时，无法执行查询。 考虑使用PDOStatement :: fetchAll（）。  
或者，如果您的代码只是针对mysql运行，
您可以通过设置
 PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY属性来启用查询缓冲。 p> 
  blockquote> 
 
 
即使我按照上述错误消息中描述的指令（即使用 fetchAll（） code>并设置 PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY  code>属性），我仍然得到相同的错误 消息。 p> 
 
 
如果我将第一个查询更改为普通的 SELECT  code> SQL查询，而不是存储过程 code>，我将不会 得到这个错误。 因此，问题似乎来自存储过程 code>。 p> 
 
 
但我该如何解决这个问题呢？ p> 
  div>

答

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.

答

You should use either PDO::MYSQL_ATTR_USE_BUFFERED_QUERY OR fetchAll() as they conflict

According to the documentation :

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (integer) If this attribute is set to TRUE on a PDOStatement, the MySQL driver will use the buffered versions of the MySQL API. If you're writing portable code, you should use PDOStatement::fetchAll() instead.

In other words, fetchAll() tries to close the buffer but PDO::MYSQL_ATTR_USE_BUFFERED_QUERY keeps it open

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

相关推荐