From: | Arguile <arguile(at)lucentstudios(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transaction processing from a Perl script.... |
Date: | 2002-08-23 22:42:42 |
Message-ID: | 1030142563.279.89.camel@broadswd |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | cn | 2002-08-24 01:12:58 | Re: Controling Rule's Firing Order |
Previous Message | Jeff Davis | 2002-08-23 20:44:47 | Re: Mysql -> PgSQL |