#!/usr/bin/perl
use strict;
unshift (@INC,"$ENV{AUTO_HOME}/bin");
require Extract_Public;
require Extract_Oracle;
require Extract_SQLServer;
require Extract_Informix;
require Extract_Mysql;
#******************************************
#功能:
# 获取抽数作业信息
#参数:
# 1.作业编号 2.数据库配置文件 3.数据库标识
#返回值:
# 作业信息
#******************************************
sub get_jobinfo
{
my ($job_id,$cfg_file,$item) = @_;
my ($host_ip,%serverinfo,$dbh,$sql,@jobinfo,@array);
my $host_ip = Extract_Public::get_ip();
%serverinfo = Extract_Public::get_inifile($cfg_file,$item);
$serverinfo{passwd} = Extract_Public::decode($serverinfo{passwd});
$dbh = Extract_Oracle::connect($serverinfo{host},$serverinfo{user},$serverinfo{passwd},$serverinfo{port},$serverinfo{dbname});
#$dbh = Extract_Oracle::rac_connect($serverinfo{user},$serverinfo{passwd},$serverinfo{tnsname});
#缓冲区大小设置为16k
$dbh->{LongReadLen}=16384;
#不截断
$dbh->{LongTruncOk}=0;
$sql = qq {SELECT SER_ID,SRC_TYPE,EXT_SQL,COL_FIELD,ROW_RECORD,IS_FIX,FILE_NAME,FILE_PATH,EXT_FLG FROM SCHEDULE.EDI_EXTRACT_JOBINFO WHERE JOB_ID ='$job_id' and EXEC_SERVER='$host_ip'};
@array = Extract_Oracle::query($dbh,$sql);
#print "@{$array[0]}";
unless (@array){
print "Cannot find job $job_id \n";
return 1;
}
return @{$array[0]};
}
#******************************************
#功能:
# 获取源系统数据库信息
#参数:
# 1.数据库别名 2.数据库配置文件 3.数据库标识
#返回值:
# 数据库信息
#******************************************
sub get_dbinfo {
my ($ser_id,$cfg_file,$item) = @_;
my (%serverinfo,$dbh,$sql,@jobinfo,@array);
%serverinfo = Extract_Public::get_inifile($cfg_file,$item);
$serverinfo{passwd} = Extract_Public::decode($serverinfo{passwd});
$dbh = Extract_Oracle::connect($serverinfo{host},$serverinfo{user},$serverinfo{passwd},$serverinfo{port},$serverinfo{dbname});
#$dbh = Extract_Oracle::rac_connect($serverinfo{user},$serverinfo{passwd},$serverinfo{tnsname});
$sql = qq {SELECT DB_ALIAS,DB_HOST ,DB_NAME ,DB_USER ,DECRYPT(DB_PWD) ,DB_PORT ,DB_TYPE FROM SCHEDULE.EDI_EXTRACT_SERINFO WHERE SER_ID ='$ser_id'};
@array = Extract_Oracle::query($dbh,$sql);
unless (@array){
print "Cannot find dbinfo $ser_id \n";
return 1;
}
return @{$array[0]};
}
#******************************************
#功能:
# 记录抽数日志
#参数:
# 1.作业编号 2.源系统类型 3.源系统别名 4.数据日期 5.抽数SQL 6.抽数日志 7.抽数状态 8.数据库配置文件 9.数据库标识
#返回值:
# 数据库信息
#******************************************
sub log_job
{
my ($job_id,$src_type,$src_alias,$date_time,$ext_sql,$err_log,$ext_status,$ext_flg,$cfg_file,$item) = @_;
my (%serverinfo,$dbh,$sql);
%serverinfo = Extract_Public::get_inifile($cfg_file,$item);
$serverinfo{passwd} = Extract_Public::decode($serverinfo{passwd});
$dbh = Extract_Oracle::connect($serverinfo{host},$serverinfo{user},$serverinfo{passwd},$serverinfo{port},$serverinfo{dbname});
#$dbh = Extract_Oracle::rac_connect($serverinfo{user},$serverinfo{passwd},$serverinfo{tnsname});
$ext_sql =~ s/\'/\'\'/g; $ext_sql = 'SQL TOO LONG TO INSERT' if ( length ($ext_sql ) > 2000 );
$err_log = 'SQL TOO LONG TO INSERT' if ( length ($err_log ) > 2000 );#lzb
$sql = qq {insert into schedule.Edi_extract_log values ('$job_id','$src_type','$src_alias','$date_time','$ext_sql','$err_log','$ext_status',sysdate,'$ext_flg')};
my $ret = Extract_Oracle::run($dbh,$sql);
return $ret;
}
#******************************************
#功能:
# 生成flg文件
#参数:
# 1.文件路径 2.文件名
#返回值:
#
#******************************************
sub wrt_flg
{
my ($file_path,$file_name,$row_record) = @_;
my ($flg_name,$size,$count,$current_time,$content);
if ($file_name =~ /(.*?)\.(txt|TXT)/){
$flg_name = $1.'.flg';
}
$size = Extract_Public::size("$file_path/$file_name");
$count= Extract_Public::count("$file_path/$file_name",$row_record);
$current_time = Extract_Public::current();
open FILE ,">$file_path/$flg_name";
$content = $file_name."\n".$size."\t byte \n".$count."\t records \n".$current_time."\n";
print FILE $content;
close FILE;
}
##add by xxx
#******************************************
#功能:
# 生成txt.ok文件
#参数:
# 1.文件路径 2.文件名
#返回值:
#
#******************************************
sub wrt_txtflg
{
my ($file_path,$file_name,$row_record) = @_;
my ($txt_name,$size,$count,$current_time,$content);
if ($file_name =~ /(.*?)\.(txt|TXT)/){
$txt_name = $1.'.txt.OK';
}
$size = Extract_Public::size("$file_path/$file_name");
$count= Extract_Public::count("$file_path/$file_name",$row_record);
$current_time = Extract_Public::current();
open FILE ,">$file_path/$txt_name";
$content = $file_name."\n".$size."\t byte \n".$count."\t records \n".$current_time."\n";
print FILE $content;
close FILE;
}
#******************************************
#功能:
# 执行卸数过程
#参数:
# 1.作业编码 2.抽数日期 3.数据库配置文件 4.数据库标识
#返回值:
#
#******************************************
sub run_ext
{
my ($job_id,$date_time,$cfg_file,$item) = @_;
my (@jobinfo,@dbinfo,@array);
print Extract_Public::current()," ]: Ready to query $job_id jobinfo.\n";
@jobinfo = get_jobinfo($job_id,$cfg_file,$item);
if ($jobinfo[0] == 1 ){
print Extract_Public::current()," ]: Error $job_id not exist.\n";
my $ret1 = log_job($job_id,"","",$date_time,"","Cannot find job $job_id",'Error',"",$cfg_file,$item);
return 1;
}
print Extract_Public::current()," ]: Query compeleted.\n";
my ($ser_id,$src_type,$ext_sql,$col_field,$row_record,$is_fix,$file_name,$file_path,$ext_flg) = @jobinfo;
#将抽数的SQL以及文件名中的时间标志替换为数据日期
$ext_sql= Extract_Public::date_repalce($ext_sql,$date_time,'FILE');
$file_name = Extract_Public::date_repalce($file_name,$date_time,'FILE');
$file_path = Extract_Public::date_repalce($file_path,$date_time,'PATH');
@dbinfo = get_dbinfo ($ser_id,$cfg_file,$item);
if ($dbinfo[0] == 1 ){
print Extract_Public::current()," ]: Cannot find dbinfo $ser_id.\n";
log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"Cannot find dbinfo $ser_id",'Error',$ext_flg,$cfg_file,$item);
return 1;
}
if ($is_fix eq 'Y'){
$col_field = '0x20';
$row_record = '0x0a';
}
# my ($db_alias,$db_host ,$db_name ,$db_user ,$db_pwd ,$db_port ,$db_type) = ($dbinfo[0],$dbinfo[1],$dbinfo[2],$dbinfo[3],$dbinfo[4] ,$dbinfo[5],$dbinfo[6]);
my ($db_alias,$db_host ,$db_name ,$db_user ,$db_pwd ,$db_port ,$db_type) = @dbinfo;
print Extract_Public::current()," ]: ==========================================\n";
print Extract_Public::current()," ]: Database type: $db_type\n";
print Extract_Public::current()," ]: Database alias: $ser_id\n";
print Extract_Public::current()," ]: File path: $file_path\n";
print Extract_Public::current()," ]: File name: $file_name.\n";
print Extract_Public::current()," ]: ==========================================\n";
`mkdir -p $file_path`;
my ($cmd,$sql_file,$sql_length); #added by lxl 20160527
$src_type =~ s/\s//g;
if ($src_type eq 'DATABASE' ){
if ($db_type eq 'ORACLE'){
#modified by lxl
$sql_length =length ($ext_sql );
if ( $sql_length > 8000 ){
$sql_file = '/tmp/'.$job_id.'_'.$date_time.'.sql';
unless (open FILE,">$sql_file" ){
print "Generate SQL file error : $!\n";
return 1;
}
print FILE $ext_sql;
close FILE;
$cmd = qq {sqluldr2 user=$db_user/$db_pwd\@$db_host:$db_port/$db_name sql=$sql_file field=$col_field record=$row_record file=$file_path/$file_name charset=ZHS16GBK };
print "$cmd\n";
}else{
$cmd = qq {sqluldr2 user=$db_user/$db_pwd\@$db_host:$db_port/$db_name query="$ext_sql" field=$col_field record=$row_record file=$file_path/$file_name charset=ZHS16GBK };
}
#modified by lxl 20160527 end
print "$cmd \n";
my $ret = `$cmd`;
if ($ret =~ m/output file/){
print Extract_Public::current()," ]: Extract success.\n";
print Extract_Public::current()," ]: Ready to write flag file.\n";
#add by bzj 20170712
print Extract_Public::current()," ]: Ready to write txt.ok file.\n";
wrt_flg("$file_path",$file_name,$row_record);
#add by bzj end
wrt_txtflg("$file_path",$file_name,$row_record);
print Extract_Public::current()," ]: Write flag file compeleted.\n";
#add by bzj 20170712
print Extract_Public::current()," ]: Write txt.ok file compeleted.\n";
my $ret1 = log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,$ret,'Success',$ext_flg,$cfg_file,$item);
#added by lxl 20160527 begin
if ( $sql_length > 8000 ){
`rm -rf $sql_file`;
}
#added by lxl 20160527 end
return $ret1 ;
}else{
print Extract_Public::current()," ]: Error found when extract:$ret\n";
my $ret1 = log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,$ret,'Error',$ext_flg,$cfg_file,$item);
return 1;
}
}elsif ($db_type eq 'SQLSERVER'){
my ($rc,$dbh,@array,$content,$num);
$rc = open (FILE,">$file_path/$file_name");
print Extract_Public::current()," ]: Ready to open $file_name.\n";
unless($rc){
print Extract_Public::current()," ]: Open file $file_path/$file_name error.\n";
log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"open file $file_path/$file_name error",'Error',$ext_flg,$cfg_file,$item);
return 1;
}
print $db_alias."\n";
print $db_user."\n";
print $db_pwd."\n";
$dbh = Extract_SQLServer::connect($db_alias,$db_user,$db_pwd);
print $db_alias."\n";
print $db_user."\n";
print $db_pwd."\n";
print "$dbh \n";
if ( $dbh == 1 ){
print Extract_Public::current()," ]: Cannot connect to SQLserver $ser_id, ensure it can be connected.\n";
log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"Cannot connect to SQLserver $ser_id",'Error',$ext_flg,$cfg_file,$item);
return 1;
}
@array = Extract_SQLServer::query($dbh,$ext_sql);
if ($array[0] == 1) {
print Extract_Public::current()," ]: Execute sql failed,check it first\n";
log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"Execute sql failed,check it first\n",'Error',$ext_flg,$cfg_file,$item);
return 1;
}
my $col_delimiter = Extract_Public::chrstr($col_field);
my $row_delimiter = Extract_Public::chrstr($row_record);
foreach(@array){
$num += 1;
my @row = @{$_};
my $row_content = join $col_delimiter ,@row;
$content .= $row_content.$row_delimiter;
if ($num == 50000){
print FILE $content;
$content='';
$num = 0;
}
}
print FILE $content;
close FILE;
print Extract_Public::current()," ]: Extract success.\n";
print Extract_Public::current()," ]: Ready to write flag file.\n";
#add by bzj 20170712
print Extract_Public::current()," ]: Ready to write txt.ok file.\n";
wrt_flg("$file_path/",$file_name,$row_delimiter);
#add by bzj 20170712
wrt_txtflg("$file_path",$file_name,$row_delimiter);
print Extract_Public::current()," ]: Write flag file compeleted.\n";
#add by bzj 20170712
print Extract_Public::current()," ]: Write txt.ok file compeleted.\n";
my $ret1 = log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"",'Success',$ext_flg,$cfg_file,$item);
return $ret1 ;
}elsif ($db_type eq 'INFORMIX'){
my ($rc,$dbh,@array,$content,$num);
$rc = open (FILE,">$file_path/$file_name");
print Extract_Public::current()," ]: Ready to open $file_name.\n";
unless($rc){
print Extract_Public::current()," ]: Open file $file_path/$file_name error.\n";
log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"open file $file_path/$file_name error",'Error',$ext_flg,$cfg_file,$item);
return 1;
}
$dbh = Extract_Informix::connect($db_alias,$db_user,$db_pwd,$db_name);
if ( $dbh == 1 ){
print Extract_Public::current()," ]: Cannot connect to SQLserver $ser_id, ensure it can be connected.\n";
log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"Cannot connect to SQLserver $ser_id",'Error',$ext_flg,$cfg_file,$item);
return 1;
}
@array = Extract_Informix::query($dbh,$ext_sql);
if ($array[0] == 1) {
print Extract_Public::current()," ]: Execute sql failed,check it first\n";
log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"Execute sql failed,check it first\n",'Error',$ext_flg,$cfg_file,$item);
return 1;
}
my $col_delimiter = Extract_Public::chrstr($col_field);
my $row_delimiter = Extract_Public::chrstr($row_record);
foreach(@array){
$num += 1;
my @row = @{$_};
my $row_content = join $col_delimiter ,@row;
$content .= $row_content.$row_delimiter;
if ($num == 50000){
print FILE $content;
$content='';
$num = 0;
}
}
print FILE $content;
close FILE;
print Extract_Public::current()," ]: Extract success.\n";
print Extract_Public::current()," ]: Ready to write flag file.\n";
#add by bzj 20170712
print Extract_Public::current()," ]: Ready to write txt.ok file.\n";
wrt_flg("$file_path/",$file_name,$row_record);
#add by bzj 20170712
wrt_txtflg("$file_path",$file_name,$row_record);
print Extract_Public::current()," ]: Write flag file compeleted.\n";
#add by bzj 20170712
print Extract_Public::current()," ]: Write txt.ok file compeleted.\n";
my $ret1 = log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"",'Success',$ext_flg,$cfg_file,$item);
return $ret1 ;
} elsif ($db_type eq 'MYSQL'){
my ($rc,$dbh,@array,$content,$num);
$rc = open (FILE,">$file_path/$file_name");
print Extract_Public::current()," ]: Ready to open $file_name.\n";
unless($rc){
print Extract_Public::current()," ]: Open file $file_path/$file_name error.\n";
log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"open file $file_path/$file_name error",'Error',$ext_flg,$cfg_file,$item);
return 1;
}
$dbh = Extract_Mysql::connect($db_host,$db_port,$db_user,$db_pwd,$db_name);
if ( $dbh == 1 ){
print Extract_Public::current()," ]: Cannot connect to MYSQL $ser_id, ensure it can be connected.\n";
log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"Cannot connect to MYSQL $ser_id",'Error',$ext_flg,$cfg_file,$item);
return 1;
}
@array = Extract_Mysql::query($dbh,$ext_sql);
if ($array[0] == 1) {
print Extract_Public::current()," ]: Execute sql failed,check it first\n";
log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"Execute sql failed,check it first\n",'Error',$ext_flg,$cfg_file,$item);
return 1;
}
my $col_delimiter = Extract_Public::chrstr($col_field);
my $row_delimiter = Extract_Public::chrstr($row_record);
foreach(@array){
$num += 1;
my @row = @{$_};
my $row_content = join $col_delimiter ,@row;
$content .= $row_content.$row_delimiter;
if ($num == 20000){
print FILE $content;
$content='';
$num = 0;
}
}
print FILE $content;
close FILE;
print Extract_Public::current()," ]: Extract success.\n";
print Extract_Public::current()," ]: Ready to write flag file.\n";
#add by bzj 20170712
print Extract_Public::current()," ]: Ready to write txt.ok file.\n";
wrt_flg("$file_path/",$file_name,$row_record);
#add by bzj 20170712
wrt_txtflg("$file_path",$file_name,$row_record);
print Extract_Public::current()," ]: Write flag file compeleted.\n";
#add by bzj 20170712
print Extract_Public::current()," ]: Write txt.ok file compeleted.\n";
my $ret1 = log_job($job_id,$src_type,$ser_id,$date_time,$ext_sql,"",'Success',$ext_flg,$cfg_file,$item);
return $ret1 ;
}
}
}
#******************************************
#功能:
# 主函数
#参数:
# 1.作业编码 2.抽数日期 3.数据库配置文件 4.数据库标识
#返回值:
#
#******************************************
sub main
{
my ($job_id,$date_time) = @ARGV;
my $ret = run_ext($job_id,$date_time,"$ENV{AUTO_HOME}/config/dbconfig.ini",'EDI');
return $ret;
}
my $ret = main ();
exit $ret;
perl和数据库结合笔记
最新推荐文章于 2024-08-23 00:23:02 发布