PDO Transaction: how to know which queries caused an error?

Question

I'm using PDO inside a PHP script to do SQL queries. I want to do a transaction with more than one query and catch the errors. The code is like this:

try
{
    $dbh = new PDO(...);
    $dbh-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch
{
    ...
}

$query1 = 'DO some QUERY';
$query2 = 'DO other QUERY';

try
{
    $dbh->beginTransaction();
    $dbh->exec($query1);
    $dbh->exec($query2);
    $dbh->commit();
}
catch
{
    ...
}

How can I know which query caused the error? I want to do this because I want to ignore a duplicate key error for the first query but not for the second one, without using SELECT (see also a previous question of mine about UNIQUE error handling).

@user1615903: that's what I'm doing now, but I would use transactions to speed up queries. — Marco Sulla, May 24 '13 at 10:04
How does two try-catch blocks prevent you from using transactions? — 1615903, May 24 '13 at 10:05
you can use serveral try catch blocks and print message in catch() block and throw your own exception to be caught in the other block where you rollback() your transaction — Robert, May 24 '13 at 10:05
@user1615903: well, you're right... I supposed it couldn't work because my last SQL error was originated by execute(). — Marco Sulla, May 24 '13 at 10:30

score 1 · Accepted Answer · edited May 24 '13 at 11:21

try
{
    $dbh->beginTransaction();

    try
    {
       $dbh->exec($query1);
    }
    catch (PDOException $e)
    {
        throw new MyNewExtendedPdoException('first query exception'); // or just simply rollback + message
    }

    try
    {
        $dbh->exec($query2);
    }
    catch(PDOException $e)
    {
        throw new MyNewExtendedPdoException('2nd query exception');// or just simply rollback + message
    }

    $dbh->commit();
}
catch (MyNewExtendedPdoException $ex)
{
   $dbh->rollBack();
   echo $ex->getMessage();
}

it can be done on various ways that is one of this way. You don't have to create your own exception class you can rollback your transaction in every try catch {} block then it will be simpler.

score 0 · Answer 2 · answered Apr 06 '18 at 18:49

If you are only running a few queries - the SQL error will normally tell you what the error is (if you use $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);. The solution I used was to create an array of all my queries and then outputted them in the Rollback section - it should be obvious from the error message and the queries you've got where the error lies. If you have large or lots of queries then this may be a bit much.

PDO Transaction: how to know which queries caused an error?

2 Answers2