Cannot execute queries while other unbuffered queries are active php error

Viewed 36

I'm facing a problem while executing some queries in a foreach loop. Only the first loop is executing and then this error is appearing:

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 many different ways but they didn't work. I added login parameters or even closeCursor after executing the query.

config.php:

<?php
//Database connection parameters

$host = 'xxxxxx';
$db   = 'xxxxxx';
$user = 'root';
$pass = 'xxxxxx';
$port = "xxxxxx";
$charset = 'utf8mb4';

    //connection
    $options = [
        \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
        \PDO::ATTR_EMULATE_PREPARES   => false,
        \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY =>true,
        
    ];
    
    $dsn = "mysql:host=$host;dbname=$db;charset=$charset;port=$port";
    try {
         $pdo = new \PDO($dsn, $user, $pass, $options);
    } catch (\PDOException $e) {
         throw new \PDOException($e->getMessage(), (int)$e->getCode());
    }

actions.php:

Of course, I added example variables etc..

require_once 'config.php';
      $sprawujacyP= array(
        0 => array(
          'plec' => 'Kobieta',
          'data_urodzenia'=>'1998-01-14'
        ),
        1 => array(
          'plec' => 'Mężczyzna',
          'data_urodzenia'=>'1998-01-14'
        ),
      );
      $id_r = 1;
      $id_op = 3;
      $outmsg="123";
      $sql = "CALL example_procedure(:id_r,:plec,:data_urodzenia,:id_op,:outmsg)";
      $statement = $pdo->prepare($sql);
      $statement->bindParam(':id_r',$id_r,PDO::PARAM_INT);
      $statement->bindParam(':id_op',$id_op,PDO::PARAM_STR);
      $statement->bindParam(':outmsg',$outmsg,PDO::PARAM_STR);
      
      foreach ($sprawujacyPas $sprawujacy) {
        try{
          $statement->bindParam(':plec',$sprawujacy['plec'],PDO::PARAM_STR);
          $statement->bindParam(':data_urodzenia',$sprawujacy['data_urodzenia'],PDO::PARAM_STR);
          if($statement->execute()){
              $statement->closeCursor();
            }
            else{
              echo 'error...';
            }
        }
        catch(PDOException $e){echo $e->getMessage();}   
        
      }
1 Answers

Okey, despite having this parameter in config.php I had to set it again before prepare statement and it helped. Maybe someone in need will see this :)

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
Related