X-Original-To: pgsql-interfaces@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 18DC1474E53 for ; Mon, 10 Feb 2003 06:25:14 -0500 (EST) Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id 851C6474E44 for ; Mon, 10 Feb 2003 06:25:13 -0500 (EST) Received: by news.hub.org (Postfix, from userid 8) id 456F443A91A; Mon, 10 Feb 2003 06:25:13 -0500 (EST) From: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= X-Newsgroups: comp.databases.postgresql.interfaces Subject: DBI transaction handling ... Date: Mon, 10 Feb 2003 12:25:26 +0100 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 555 Message-ID: <3E478C26.7000800@cybertec.at> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed X-Complaints-To: usenet@news.hub.org To: rlippan@remotelinux.com, nandrews@investsystems.co.uk User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20020826 X-Accept-Language: en-us, en To: pgsql-interfaces@postgresql.org Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/33 X-Sequence-Number: 3654 I have updated to the latest version of DBI/DBD. I have no idea why DBI performs an implicit ROLLBACK since I want to=20 perform a COMMIT. I have compiled the code below. The INSERT stuff seems to work but the=20 UPDATE stuff won't be visible at the end of the transactions. My COMMIT=20 statement does not bomb out an error but performs a ROLLBACK instead.=20 Maybe somebody has similar problems with DBI/DBD. Maybe it is a bug=20 somewhere. Can anybody help me? Two of us had a closer at it but nobody has found=20 the error so far. Maybe it is a bug inside the DBI/DBD stuff. Hans #!/usr/bin/perl -I/usr/local/shoppingnet/lib/ # www.postgresql.at, Sch=F6nig 2003 use strict; use DBI; use debug; use config; use database; use helper; # Verbinde zur Datenbank ... my $config =3D config->new(); my $proddb =3D database->new(db =3D> $config->{DB}, parameter =3D> $config->{PGCONNECT}, autocommit =3D> 0 ); my $proddb2 =3D database->new(db =3D> $config->{DB}, parameter =3D> $config->{PGCONNECT}, autocommit =3D> 0 ); $proddb->dbi_begin(); $proddb->dbi_do("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); # $proddb2->dbi_begin(); $proddb2->dbi_do("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); # Auslesen der Lieferanten, die unfertige Bestellungen haben $proddb->dbi_select("SELECT DISTINCT lief_nr FROM t_bestell_arch WHERE abgeholt IS NULL OR abgeholt =3D 'f' "); my @xarray; for (my $i =3D 0; $i < $proddb->{rows}; $i++) { push @xarray, $proddb->{data}->[$i][0]; } foreach my $lief_nr (@xarray) { my $kundendb =3D "K".$lief_nr; my $newdbh =3D database->new(db =3D> "dbname=3D$kundendb", parameter =3D> $config->{KUNDENCONNECT}, autocommit =3D> 0 = ); # $newdbh->dbi_begin(); my @line; $proddb->dbi_select("SELECT transakt_nr, kd_nr_lief, name1, name2, strasse1, strasse2, land_iso, plz,=20 telefon, fax, email FROM t_bestell_arch WHERE t_bestell_arch.lief_nr =3D '$lief_nr' AND (abgeholt IS NULL OR abgeholt =3D 'f') "); my $xa =3D ''; # durchgehen der bestellungen for (my $j =3D 0; $j < $proddb->{rows}; $j++) { # setzen der spalten for (my $m =3D 0; $m <=3D 10; $m++) { $line[$m] =3D $proddb->{data}->[$j][$m]; } &debug::debug("Verarbeite Transaktionsnummer: $line[0]"); $newdbh->dbi_do("INSERT INTO t_bestell_arch=20 (transakt_nr, kd_nr_lief, name1, name2, strasse1,=20 strasse2, land_iso, plz, telefon, fax, email) VALUES ('$line[0]', '$line[1]',=20 '$line[2]', '$line[3]', '$line[4]', '$line[5]',=20 '$line[6]', '$line[7]', '$line[8]', '$line[9]', '$line[10]' )"); $proddb2->dbi_select("SELECT datum_zeit, transakt_nr,=20 art_nr_lief, prodcode, kurz_text, lang_text, vp_einh, vp_menge, vp_druck,=20 bestellmenge, preis, mwst FROM t_bestell_detail_arch WHERE t_bestell_detail_arch.lief_nr =3D '$lief_nr' AND t_bestell_detail_arch.transakt_nr =3D= =20 '$line[0]'; "); # Durchgehen der Bestellpositionen for (my $n =3D 0; $n < $proddb2->{rows}; $n++) { my ($cpreis, $cmwst); if (length $proddb2->{data}->[$n][10] eq 0) { $cpreis =3D "NULL"; } else { $cpreis =3D=20 "'".$proddb2->{data}->[$n][10]."'"; } if (length $proddb2->{data}->[$n][11] eq 0) { $cmwst =3D "NULL"; } else { $cmwst =3D "'".$proddb2->{data}->[$n][11]."= '"; } my $pinsert =3D "INSERT INTO t_bestell_detail_arch= =20 (datum_zeit, transakt_nr, art_nr_lief, prodcode,=20 kurz_text, lang_text, vp_einh, vp_menge, vp_druck,=20 bestellmenge, preis, mwst) VALUES ('$proddb2->{data}->[$n][0]',=20 '$proddb2->{data}->[$n][1]', '$proddb2->{data}->[$n][2]',=20 '$proddb2->{data}->[$n][3]', '$proddb2->{data}->[$n][4]',=20 '$proddb2->{data}->[$n][5]', '$proddb2->{data}->[$n][6]',=20 '$proddb2->{data}->[$n][7]', '$proddb2->{data}->[$n][8]',=20 '$proddb2->{data}->[$n][9]', $cpreis, $cmwst) "; $newdbh->dbi_do($pinsert); } $proddb->dbi_do("UPDATE t_bestell_arch SET abgeholt =3D 't' WHERE transakt_nr =3D '$line[0]' "); } # $newdbh->dbi_begin(); $newdbh->dbi_commit(); $newdbh->dbi_disconnect(); } $proddb->dbi_commit(); $proddb->dbi_disconnect(); $proddb2->dbi_commit(); $proddb2->dbi_disconnect(); ----------------------------------------------- Here is the interface I am using. I have been using this code for a long time and it seems to work. package database; use strict; use config; use DBI; # Cybertec Geschwinde &. Sch=F6nig OEG 2002 push @INC, "."; my $self; # Konstruktor sub new { $self =3D {}; my ($class, %args) =3D @_; my $connect =3D $args{db}; my $parameter =3D $args{parameter}; my $autocommit =3D $args{autocommit}; if (length $autocommit eq 0) { $autocommit =3D 0; } # $self->{dbh} =3D DBI->connect("dbi:Pg:$config->{DB}", # $config->{PGCONNECT}, "", {'AutoCommit' =3D> 0}) or # $self->{dbh} =3D DBI->connect("dbi:Pg:$connect", # $parameter, "", {'AutoCommit' =3D> 0}) or # &debug::dblog("cannot connect to database:=20 ".$self->{dbh}->errstr(), 1); $self->{dbh} =3D DBI->connect("dbi:Pg:$connect", $parameter, "", {'AutoCommit' =3D> $autocommit}) or &debug::dblog("cannot connect to database:=20 ".$self->{dbh}->errstr(), 1); &debug::dblog("connection ready ..."); bless($self); return $self; } # funktion zur abfrage von daten sub dbi_select { my $self =3D shift; my $sql =3D $_[0]; &debug::debug("SELECT: $sql"); my $sth =3D $self->{dbh}->prepare($sql) or &debug::dblog("cannot prepare query ($sql):=20 ".$self->{dbh}->errstr, 1); my $rv =3D $sth->execute() or &debug::dblog("cannot execute query ($sql):=20 ".$self->{dbh}->errstr, 1); my $count =3D 0; my @row; my @data; $self->{rows} =3D $sth->rows; &debug::debug("SELECT ROWS: $self->{rows} --- ".$sth->rows); my $tab =3D $sth->fetchall_arrayref; $self->{data} =3D $tab; bless($self); return $self; } sub dbi_do { my $self =3D shift; my $sql =3D shift; &debug::debug("G: $sql"); my $rv =3D $self->{dbh}->do($sql) or &debug::dblog("cannot perform SQL statement (". $sql.") - ".$self->{dbh}->errstr, "dblog.log", 1); return 0; } sub dbi_begin { my $rv =3D $self->{dbh}->begin_work() or &debug::dblog("cannot begin transaction", 1); return 0; } sub dbi_commit { &debug::dblog("trying to commit transaction"); my $rv =3D $self->{dbh}->commit() or $self->{dbh}->rollback(); # &debug::dblog("cannot commit transaction:=20 ".$self->{dbh}->errstr() , 1); &debug::dblog("commit successful"); return 0; } sub dbi_selectone { my $sql =3D $_[1]; &debug::debug("SELECTONE: $sql"); my $sth =3D $self->{dbh}->prepare($sql) or &debug::dblog("cannot compute ($sql): $!", 1); my $rv =3D $sth->execute() or &debug::dblog("cannot compute ($sql): $!", 1); my @tab =3D $sth->fetchrow_array(); return $tab[0]; } sub dbi_selectline { my $sql =3D $_[1]; &debug::debug("SELECTLINE: $sql"); my $sth =3D $self->{dbh}->prepare($sql) or &debug::dblog("cannot compute ($sql): $!", 1); my $rv =3D $sth->execute() or &debug::dblog("cannot compute ($sql): $!", 1); return $sth->fetchrow_array(); } sub dbi_selectlinehash { my $sql =3D $_[1]; &debug::debug("SELECTLINEHASH: $sql"); my $sth =3D $self->{dbh}->prepare($sql) or &debug::dblog("cannot compute ($sql): $!", 1); my $rv =3D $sth->execute() or &debug::dblog("cannot compute ($sql): $!", 1); return $sth->fetchrow_hashref(); } # ausf=FChren der query sub dbi_execute { my $self =3D shift; my $list =3D $_[0]; my $sth =3D $self->{dbh}->prepare($list) or &debug::makelog("cannot prepare query ($list): $!", 1); my $rv =3D $sth->execute() or &debug::makelog("cannot execute query ($list): $!", 1); my $count =3D 0; my @row; my @data; $self->{rows} =3D $sth->rows; &debug::debug("SELECT (rows): $self->{rows}"); my $tab =3D $sth->fetchall_arrayref; $self->{data} =3D $tab; bless($self); return $self; } # vorbereiten einer query sub dbi_prepare { $self->{dbh}->do("DEALLOCATE $_[2]"); &debug::debug("START_PREPARE: $_[1] "); my $rv =3D $self->{dbh}->do($_[1]) or &debug::makelog("cannot perform SQL statement=20 (".$_[1].")", 1); return 0; } # disconnect from database sub dbi_disconnect { my $self =3D shift; &debug::debug("Trenne Verbindung ..."); $self->{dbh}->disconnect() or &debug::dblog("cannot disconnect from database", 1); return 0; } --------------------------------- Here is the PostgreSQL log file: 2003-02-10 12:12:26 [20812] LOG: connection received: host=3D[local] 2003-02-10 12:12:26 [20812] LOG: connection authorized: user=3Dhs=20 database=3Dcontec 2003-02-10 12:12:26 [20812] LOG: query: select getdatabaseencoding() 2003-02-10 12:12:26 [20812] LOG: duration: 0.003739 sec 2003-02-10 12:12:26 [20812] LOG: query: begin 2003-02-10 12:12:26 [20812] LOG: duration: 0.000263 sec 2003-02-10 12:12:26 [20813] LOG: connection received: host=3D[local] 2003-02-10 12:12:26 [20813] LOG: connection authorized: user=3Dhs=20 database=3Dcontec 2003-02-10 12:12:26 [20813] LOG: query: select getdatabaseencoding() 2003-02-10 12:12:26 [20813] LOG: duration: 0.003197 sec 2003-02-10 12:12:26 [20813] LOG: query: begin 2003-02-10 12:12:26 [20813] LOG: duration: 0.000247 sec 2003-02-10 12:12:26 [20812] LOG: query: SET TRANSACTION ISOLATION=20 LEVEL SERIALIZABLE 2003-02-10 12:12:26 [20812] LOG: duration: 0.001518 sec 2003-02-10 12:12:26 [20813] LOG: query: SET TRANSACTION ISOLATION=20 LEVEL SERIALIZABLE 2003-02-10 12:12:26 [20813] LOG: duration: 0.000319 sec 2003-02-10 12:12:26 [20812] LOG: query: SELECT DISTINCT lief_nr FROM t_bestell_arch WHERE abgeholt IS NULL OR abgeholt =3D 'f' 2003-02-10 12:12:26 [20812] LOG: duration: 0.017677 sec 2003-02-10 12:12:26 [20814] LOG: connection received: host=3D[local] 2003-02-10 12:12:26 [20814] LOG: connection authorized: user=3Dhs=20 database=3DK4020 2003-02-10 12:12:26 [20814] LOG: query: select getdatabaseencoding() 2003-02-10 12:12:26 [20814] LOG: duration: 0.003623 sec 2003-02-10 12:12:26 [20814] LOG: query: begin 2003-02-10 12:12:26 [20814] LOG: duration: 0.000243 sec 2003-02-10 12:12:26 [20812] LOG: query: SELECT transakt_nr,=20 kd_nr_lief, name1, name2, strasse1, strasse2, land_iso, plz,=20 telefon, fax, email FROM t_bestell_arch WHERE t_bestell_arch.lief_nr =3D '4020' AND (abgeholt IS NULL OR abgeholt =3D 'f') 2003-02-10 12:12:26 [20812] LOG: duration: 0.003892 sec 2003-02-10 12:12:26 [20814] LOG: query: INSERT INTO t_bestell_arch=20 (transakt_nr, kd_nr_lief, name1, name2, strasse1,=20 strasse2, land_iso, plz, telefon, fax, email) VALUES ('195', '0', 'Cybertec',=20 'Geschwinde und Sch=F6nig', 'F=FCnfhausgasse 20/1',=20 'F=FCnfhaus', 'AT', '6020', '019136809', '', 'debug@cybertec.at' ) 2003-02-10 12:12:26 [20814] LOG: duration: 0.010127 sec 2003-02-10 12:12:26 [20813] LOG: query: SELECT datum_zeit,=20 transakt_nr, art_nr_lief, prodcode, kurz_text, lang_text, vp_einh, vp_menge, vp_druck,=20 bestellmenge, preis, mwst FROM t_bestell_detail_arch WHERE t_bestell_detail_arch.lief_nr =3D '4020' AND t_bestell_detail_arch.transakt_nr =3D= =20 '195'; 2003-02-10 12:12:26 [20813] LOG: duration: 0.045449 sec 2003-02-10 12:12:26 [20814] LOG: query: INSERT INTO=20 t_bestell_detail_arch (datum_zeit, transakt_nr, art_nr_lief, prodcode,=20 kurz_text, lang_text, vp_einh, vp_menge, vp_druck,=20 bestellmenge, preis, mwst) VALUES ('2003-02-02 14:20:46.97793', '195', '297140', '4020297140', 'DM 60 V W213 re-297140', 'DM 60=20 Verschl. W213 re Nr.361163', 'Stk.', '1', '', '1', NULL, NULL) 2003-02-10 12:12:26 [20814] LOG: query: SELECT 1 FROM ONLY=20 "public"."t_bestell_arch" x WHERE "transakt_nr" =3D $1 FOR UPDATE OF x 2003-02-10 12:12:26 [20814] LOG: duration: 0.012557 sec 2003-02-10 12:12:26 [20814] LOG: query: INSERT INTO=20 t_bestell_detail_arch (datum_zeit, transakt_nr, art_nr_lief, prodcode,=20 kurz_text, lang_text, vp_einh, vp_menge, vp_druck,=20 bestellmenge, preis, mwst) VALUES ('2003-02-02 14:20:46.97793', '195', '297139', '4020297139', 'DM 60 V W196 2flg. li-297139',=20 'DM 60 Verschl. W196 2flg. li Nr.322706', 'Stk.', '1', '', '700', NULL, NULL) 2003-02-10 12:12:26 [20814] LOG: duration: 0.001308 sec 2003-02-10 12:12:26 [20812] LOG: query: UPDATE t_bestell_arch SET=20 abgeholt =3D 't' WHERE transakt_nr =3D '195' 2003-02-10 12:12:26 [20812] LOG: duration: 0.005513 sec 2003-02-10 12:12:26 [20814] LOG: query: INSERT INTO t_bestell_arch=20 (transakt_nr, kd_nr_lief, name1, name2, strasse1,=20 strasse2, land_iso, plz, telefon, fax, email) VALUES ('196', '0', 'Cybertec',=20 'Geschwinde und Sch=F6nig', 'F=FCnfhausgasse 20/1',=20 'F=FCnfhaus', 'AT', '6020', '019136809', '', 'debug@cybertec.at' ) 2003-02-10 12:12:26 [20814] LOG: duration: 0.001105 sec 2003-02-10 12:12:26 [20813] LOG: query: SELECT datum_zeit,=20 transakt_nr, art_nr_lief, prodcode, kurz_text, lang_text, vp_einh, vp_menge, vp_druck,=20 bestellmenge, preis, mwst FROM t_bestell_detail_arch WHERE t_bestell_detail_arch.lief_nr =3D '4020' AND t_bestell_detail_arch.transakt_nr =3D= =20 '196'; 2003-02-10 12:12:26 [20813] LOG: duration: 0.001403 sec 2003-02-10 12:12:26 [20814] LOG: query: INSERT INTO=20 t_bestell_detail_arch (datum_zeit, transakt_nr, art_nr_lief, prodcode,=20 kurz_text, lang_text, vp_einh, vp_menge, vp_druck,=20 bestellmenge, preis, mwst) VALUES ('2003-02-02 20:36:35.541052', '196', '297139', '4020297139', 'DM 60 V W196 2flg. li-297139',=20 'DM 60 Verschl. W196 2flg. li Nr.322706', 'Stk.', '1', '', '400', NULL, NULL) 2003-02-10 12:12:26 [20814] LOG: duration: 0.001254 sec 2003-02-10 12:12:26 [20812] LOG: query: UPDATE t_bestell_arch SET=20 abgeholt =3D 't' WHERE transakt_nr =3D '196' 2003-02-10 12:12:26 [20812] LOG: duration: 0.001365 sec 2003-02-10 12:12:26 [20814] LOG: query: INSERT INTO t_bestell_arch=20 (transakt_nr, kd_nr_lief, name1, name2, strasse1,=20 strasse2, land_iso, plz, telefon, fax, email) VALUES ('197', '0', 'Shopping Net ',=20 'Testkunde', 'An-der-Lan-Str. 18 / 36', '',=20 'AT', '6020', '0512 260097', '051226009722',=20 'office@shoppingnet.at' ) 2003-02-10 12:12:26 [20814] LOG: duration: 0.001139 sec 2003-02-10 12:12:26 [20813] LOG: query: SELECT datum_zeit,=20 transakt_nr, art_nr_lief, prodcode, kurz_text, lang_text, vp_einh, vp_menge, vp_druck,=20 bestellmenge, preis, mwst FROM t_bestell_detail_arch WHERE t_bestell_detail_arch.lief_nr =3D '4020' AND t_bestell_detail_arch.transakt_nr =3D= =20 '197'; 2003-02-10 12:12:26 [20813] LOG: duration: 0.001490 sec 2003-02-10 12:12:26 [20814] LOG: query: INSERT INTO=20 t_bestell_detail_arch (datum_zeit, transakt_nr, art_nr_lief, prodcode,=20 kurz_text, lang_text, vp_einh, vp_menge, vp_druck,=20 bestellmenge, preis, mwst) VALUES ('2003-03-02 08:43:57.134697', '197', 'SDH661', '4020SDH661', 'DM 80 TV W196-SDH661', 'DM 80=20 T=FCrverschluss W196 Nr.375969', 'Stk.', '1', '', '1', NULL, NULL) 2003-02-10 12:12:26 [20814] LOG: duration: 0.001284 sec 2003-02-10 12:12:26 [20814] LOG: query: INSERT INTO=20 t_bestell_detail_arch (datum_zeit, transakt_nr, art_nr_lief, prodcode,=20 kurz_text, lang_text, vp_einh, vp_menge, vp_druck,=20 bestellmenge, preis, mwst) VALUES ('2003-03-02 08:43:57.134697', '197', 'SDH663', '4020SDH663', 'DM 80 TV W196TF-SDH663', 'DM 80=20 T=FCrverschluss W196TF Nr.385957', 'Stk.', '1', '', '1', NULL, NULL) 2003-02-10 12:12:26 [20814] LOG: duration: 0.001246 sec 2003-02-10 12:12:26 [20814] LOG: query: INSERT INTO=20 t_bestell_detail_arch (datum_zeit, transakt_nr, art_nr_lief, prodcode,=20 kurz_text, lang_text, vp_einh, vp_menge, vp_druck,=20 bestellmenge, preis, mwst) VALUES ('2003-03-02 08:43:57.134697', '197', 'SDH665', '4020SDH665', 'DM 80 TV W197TF-SDH665', 'DM 80=20 T=FCrverschluss W197TF Nr.393696', 'Stk.', '1', '', '1', NULL, NULL) 2003-02-10 12:12:26 [20814] LOG: duration: 0.001285 sec 2003-02-10 12:12:26 [20812] LOG: query: UPDATE t_bestell_arch SET=20 abgeholt =3D 't' WHERE transakt_nr =3D '197' 2003-02-10 12:12:26 [20812] LOG: duration: 0.001401 sec 2003-02-10 12:12:26 [20814] LOG: query: commit 2003-02-10 12:12:26 [20814] LOG: duration: 0.004005 sec 2003-02-10 12:12:26 [20814] LOG: query: begin 2003-02-10 12:12:26 [20814] LOG: duration: 0.000166 sec 2003-02-10 12:12:26 [20814] LOG: query: rollback 2003-02-10 12:12:26 [20814] LOG: duration: 0.000387 sec 2003-02-10 12:12:26 [20812] LOG: query: rollback 2003-02-10 12:12:26 [20812] LOG: duration: 0.000442 sec 2003-02-10 12:12:26 [20813] LOG: query: rollback 2003-02-10 12:12:26 [20813] LOG: duration: 0.000399 sec --=20 *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at , cluster.postgresql.at=20 , www.cybertec.at=20 , kernel.cybertec.at