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 $(at)(dot) 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(at)artofit(dot)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(dot)\n$(at)\n";
$dbh->rollback();
} else {
print "Transactions successful. Committing them.\n\n";
$dbh->commit();
}

$dbh->disconnect;