Re: Transaction processing from a Perl script....

On Thursday 22 August 2002 8:36 pm, Björn Lundin wrote:

Issue the exec procedure with like

Exec("Begin Work")
Exec("insert into child table 1 ")
Exec("insert into child table 2 ")
Exec("insert into the master table")
Exec("Commit")
check result of commit,
if not everything is ok
Exec("Rollback")

I dont know the exact Perl syntax, but this is how I would go about to do
it...

/Björn

Greg Patnude wrote:

Ok.... I can connect to my postgreSQL database with Autocommit=>0 to set
up for a transaction-based process...I'm using Perl 5.6 and Pg 1.9 on a
FreeBSD 4.x box with postgreSQL 7.2.

The question is HOW should I go about managing the transaction itself
from within my Perl script... I need to {pseudo}:

--begin work
--insert into child table 1 (and return the new primary key pk1)
--insert into child table 2 (and retrun the new primary key pk2)
--insert into the master table (child 1 pk, child 2 pk) and return the
master primary key pkm
--commit work if everything is ok
--else rollback if anything failed.

Usually, this kind of thing is handled by the connect method (I've
typically used Oracle, Sybase, or SQL Server with VB, C, or Powerbuilder)
and the
{begin {process}{commit or rollback}} is managed by the connect method
defined in the application. Pg has no such mechanism that I could find in
the Pg docs...

Any ideas anyone ???

TIA

GP

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Have a look at Chapter 3 of the docs (Advanced Features), there's a bit about
transactions there which says PostgreSQL automatically supports them.

Excerpt:
"PostgreSQL actually treats every SQL statement as being executed within a
transaction. If you don't issue a BEGIN command, then each individual
statement has an implicit BEGIN and (if successful) COMMIT wrapped around it.
A group of statements surrounded by BEGIN and COMMIT is sometimes called a
transaction block. "
--
Geraint Jones

Greg Patnude wrote:

Ok.... I can connect to my postgreSQL database with Autocommit=>0 to set
up for a transaction-based process...I'm using Perl 5.6 and Pg 1.9 on a
FreeBSD 4.x box with postgreSQL 7.2.

First off I'd suggest using DBI and DBD::Pg instead of the Pg module

Usually, this kind of thing is handled by the connect method (I've
typically used Oracle, Sybase, or SQL Server with VB, C, or Powerbuilder)
and the
{begin {process}{commit or rollback}} is managed by the connect method
defined in the application. Pg has no such mechanism that I could find in
the Pg docs...

DBI is the standard 'DataBase Interface' for Perl (think: ODBC, JDBC).
It probably handles things in the way you're more familiar with.

It automatically starting transactions (at connection and after a
commit/rollback a new one is started) and will automatically die (if
using RaiseError => 1) on an error.

The question is HOW should I go about managing the transaction itself from
within my Perl script... I need to {pseudo}:

--begin work
--insert into child table 1 (and return the new primary key pk1)
--insert into child table 2 (and retrun the new primary key pk2)
--insert into the master table (child 1 pk, child 2 pk) and return the
master primary key pkm
--commit work if everything is ok
--else rollback if anything failed.

The following code shell should get you started (it has graceful error
catching):

#!/usr/bin/perl
use strict;
use warnings;

use DBI;
use Error;

...

my $dbh = DBI->connect('con_str', 'user', 'passwd', {
AutoCommit => 0,
RaiseError => 1,
}) or die "Could not establish connection: $!";

...

try {
my $sth = $dbh->prepare(q{
INSERT INTO table (field1, ... fieldn)
VALUES (?, ...?)
});

$sth->execute($foo, $bar);

...

$dbh->commit;
}
catch Error {
$dbh->rollback;
die "Failure on $err";
};

The above example is generalised to any DBMS with a DBD driver written
for it. As you can see DBI includes placeholder, automatic error
throwing, and a common syntax across DBMSs (SQL dialects are unchanged
ofcourse).

(There are also PostgreSQL specific attributes, see DBD::Pg
documentation for more info.)

The try/catch syntactical sugar is provided by the Error module, which
is by no means limited to the simplistic use shown here. If you don't
want to use a module, the form...

eval { code goes here }
if (@!) { error handling here }

... is straight base Perl.

Read up on the below documentation for more precise information.

SEE ALSO:

DBI - http://search.cpan.org/author/TIMB/DBI-1.30/DBI.pm
DBD::Pg - http://search.cpan.org/author/JBAKER/DBD-Pg-1.13/Pg.pm
Error -
http://search.cpan.org/author/BIRNEY/bioperl-1.0.2/examples/exceptions/Error.pm