"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
– Juniperus, Commented Dec 16, 2013 at 10:07
Why are you trying to build dynamic queries using named parameters? — david strachan
– david strachan, Commented Dec 16, 2013 at 11:00
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
– Juniperus, Commented Dec 16, 2013 at 12:07
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
– Juniperus, Commented Dec 16, 2013 at 12:26

Matt · Accepted Answer · 2015-05-17 03:05:57Z

1

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.

answered May 17, 2015 at 3:05

Matt

3,9203 gold badges28 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Juniperus Over a year ago

Thank you for your feedback. I will check this out. I actually don't remember how I solved this. I probably did some kind of workaround. I will try your solution next time. Thanks!

david strachan · Accepted Answer · 2013-12-16 13:39:20Z

0

Using unnamed parameters for dynamic queries are simpler than using named ones.

ie INSERT

$columnNames = array("id","name","address"); 
$columnStr ='';
foreach ($columnNames as $value)
    {
    $columnStr .= $value.",";
    }
$columnStr = substr($columnStr, 0, -1);
$values = array(1,"john","Home"); 
$params  = str_repeat('?, ', count($values) - 1) . '?';
$sql = "INSERT INTO table1 ($columnStr)  VALUES ($params) ";
$stmt = $dbh->prepare($sql); 
$stmt->execute($values);

Produces query

INSERT INTO table1 (id,name,address) VALUES (?, ?, ?)

answered Dec 16, 2013 at 13:39

david strachan

7,2282 gold badges25 silver badges33 bronze badges

1 Comment

Juniperus Over a year ago

Thank you for streamlining my code. I really appreciate the feedback. It is a lot better compared to using named parameters. My question however is still unanswered. Both of my original approaches work for single calls, even though they might not be the most efficient. The problem lies in that the calls are made in a loop, and that something is causing the second, and so on, call to fail.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related