CDbCommand :: fetchColumn()失败:SQLSTATE [HY000]:常规错误:2014当其他未缓冲的查询处于活动状态时，无法执行查询 [英] CDbCommand::fetchColumn() failed: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active

问题描述

我希望能够在OUT参数中从mysql存储过程中获取一些输出，但是对于存储过程还通过选择语句(如下面的语句)返回结果集的情况.

I want to be able to get some output from mysql stored procedure within OUT params but for case when stored procedure also returns result set via select statement like one below.

DELIMITER $$

DROP PROCEDURE IF EXISTS `RD`.`sp`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp`(in b int, out a int)
BEGIN
select 664656 into a;

select 5;
END$$

DELIMITER ;

在下面提到的命令中一个一个地运行

Runing in console below mentioned command one by one

call sp(5,@a)
select @a

我们会得到结果 5 => 5 @a => 664656

we will get result 5 => 5 @a => 664656

但是，当我尝试从php运行相同的代码时，出现以下错误

However when I try to run the same code from php getting error below

[error] [system.db.CDbCommand] CDbCommand::fetchColumn() failed: 
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.. The SQL statement executed was: select @a as result;.

请在下面找到我的PHP代码.

Below please find my php code.

$connection = Yii::app ()->db;
    $b=7;
    $command = $connection->createCommand ( "CALL sp(:b,@a)" );
    $command->bindParam ( ":b", $b, PDO::PARAM_INT );
    $command->execute();

    $command->getPdoStatement()->fetchAll();

    $command->getPdoStatement()->closeCursor();

    $valueOut = $connection->createCommand ( "select @a as result;" )->queryScalar ();


    echo $valueOut;

任何想法如何解决?

PHP版本5.3.1

PHP Version 5.3.1

Yii版本1.1.9

Yii version 1.1.9

MySQL版本5.1.41-3ubuntu12.10

Mysql Version 5.1.41-3ubuntu12.10

推荐答案

最后，我发现了问题，它与php版本有关. https://bugs.php.net/bug.php?id=39858 我将版本更新到PHP版本5.3.2，现在可以正常使用了. 请考虑到它对于相同版本的Windows无效，而仅在Linux下有效.(至少对我而言).

At the end I found issues and it was with php version. https://bugs.php.net/bug.php?id=39858 I updated version till PHP Version 5.3.2 and it works fine now. please take in consideration that it doesn't work under windows for the same version and only works under linux.(At least for me).

这篇关于CDbCommand :: fetchColumn()失败:SQLSTATE [HY000]:常规错误:2014当其他未缓冲的查询处于活动状态时，无法执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！