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

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

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