Re: BEGIN, ROLLBACK,COMMIT

Yuri,

Here's one way. Turn off autocommit, do all database inserts, updates, or
deletes within an eval block in order to trap exceptions. Then check $@. If it
is defined, an exception happened, so you might rollback. Otherwise, commit.

The code snippet below illustrates.

Paul Laub

> Hello,
>
>       When i starts transaction with BEGIN from my Perl scripts using
>       DBD::Pg module i want to get in script status after COMMIT
>       executed.
>
>
>       I mean, if ROLLBACK used i want to inform user to try again
>       later or something else
>
>       How can i do it
>
> ------------
> With respect,
> Yuri A. Kabaenkov
> hellman@artofit.com



my $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $username, $password,
    {
      AutoCommit => 0,  # Turn off autocommit to allow rollback.
      PrintError => 0,  # I create my own error messages using $handle->errstr
      RaiseError => 0   # I use die within eval to raise exceptions.
    }
) or die "Cannot connect!\n$dbh->errstr";

my $sqlinsert = "insert into ...";
my $sqlinsert_h = $dbh->prepare($sqlinsert);

eval {

    foreach $record (@array) {
        ...
        $sqlinsert_h->execute($arg1, $arg2)
            or die "\nERROR: SQL insert statement failed for "
                 . "arg1 $arg1, arg2 $arg2\n$sqlinsert_h->errstr";
        ...
    }
};

if ($@) {
    print "Rolling back on error.\n$@\n";
    $dbh->rollback();
} else {
    print "Transactions successful. Committing them.\n\n";
    $dbh->commit();
}

$dbh->disconnect;