"Cannot execute queries while other unbuffered queries are active" error in loop

Question

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.

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();
}

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

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.

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:

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.

I see that someone gave me a downvote. It would be nice to know why. What information is lacking in my question? Is there an answer to it somewhere else that I have missed? — Juniperus
Why are you trying to build dynamic queries using named parameters? — david strachan
Because there are so many parameters, 41 of them. It would be a hack to create 41 named variables, one for each parameter and then set all in the loop. Do you think that is creating the problem? Its working for the first row of the data array, so it seems like it is something else to me. — Juniperus
I still need to order them correctly according to the input order of the stored procedure. But I will try that. Thanks. I still don't understand why this would help though. — Juniperus

Matt Matt · Accepted Answer · 2015-05-17T03:05:57

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
}

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.

"Cannot execute queries while other unbuffered queries are active" error in loop

2 Answers