Thread: AW: [GENERAL] Re: [HACKERS] TRANSACTIONS

> ========================= example =========================
> 
> $dbh->{AutoCommit} = 0;
> $dbh->do("CREATE TABLE tmp (a int unique,b int)");
> while (<>){
>     if (/([0-9]+) ([0-9]+)/) {
>     $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)");
>     if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")};
>     }
> }
> $dbh->commit;
> $dbh->disconnect;
> 
> ========================= end ============================

This is a very good example, and is unfortunately currently not possible in 
PostgreSQL. But I am sure Vadim is on his way to fix that :-)

Andreas

Here is my example:

package Apache::Hits;

use strict;
use Apache::Constants qw(:common);

my ($sth_lock, $sth_upd, $sth_ins );


sub handler { my $r = shift; if ( $r->args() =~ /msg_id=(\d+)/o ) {   $HTML::Mason::Commands::dbs ||= My::DB->new(1);
my$dbh = $HTML::Mason::Commands::dbs->{dbh};   $sth_lock ||= $dbh->prepare("LOCK TABLE hits IN SHARE ROW EXCLUSIVE
MODE");  $sth_upd  ||= $dbh->prepare("UPDATE hits SET count=count+1,last_access=now() WHERE msg_id=?");   $sth_ins  ||=
$dbh->prepare("INSERTINTO hits (msg_id,count) VALUES (?, 1)");   $dbh->{AutoCommit} = 0;   my $success = 1;   $success
&&=$sth_lock->execute();   $success &&= $sth_upd->execute( $1 );   $success &&= $sth_ins->execute( $1 ) if ( $success
eq'0E0');   my $result = ($success ? $dbh->commit : $dbh->rollback);   unless ( $result ) {       $r->log_error("Unable
toprocess transaction: ". $dbh->errstr ."\n");   } } return OK;
 
}

1;
__END__


On Thu, 24 Feb 2000, Zeugswetter Andreas SB wrote:

> Date: Thu, 24 Feb 2000 10:18:58 +0100
> From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
> To: "'kdebisschop@range.infoplease.com'"    <kdebisschop@range.infoplease.com>
> Cc: "'hackers@postgresql.org'" <hackers@postgreSQL.org>
> Subject: AW: [GENERAL] Re: [HACKERS] TRANSACTIONS
> 
> > ========================= example =========================
> > 
> > $dbh->{AutoCommit} = 0;
> > $dbh->do("CREATE TABLE tmp (a int unique,b int)");
> > while (<>){
> >     if (/([0-9]+) ([0-9]+)/) {
> >     $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)");
> >     if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")};
> >     }
> > }
> > $dbh->commit;
> > $dbh->disconnect;
> > 
> > ========================= end ============================
> 
> This is a very good example, and is unfortunately currently not possible in 
> PostgreSQL. But I am sure Vadim is on his way to fix that :-)
> 
> Andreas
> 
> ************
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

>Here is my example:
>
>package Apache::Hits;
>
>use strict;
>use Apache::Constants qw(:common);
>
>my ($sth_lock, $sth_upd, $sth_ins );
>
>
>sub handler {
>  my $r = shift;
>  if ( $r->args() =~ /msg_id=(\d+)/o ) {
>    $HTML::Mason::Commands::dbs ||= My::DB->new(1);
>    my $dbh = $HTML::Mason::Commands::dbs->{dbh};
>    $sth_lock ||= $dbh->prepare("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE");
>    $sth_upd  ||= $dbh->prepare("UPDATE hits SET count=count+1,last_access=now() WHERE msg_id=?");
>    $sth_ins  ||= $dbh->prepare("INSERT INTO hits (msg_id,count) VALUES (?, 1)");
>    $dbh->{AutoCommit} = 0;
>    my $success = 1;
>    $success &&= $sth_lock->execute();
>    $success &&= $sth_upd->execute( $1 );
>    $success &&= $sth_ins->execute( $1 ) if ( $success eq '0E0');
>    my $result = ($success ? $dbh->commit : $dbh->rollback);
>    unless ( $result ) {
>        $r->log_error("Unable to process transaction: ". $dbh->errstr ."\n");
>    }
>  }
>  return OK;
>}
>
>1;
>__END__

Maybe I'm missing something - I don't use the Apache module, nor the
mason module, so it's difficult for me to decode this.  But I looks to
me like this commits or rolls back one statement at a time. 

What I want to be able to do is run a multi-statement transaction, and
for each statement in the transaction try to fix it if an error is
generated.  If I cannot fix it, I want to roll back all of the
previous statements in the transaction.  If I can fix it, I want to
clear the error status and continue on to the next statement in the
transaction.

Does your counter example do this in some way that I am not seeing?

-- 
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com  - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net