I get 2014 Cannot execute queries while other unbuffered queries are active when doing exec with PDO

Question

I am doing a PDO::exec command on multiple updates:

$MyPdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);
$MyPdo->exec("update t1 set f1=1;update t2 set f1=2");

I am doing it inside a transaction, and I keep getting the following error:

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.

those are the only query/ies

score 0 · Answer 1 · answered Nov 25 '09 at 15:53

0

You shouldn't execute the statements as a single query. Execute them one at a time:

$MyPdo->exec("update t1 set f1=1");
$MyPdo->exec("update t2 set f1=2");

answered Nov 25 '09 at 15:53

Jordan Ryan Moore

6,877
2
26
27

Thanks, this is what I currently do, I need to find if there is a way to keep the queries as they are now. – Itay Moav -Malimovka Nov 25 '09 at 15:59

diyism · Answer 2 · 2010-05-19T02:36:51.757

I encountered the same problem, and haven't found any solution:

$pdo->exec('update....;update...;')
$pdo->exec('update....;update...;')

Before php 5.3 you could close connection by destroy $pdo, in php 5.3+, you could use $pdo->query() to execute multi-query, take care that $pdo->exec() finished does not mean the sql server finished the execution:

$cnt=1000;
$sql='';
$j=0;
for ($i=0;$i<$cnt;++$i)
    {++$j;
     $sql.="update sem_UploadKeywordQueue set ProductCount='{$i}' where ID='{$i}';";
     if ($j==100)
        {$pdo=new PDO('mysql:host=domain.com;port=3306;dbname=db', "user", "pass");
         $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         $pdo->exec($sql);
         $pdo=null;
         $sql='';
         $j=0;
        }
    }
if ($sql)
   {$pdo->exec($sql);
   }

for diyism

I get 2014 Cannot execute queries while other unbuffered queries are active when doing exec with PDO

2 Answers2

Linked