我一直在尝试使用 PDO 从存储过程中获取结果集,但目前当对数据库进行多次调用时,它会给出一个错误,说
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
法典:
$table = $data["TABLE_NAME"];// this returns table names like table_1,table_2
$exc = $conn->prepare("CALL Dummy_2('$table')");
$exc->execute();
while($finalRes = $exc->fetch(PDO::FETCH_ASSOC)) {
$ID = substr($table,11);
$exc2 = $conn->prepare("CALL sp_new('$ID')");
$exc2->execute();// the place which triggers the error
if(false !== $result) {
$totals = array();
while($row = $exc2->fetch(PDO::FETCH_ASSOC)) {
$tot = new stdClass();
$tot->count = (int)$row['cnt'];
$tot->ucount = (int)$row['ucnt'];
$tot->date = new DateTime($row['dat']);
$totals[] = $tot;
}
var_dump($tot);
}
}
您正在尝试第二个预准备语句 ( $exc2
),而同一连接上已经有一个正在进行中。就像警告所建议的那样,尝试使用 fetchAll
并循环访问返回的数据,而不是一次获取一行 - 这样,您可以在开始第二行之前关闭第一个语句。
例
我没有时间测试它,但您可以尝试以下方法。我更改了第 4-5 行($dataset
和 foreach
行)。
$table = $data["TABLE_NAME"];// this returns table names like table_1,table_2
$exc = $conn->prepare("CALL Dummy_2('$table')");
$exc->execute();
$dataset = $ex->fetchAll();
foreach($dataset AS $finalRes) {
$ID = substr($table,11);
$exc2 = $conn->prepare("CALL sp_new('$ID')");
$exc2->execute();// the place which triggers the error
if(false !== $result) {
$totals = array();
while($row = $exc2->fetch(PDO::FETCH_ASSOC)) {
$tot = new stdClass();
$tot->count = (int)$row['cnt'];
$tot->ucount = (int)$row['ucnt'];
$tot->date = new DateTime($row['dat']);
$totals[] = $tot;
}
var_dump($tot);
}
}