Thread

1. AW: [GENERAL] Re: [HACKERS] TRANSACTIONS

   Zeugswetter Andreas SB <zeugswettera@wien.spardat.at> — 2000-02-24T09:18:58Z

   > ========================= 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
   
   

2. Re: AW: [GENERAL] Re: [HACKERS] TRANSACTIONS

   Oleg Bartunov <oleg@sai.msu.su> — 2000-02-24T10:46:38Z

   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__
   
   
   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
   
   
   

3. Re: AW: [GENERAL] Re: [HACKERS] TRANSACTIONS

   Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-02-24T15:23:23Z

   >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