|
#!/usr/bin/perl
use DBI qw(:sql_types);
$ip='129.0.19.51';
$userid='root';
$user='system';
$pass='';
$pw='123456';
$srcdb='ws1';
$destable='xf_frmwms_itemmas';
$srctable='inbound_xf_frmwms_itemmas';
#dbi:Oracle:host=129.0.19.51;sid=e31utf8a;port=1521', 'mt1utf8a', 'mt1utf8a'
$ordbh=DBI->connect('dbi:Oracle:host='.$ip.';sid=orcl;port=1521','system','123456',{RaiseError=>1,AutoCommit=>0}) || die "cannot connect oracle!:$DBI::errstr\n";
$mydbh=DBI->connect("DBI:mysql:database=$srcdb;host=127.0.0.1","$userid","$pass",{'RaiseError' => 1,'AutoCommit' => 0})or die "cannot connect mysql";
#$mydbh->do("lock tables $srctable write");
my $sth=$mydbh->prepare(qq/select * from $srctable where status_flag=0 for update/);
$sth->execute();
my $sql=qq{insert into $destable values( to_date(?,'YYYY-MM-DD HH24:MI:SS'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) };
eval{
$osth=$ordbh->prepare($sql);
while(@row=$sth->fetchrow_array){
print "$row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$row[7],$row[8],$row[9],$row[10],$row[11],$row[12],$row[13],$row[14],$row[15],$row[16],$row[17],$row[18],$row[19],$row[20],$row[21],$row[22],$row[23],$row[24],$row[25],$row[26],$row[27],$row[28],$row[29],$row[30],$row[31],$row[32],$row[33],$row[34],$row[35],$row[36]";
$osth->bind_param(1,$row[0],SQL_VARCHAR);
$osth->bind_param(2,$row[1],SQL_VARCHAR);
$osth->bind_param(3,$row[2],SQL_VARCHAR);
$osth->bind_param(4,$row[3],SQL_VARCHAR);
$osth->bind_param(5,$row[4],SQL_VARCHAR);
$osth->bind_param(6,$row[5],SQL_VARCHAR);
$osth->bind_param(7,$row[6],SQL_VARCHAR);
$osth->bind_param(8,$row[7],SQL_VARCHAR);
$osth->bind_param(9,$row[8],SQL_VARCHAR);
$osth->bind_param(10,$row[9],SQL_VARCHAR);
$osth->bind_param(11,$row[10],SQL_VARCHAR);
$osth->bind_param(12,$row[11],SQL_VARCHAR);
$osth->bind_param(13,$row[12],SQL_VARCHAR);
$osth->bind_param(14,$row[13],SQL_VARCHAR);
$osth->bind_param(15,$row[14],SQL_VARCHAR);
$osth->bind_param(16,$row[15],SQL_VARCHAR);
$osth->bind_param(17,$row[16],SQL_VARCHAR);
$osth->bind_param(18,$row[17],SQL_VARCHAR);
$osth->bind_param(19,$row[18],SQL_VARCHAR);
$osth->bind_param(20,$row[19],SQL_VARCHAR);
$osth->bind_param(21,$row[20],SQL_VARCHAR);
$osth->bind_param(22,$row[21],SQL_VARCHAR);
$osth->bind_param(23,$row[22],SQL_VARCHAR);
$osth->bind_param(24,$row[23],SQL_VARCHAR);
$osth->bind_param(25,$row[24],SQL_VARCHAR);
$osth->bind_param(26,$row[25],SQL_VARCHAR);
$osth->bind_param(27,$row[26],SQL_VARCHAR);
$osth->bind_param(28,$row[27],SQL_VARCHAR);
$osth->bind_param(29,$row[28],SQL_VARCHAR);
$osth->bind_param(30,$row[29],SQL_VARCHAR);
$osth->bind_param(31,$row[30],SQL_VARCHAR);
$osth->bind_param(32,$row[31],SQL_VARCHAR);
$osth->bind_param(33,$row[32],SQL_VARCHAR);
$osth->bind_param(34,$row[33],SQL_VARCHAR);
$osth->bind_param(35,$row[34],SQL_VARCHAR);
$osth->bind_param(36,$row[35],SQL_VARCHAR);
$osth->bind_param(37,$row[36],SQL_VARCHAR);
$osth->execute();
}
#$sth=$mydbh->prepare(qq/update inbound_xf_frmwms_itemmas set status_flag=1 where status_flag=0/);
#$sth->execute();
$mydbh->do("update inbound_xf_frmwms_itemmas set status_flag=1 where status_flag=0");
$ordbh->commit();
};
if ($@){
print "warnings:Transaction aborted:$@";
eval { $ordbh->rollback };
eval { $mydbh->rollback };
}
else {
eval { $mydbh->commit() };
}
#$mydbh->do("unlock tables");
$osth->finish();
$sth->finish();
$ordbh->disconnect();
$mydbh->disconnect();
|
