James's Blog

Perl: transaction across databases

How to create transaction across multiple databases in perl script.


#!/usr/local/bin/perl -w

#
# this script will test transaction rollback
#

use DBD::Sybase;

$dbh_ora = DBI->connect("dbi:Oracle:host=192.168.69.11;sid=qadtrain",
'qad',
'qad',
{AutoCommit=>0}
)|| die "Database connection not made: $DBI::errstr";

$dbh_syb = DBI->connect("dbi:Sybase:server=DEVELOP;database=CIS",
"sa",
"october"
)|| die "Database connection not made: $DBI::errstr";

$dbh_syb->{AutoCommit}=0;
$dbh_syb->{RaiseError}=1;

$dbh_ora->{AutoCommit}=0;
$dbh_syb->{RaiseError}=1;

eval{
my $v1=4;
$sth_ora=$dbh_ora->prepare("insert into peij1 values(?,?)");
$sth_ora->execute($v1,$v1);
$sth_syb=$dbh_syb->prepare("insert into peij1 values(?,?)");
$sth_syb->execute($v1,$v1);
$dbh_ora->commit;
$dbh_syb->commit;
};
if($@){
warn "Transaction aborted due to $@";
$dbh_ora->rollback;
$dbh_syb->rollback;
}

posted by James Pei @ 11:37 AM