Laravel - PDO Prepared Statement - Cannot execute queries while other unbuffered queries are active

Question

I am having trouble running the following prepared statement in Laravel:

$pdo = DB::connection()->getPdo();
$ps_TempTable_PushCsv = $pdo->prepare(
    "LOAD DATA LOCAL INFILE '123'
    INTO TABLE `123`
    CHARACTER SET utf8mb4
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\\n'"
);
$ps_TempTable_PushCsv->execute();
$ps_TempTable_PushCsv->closeCursor();
$rowCount = $ps_TempTable_PushCsv->rowCount();

I get the following error:

[2017-06-08 03:41:35] local.ERROR: PDOException: 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.

This is the entry-point of my controller, so there are definitely no prior queries running.

What do?

Thanks

mils · Accepted Answer · 2017-06-08 04:52:06Z

12

The only way I could get it working was to replace the prepared statement with an 'exec' call:

$rowCount = DB::connection()->getpdo()->exec(
            "LOAD DATA LOCAL INFILE '$fileName'
            INTO TABLE $tableName
            CHARACTER SET utf8mb4
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '\"'
            LINES TERMINATED BY '\\n'"
        );

I have no idea why it wouldn't work using a prepared statement in Laravel - it definitely does work with a pure PDO prepared statement.

answered Jun 8, 2017 at 4:52

mils

1,9302 gold badges23 silver badges46 bronze badges

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

4 Comments

tadman Over a year ago

You really should avoid grabbing the PDO handle and instead use something like DB::insert with a raw query.

tadman Over a year ago

One way to find out is to try it. As far as I know those handlers are pretty generic.

summea Over a year ago

@tadman For what it's worth, DB::insert didn't seem to work for me in this type of situation. Thanks for the suggestion, though!

Muhammad Over a year ago

PDO::exec(): LOAD DATA LOCAL INFILE forbidden

Dustin · Accepted Answer · 2018-12-17 21:07:44Z

2

If you go forward with the accepted answer, make sure to add the following code when creating your connection:

PDO::MYSQL_ATTR_LOCAL_INFILE => true

For example:

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::MYSQL_ATTR_LOCAL_INFILE => true
];
$pdo = new PDO("mysql:host=" . MYSQL_HOST . ";dbname=" . MYSQL_DB, MYSQL_USER, MYSQL_PASSWORD, $options);

edited Dec 17, 2018 at 21:07

user1531971

answered Dec 17, 2018 at 19:05

Dustin

1231 silver badge10 bronze badges

Comments

Rohit Sonawane · Accepted Answer · 2022-06-28 03:21:26Z

-1

If you are using DB::SELECT() to run raw queries.. In that case for INSERT, UPDATE & DELETE, it will hang for SELECT, so as per documentation You can simply use DB::INSERT('your insert query'), DB::UPDATE('your update query'), DB::DELETE('your delete query')

It works for me...

answered Jun 28, 2022 at 3:21

Rohit Sonawane

311 silver badge7 bronze badges

Collectives™ on Stack Overflow

Laravel - PDO Prepared Statement - Cannot execute queries while other unbuffered queries are active

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related