How can I get an error when running multiple queries with PDO?

Question

I'm running multiple queries using PDO. If the second query fails, no Exception is thrown.

$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "
DELETE FROM car; 
INSERT INTO car(name, type) SELECT name, from FROM vehicle;
";

try {
    $db->exec($sql);
}
catch (PDOException $e)
{
    echo $e->getMessage();
    die();
}

The above code executes without throwing an exception.

How can I make sure that all queries have run successfully? How can I check which queries have failed?

P.S. I'm using PDO multi query to run MySQL dumps, but any valid .sql file should work.

The best way would be to run them one at a time. Also, you have an SQL syntax error in your `INSERT` query - the `SELECT` statement needs another column name after `name, ` — Kryten, Apr 23 '14 at 14:53
@Kryten I'm using PDO to run MySQL dumps. Splitting a dump up is a daunting task. The error in the example is on purpose. — Arnold Daniels, Apr 23 '14 at 14:58
Ah, I see. In that case, the only suggestion I have is to examine the `PDOException` in your `catch` block & see if it includes more details on the specific query that failed. Start with a `print_r($e)` in the `catch` block... — Kryten, Apr 23 '14 at 15:03
Would placing a `print_r($db->errorInfo());` in the catch block work? — Jens A. Koch, Apr 23 '14 at 15:12
One dirty way could be to split the sql-string at `;\n` and run each of them one-by-one. Dirty hack, but I think it would solve the problem. Edit: Darn, this was already suggested. — OptimusCrime, Apr 23 '14 at 23:42

score 10 · Accepted Answer · answered Apr 24 '14 at 02:24

I found the answer in using a prepared statement. After looping through all rowsets, I can check if the last query executed caused an error using $stmt->errorInfo().

$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

$sql = "
DELETE FROM car; 
INSERT INTO car(name, type) SELECT name, from FROM vehicle;
";

$stmt = $db->prepare($sql);
$stmt->execute();
$i = 0;

do {
  $i++;
} while ($stmt->nextRowset());

$error = $stmt->errorInfo();
if ($error[0] != "00000") {
  echo "Query $i failed: " . $error[2];
  die();
}

in your solution if incorrect a statement occurs, execute any other statement but This (this link: https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd/55169977#55169977) solution helps you to run multiple statement together, If an incorrect a statement occurs, it does not execute any other statement — Sajad Mirzaei, Mar 16 '19 at 07:20

score 1 · Answer 2 · answered May 01 '14 at 22:59

Following code will show how to catch error while running multiple queries. Try avoiding using "DELIMITER //". Queries are generally separately by ";".

<?php
// Create SQL List
$sqlStatements = "SELECT 100;SELECT 200;SELECT 300; Error SELECT 400;SELECT 500;";

// Prepare and execute statements
$options = [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION];
$db = new PDO("mysql:host=127.0.0.1;dbname=test", 'root', '', $options);

// Prepare and execute
$error = false;
$statement = $db->prepare($sqlStatements);
$statement->execute();

// Check error
try{
    do{
        // Print Resultset
        $rowset = $statement->fetchAll(PDO::FETCH_NUM);
        var_dump($rowset);
    }while($statement->nextRowset());

}catch(\Exception $e){
    echo $e->getMessage()."\n";
}
?>

score -1 · Answer 3 · answered Apr 23 '14 at 15:25

-1

There's no clear&easy way to do that.

I think, the best way is to read dump file query-by-query and execute one query at a time.

You can also parse error message and extract problem line number if DB driver tells it (like 'unknown identifier 'blabla' on line 666').

answered Apr 23 '14 at 15:25

SlyChan

769
4
15

1

That would get me from fire into the frying pan. It's very likely that the queries have ";" in their values. Also I don't wan't to put the emphasis on the format of the dump file. Any valid .sql file should do. – Arnold Daniels Apr 23 '14 at 23:37

How can I get an error when running multiple queries with PDO?

3 Answers3

Linked