CODEWITHSUNDEEP
phpdatabaseexceptionpdotransactions
Marco Sulla
Marco Sulla

Reputation: 15930

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

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).

Upvotes: 0

Views: 936

Answers (2)

Antony
Antony

Reputation: 4364

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.

Upvotes: 0

Robert
Robert

Reputation: 20286

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.

Upvotes: 1

Related Questions