Re: Oracle and Two Phase commit with Perl?

On Sep 13, 11:16 am, [email protected] ("Bobak, Mark") wrote:
> Does anyone have any experience w/ doing two-phase commit across connections to two different databases from the same Perl program?  (To guarantee that either both or neither transaction is committed, for consistency.)
>
> Any thoughts, ideas or suggestions would be appreciated.
>
> Thanks,
>
> -Mark

Well, assuming you have AutoCommit turned off, why not....

$dbh1 = DBI->connect("dbi:Oracle:$db1", $user, $passwd,AutoCommit=>0);
$dbh2 = DBI->connect("dbi:Oracle:$db2", $user, $passwd,AutoCommit=>0);
$sth1 = $dbh1->prepare("INSERT INTO foo (val1,val2) VALUES (?,?)");
$sth2 = $dbh2->prepare("INSERT INTO bar (val1,val2) VALUES (?,?)");

unless ($sth1->execute("this","that") && $sth2-
>execute("this","that")) {
  $dbh1->rollback;
  $dbh2->rollback;
} else {
  $dbh1->commit;
  $dbh2->commit;
}