“在其他未缓冲的查询处于活动状态时无法执行查询"循环错误 [英] "Cannot execute queries while other unbuffered queries are active" error in loop

问题描述

很抱歉提出这个问题.我已经阅读了很多类似的文章，但是从未找到我可以成功实现的解决方案.我发现的所有提示和技巧都对我毫无用处.

I'm sorry for asking this question. I've read alot just like it, but never found a solution that I successfully could implement. All the tips and tricks that I've found has been to no use for me.

我有一个大型关联数组，该数组具有要使用PDO存储过程插入到mysql数据库中的数据.

I have a large associative array with data that I want to insert into a mysql database using a stored procedure with PDO.

$data_arr = {a lot of data with keys: Name, Nbr, Val} //This is really 41 columns
$inputs = array('Name','Nbr','Val');
$query = 'CALL add_edit_standard(:Name,:Nbr,:Val)';
$stmt = $db->prepare($query);
foreach($inputs AS $Akey => $Aval){
    $values[$Aval]=0;
    $stmt->bindParam(':'.$Aval,$values[$Aval]);
}
foreach($data_arr AS $key => $val){
    $values = $val;
    $stmt->execute();
    $res = $stmt->fetchAll();
}

这对于$ data_arr中的第一行工作得很好，但第二行和其余行都抛出错误:

This works perfectly fine for the first row in $data_arr, but the second and the rest throws an error:

警告:PDOStatement :: execute():SQLSTATE [HY000]:常规错误:2014当其他无缓冲查询处于活动状态时，无法执行查询.考虑使用PDOStatement :: fetchAll().另外，如果您的代码只能在mysql上运行，则可以通过设置PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY属性来启用查询缓冲.

Warning: PDOStatement::execute(): 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.

我尝试了fetchAll()以及没有运气的连接属性. 我还尝试为每个通话做出新的声明:

I've tried with the fetchAll(), as well as the connection attribute with no luck. I've also tried to make a new statement for each call:

foreach($data_arr AS $key => $val)
{
    $values = $val;
    $stmt = $db->prepare($query);
    foreach($inputs AS $Akey => $Aval)
    {
        $stmt->bindValue(':'.$Aval,$values[$Aval]);
    }
    $stmt->execute();
    $res = $stmt->fetchAll();
    $stmt->closeCursor();
}

同样，这对于第一行来说效果很好，但是随后引发以下错误:

Again, this works perfectly for the first row, but then throws the following error:

警告:数据包乱序.预期收到1个57.数据包大小= 7

Warning: Packets out of order. Expected 1 received 57. Packet size=7

我已经尽力了.请帮助我找到一种使其工作的方法.

I have tried everything I've come up with. Please help me to find a way to make it work.

推荐答案

在您的execute循环中获取所有结果之后，应获取下一个行集，然后关闭游标，然后再次尝试执行存储过程.试试这个:

After you fetch all the results in your execute loop, you should get the next rowset and then close the cursor before attempting to execute the stored procedure again. Try this:

foreach($data_arr AS $key => $val){
    $values = $val;
    $stmt->execute();
    $res = $stmt->fetchAll();
    $stmt->nextRowset();   // NEW: Get the next rowset after fetching your results
    $stmt->closeCursor();  // NEW: Close the cursor
}

这里真正重要的补充是对 nextRowSet()的调用.在幕后，PDO将返回第二个行集，在同一连接上执行第二个(以及后续)存储过程之前，您需要访问该第二个行集.

The really important addition here is the call to nextRowSet(). Under the hood, PDO is returning a second rowset which you need to access before executing a second (and subsequent) stored procedure on the same connection.

这篇关于“在其他未缓冲的查询处于活动状态时无法执行查询"循环错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！