Cannot execute queries while other unbuffered queries are active PDO PHP

Question

I have been trying to get a result set from a stored procedure using PDO, but currently when make several call to the database it gives an error saying

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

Code:

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

Adam Hopkinson · Accepted Answer · 2015-01-29 09:19:22Z

2

You're attempting a second prepared statement ($exc2) while there is already one in progress on the same connection. Like the warning suggests, try using fetchAll and looping through the returned data, instead of fetching lines one at a time - this way, you can close the first statement before beginning the second.

Example

I've not got time to test it, but you could try the below. I've changed lines 4-5 ($dataset and 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);
    }
}

edited Jan 29, 2015 at 9:19

answered Jan 29, 2015 at 8:51

Adam Hopkinson

28.8k9 gold badges74 silver badges105 bronze badges

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

2 Comments

Prog_Rookie Over a year ago

thanks adam ill check it... im new to PDO thats why :P

Prog_Rookie Over a year ago

still get the same error bro... triggers from same place

Collectives™ on Stack Overflow

Cannot execute queries while other unbuffered queries are active PDO PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related