Need Urgent Help - perl script informix-dbd/dbi

[maz_dcet]

Hi,

I have a Perl Script which uses Informix-DBD/DBI; but on the other system I don't have Informix-DBD/DBI installed. I want to run the script on other system. The original Perl Script was attached as "orginal.pl". I have modified the script and attached as "new.pl". The Data file /tmp/t1.dat is pipe-delimited and looks like:
ZAMMS 01-MS12|zamms01ms12|
ZAMMS 01-MS14|zamms01ms14|
ZAMMS 01-MS15|zamms01ms15|
ZAMMS 02-MS1|zamms02ms1|
ZAMMS 02-MS2|zamms02ms2|
ZAMMS 02-MS3|zamms02ms3|
ZAMMS 02-MS4|zamms02ms4|
ZAMMS 03-AR1|zamms03ar1|
ZAMMS 03-AR1 G1|zamms03ar1g1|
ZAMMS 03-AR1 G2|zamms03ar1g2|
ZAMMS 03-AR1 G3|zamms03ar1g3|
ZAMMS 03-AR1 G4|zamms03ar1g4|
ZAMMS 03-AR1 G5|zamms03ar1g5|
ZAMMS 03-AR2|zamms03ar2|
ZAMMS 03-AR2 G1|zamms03ar2g1|
ZAMMS 03-AR2 G2|zamms03ar2g2|
ZAMMS 03-AR2 G3|zamms03ar2g3|
ZAMMS 03-AR2 G4|zamms03ar2g4|
ZAMMS 03-AR3|zamms03ar3|
ZAMMS 03-AR3 G1|zamms03ar3g1|
ZAMMS 03-AR3 G2|zamms03ar3g2|
ZAMMS 03-AR3 G3|zamms03ar3g3|
ZAMMS 03-AR3 G4|zamms03ar3g4|
ZAMMS 03-AR3 G5|zamms03ar3g5|

Please let me know how can I duplicate the orignal.pl to new.pl

=============
original.pl
=============
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
# Define Variables.
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
use strict;
use CGI qw(-no_debug :standard);
use Spreadsheet::WriteExcel::Big;
use DBI;

my $reportfile = "/tmp/reportfile";
#my $reportfile = "/tmp/newloan";
#my $save_path = "/tmp/loanlevel/";
my $save_path = "/tmp/loanlevel/";
my $filenames_table = "table1";
my $TEMPLATE = "a15 a15 a2 a2 a10 a6 a6 a12 a1 a1 a18 a18 a16 a6 a11 a6 a8 a8 a11 a1 a4 a11 a1 a6 a6 a
6 a11 a11 a11 a11 a11 a8 a8 a1 a8 a11 a18 a18 a18 a12 a3 a6 a8 a11 a11";
my $TEMPLATE2 = "x15 a15";
my $returncode;
my @issue_ids;
my $currentline;
my $p_issueid;
my %row_count;
my $notfound;
my $i;
my $j;
my $k = 0;
my $m = 0;
my %workbook;
my %worksheet;
my $currentyear;
my $thismonth;
my $procyrmo;
my %nooutput;
my %filenames;
my @file;
my @headers;
my $loan_ref;
my @fields;
my $date;
my $issue;
my $oldstring;
my @temp_issues;
my @new_issues;
my @curr_issue;
my %dups;
my $dbh;
my $sth;
my $stmt;
my @data;
my $data;

#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
# Main::
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$currentyear = sprintf("%04d", (localtime)[5] + 1900);
$thismonth = sprintf("%02d", (localtime)[4] + 1);
$procyrmo = $currentyear.$thismonth;
$returncode = file_exist($reportfile);
if ($returncode) {
# put filenames for excel spreadsheets into filenames hash.
$dbh = DBI->connect("dbi:Informix:testdb");
$stmt = "SELECT issue_name, xls_name
FROM $filenames_table
WHERE public = 'Y'
ORDER BY issue_name;";
$sth = $dbh->prepare($stmt) or die "Failed to prepare '$stmt'\n";
$sth->execute() or die "Failed to execute '$stmt'\n";
while (@data = $sth->fetchrow_array) {
$_ = $data[0];
s/\s*$//;
$data[0] = $_;
$_ = $data[1];
s/\s*$//;
$data[1] = $_;
$filenames{$data[0]} = $data[1];
}
$sth->finish;

# put issues that shouldn't be processed into nooutput hash.
undef $stmt;
undef $sth;
$stmt = "SELECT issue_name
FROM $filenames_table
WHERE public = 'N'
ORDER BY issue_name;";
$sth = $dbh->prepare($stmt) or die "Failed to prepare '$stmt'\n";
$sth->execute() or die "Failed to execute '$stmt'\n";
while ($data = $sth->fetchrow_array) {
$_ = $data;
s/\s*$//;
$data = $_;
$nooutput{$_}++;
}
$sth->finish;
$dbh->disconnect;

# check the reportfile file for issues that are new (ie: aren't in
# filenames hash or the nooutput hash.
chk4new_issues();

# create the labels(column headings) array.
@headers = ( "fld1",
"fld2",
"fld3"
);
open(reportfile, $reportfile);
while () {
chomp;
$oldstring = $_;
get_deal_info($_);
$_ = $oldstring;

# process loan level data and print excel spreadsheets.
unless ($nooutput{$p_issueid}) {
($loan_ref, $p_issueid) = get_loan_info($_);
fix_deal_info($p_issueid);
@$loan_ref[16]=fix_date_info(@$loan_ref[16]);
@$loan_ref[17]=fix_date_info(@$loan_ref[17]);
@$loan_ref[21]=fix_date_info(@$loan_ref[21]);
@$loan_ref[32]=fix_date_info(@$loan_ref[32]);
@$loan_ref[34]=fix_date_info(@$loan_ref[34]);
@$loan_ref[42]=fix_date_info(@$loan_ref[42]);
unless ($workbook{$p_issueid}) {
print "Processing issue $p_issueid : $filenames{$p_issueid}\n";
$workbook{$p_issueid} = Spreadsheet::WriteExcel::Big->new("$save_path$filenames{$p_issueid}_loan_$procyrmo.xls");
$worksheet{$p_issueid} = $workbook{$p_issueid}->addworksheet();
$worksheet{$p_issueid}->write_row($row_count{$p_issueid}++, "0", \@headers);
}
$worksheet{$p_issueid}->write_row($row_count{$p_issueid}++, "0", $loan_ref);
}
}
close reportfile;
print "Closing spreadsheets...\n";
foreach $issue (keys %workbook) {
$workbook{$issue}->close();
chmod 0777, $workbook{$issue};
}
print "Report process done.\n";
}
else {
print "reportfile file was not available. Try again later.\n";
exit(0);
}

#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
# Sub-Routines begin here.
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
#---------------------------------------------------------------------
#Subroutine : file_exist
#Description: Determines if reportfile file exists.
#
#Incoming Parameters: None
#Return Parameters : $result
#
#---------------------------------------------------------------------
sub file_exist {
$SIG{CHLD} = 'IGNORE';
my $filename = shift;
my $result;

$result = sysopen(FILE, $filename, 2);
if ($result) {
close FILE;
}

return $result;
}

#---------------------------------------------------------------------
#Subroutine : get_deal_info
#Description: Gets all the issues in the Report file.
#
#Incoming Parameters: None
#Return Parameters : $p_issueid
#
#---------------------------------------------------------------------
sub get_deal_info {
$currentline = shift;
($_) = unpack($TEMPLATE2, $currentline);
chomp;
s/ GROUP.*$//i;
s/ GRP.*$//i;
s/ GP.*$//i;
s/ G.*$//i;
s/ I+\s*$//;
s/ IV\s*$//;
s/ V.*$//;
s/\/G.*$//;
s/ 1\s*$//;
s/ 2\s*$//;
s/ 3\s*$//;
s/G\d\s*$//;
s/-$//;
s/000000000000000/INDYMAC/;
s/ /TRYON/;
s/\s*$//;

$p_issueid = $_;
}

#---------------------------------------------------------------------
#Subroutine : fix_deal_info
#Description: Corrects the issue name by removing group references.
#
#Incoming Parameters: None
#Return Parameters : $p_issueid
#
#---------------------------------------------------------------------
sub fix_deal_info {
$_ = $p_issueid;

s/ GROUP.*$//i;
s/ GRP.*$//i;
s/ GP.*$//i;
s/ G.*$//i;
s/ I+\s*$//;
s/ IV\s*$//;
s/ V.*$//;
s/\/G.*$//;
s/ 1\s*$//;
s/ 2\s*$//;
s/ 3\s*$//;
s/G\d\s*$//;
s/-$//;
s/000000000000000/INDYMAC/;
s/ /TRYON/;
s/\s*$//;

$p_issueid = $_;
}

#---------------------------------------------------------------------
#Subroutine : fix_date_info
#Description: Corrects the date formats to display correctly in excel.
#
#Incoming Parameters: None
#Return Parameters : $date
#
#---------------------------------------------------------------------
sub fix_date_info {
$date = shift;
$date =~ s#(.{2})(.{2})(.{4})#$1/$2/$3#;
if ((substr $date, 0, 1) eq " ") {
$date = ""
}
return ($date);
}

#---------------------------------------------------------------------
#Subroutine : get_loan_info
#Description: Grabs the loan level info from reportfile.
#
#Incoming Parameters: None
#Return Parameters : @fields, $p_issueid
#
#---------------------------------------------------------------------
sub get_loan_info {
$currentline = shift;
@fields = unpack($TEMPLATE, $currentline);
$p_issueid = $fields[1];
return (\@fields, $p_issueid);
}


#---------------------------------------------------------------------
#Subroutine : chk4new_issues
#Description: Checks all the issues in the Report file and prints all
# new issues without filenames for verification and stops
# the program.
#
#Incoming Parameters: None
#Return Parameters : None
#
#
#---------------------------------------------------------------------
sub chk4new_issues {
open(reportfile, $reportfile);
while () {
chomp;
get_deal_info($_);
push @temp_issues, $p_issueid;
}
close reportfile;
@dups{@temp_issues} = ();
@issue_ids = keys %dups;

# if any issues from the current Report file aren't in the filenames
# hash, add to list of new issues from the Report file that don't have
# filenames associated with them. We are generating a list and stopping
# the program until the new filenames can be added.
$i = 0;
$j = 0;
undef @temp_issues;
foreach $i (0..$#issue_ids) {
@curr_issue = (keys %filenames);
push @curr_issue, (keys %nooutput);
foreach $j (0..$#curr_issue) {
if ($issue_ids[$i] eq $curr_issue[$j]) {
$notfound = 0;
last;
}
else {
$notfound = 1;
}
}
if ($notfound) {
push @temp_issues, $issue_ids[$i];
print "New issue $issue_ids[$i] found.\n";
}
}
undef %dups;
@dups{@temp_issues} = ();
@new_issues = keys %dups;

if (@new_issues) {
$i = 0;
print "New issues without filenames:\n";
foreach $i (0..$#new_issues) {
print "$new_issues[$i]\n";
}
print "Send to Admin for filename verification, add to the filenames\n";
print "text file, or the nooutput text file, and then re-run this program.\n";
exit(0);
}
}

============
new.pl
============
#!/usr/bin/perl -w

#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
# Define Variables.
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
use strict;
use Spreadsheet::WriteExcel::Big;

my $reportfile = "/tmp//reportfile";
my $save_path = "/tmp/loanlevel/";
my $filenames_table = "table1";
my $TEMPLATE = "a15 a15 a2 a2 a10 a6 a6 a12 a1 a1 a18 a18 a16 a6 a11 a6 a8 a8 a11 a1 a4 a11 a1 a6 a6 a6 a11 a11 a11 a11 a11 a8 a8 a1 a8 a11 a18 a18 a18 a12 a3 a6 a8 a11 a11";
my $TEMPLATE2 = "x15 a15";
my $returncode;
my @issue_ids;
my $currentline;
my $p_issueid;
my %row_count;
my $notfound;
my $i;
my $j;
my $k = 0;
my $m = 0;
my %workbook;
my %worksheet;
my $currentyear;
my $thismonth;
my $procyrmo;
my %nooutput;
my %filenames;
my @file;
my @headers;
my $loan_ref;
my @fields;
my $date;
my $issue;
my $oldstring;
my @temp_issues;
my @new_issues;
my @curr_issue;
my %dups;
my $dbh;
my $sth;
my $stmt;
my @data;
my $data;
my $cmd;
my $tmp_file = "/tmp/t1.dat";
my $tmp_file2 = "/tmp/t2.dat";
my $fld1;
my $fld2;


#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
# Main::
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$currentyear = sprintf("%04d", (localtime)[5] + 1900);
$thismonth = sprintf("%02d", (localtime)[4] + 1);
$procyrmo = $currentyear.$thismonth;
$returncode = file_exist($reportfile);
if ($returncode) {
# put filenames for excel spreadsheets into filenames hash.
# $dbh = DBI->connect("dbi:Informix:testdb");
# $stmt = "SELECT issue_name, xls_name
# FROM $filenames_table
# WHERE public = 'Y'
# ORDER BY issue_name;";
# $sth = $dbh->prepare($stmt) or die "Failed to prepare '$stmt'\n";
# $sth->execute() or die "Failed to execute '$stmt'\n";
# while (@data = $sth->fetchrow_array) {
$cmd = `echo "UNLOAD TO /tmp/t1.dat DELIMITER '|' SELECT issue_name, xls_name FROM dealmast_investor WHERE public='Y' ORDER BY issue_name" | dbaccess testdb`;
system($cmd);

#open(INFILE1, "< $tmp_file ") || die "can't open $tmp_file\n";
open(INFILE1, $tmp_file);

#@data = ;
#s/\s*$//;
while () {
@data = split(' ',$_);
$_ = $data;
#s/\|//;
$fld0 = $data[0];
$fld1 = $data[1];
$data[0] = $_;
$_ = $data[1];
print "$fld0 data1\n";
s/\|//;
s/\s*$//;
$data[1] = $_;
s/\s*$//;
$filenames{$data[0]} = $data[1];
$filenames = $data[0];
print " $filenames \n";
#$filenames{$fld0} = $fld1;
}
# $sth->finish;

#close INFILE1;

$cmd = `echo "UNLOAD TO /tmp/t2.dat DELIMITER ' ' SELECT issue_name FROM dealmast_investor WHERE public='N' ORDER BY issue_name" | dbaccess testdb`;
system($cmd);

#open(INFILE2, "< $tmp_file2 ") || die "can't open $tmp_file2\n";
open(INFILE2, $tmp_file2);

# put issues that shouldn't be processed into nooutput hash.
# undef $stmt;
# undef $sth;
# $stmt = "SELECT issue_name
# FROM $filenames_table
# WHERE public = 'N'
# ORDER BY issue_name;";
# $sth = $dbh->prepare($stmt) or die "Failed to prepare '$stmt'\n";
# $sth->execute() or die "Failed to execute '$stmt'\n";
# while ($data = $sth->fetchrow_array) {

while () {
@data = split(' ',$_);
$_ = $data[0];
s/\s*$//;
#$data = $_;
$nooutput{$data[0]}++;
}
# $sth->finish;
# $dbh->disconnect;

close INFILE2;
# check the reportfile file for issues that are new (ie: aren't in
# filenames hash or the nooutput hash.
chk4new_issues();

# create the labels(column headings) array.
@headers = ( "fld1",
"fld2",
"fld3"
);
open(LOANRPT, $reportfile);
while () {
chomp;
$oldstring = $_;
get_deal_info($_);
$_ = $oldstring;

# process loan level data and print excel spreadsheets.
unless ($nooutput{$p_issueid}) {
($loan_ref, $p_issueid) = get_loan_info($_);
...

[Ron Kuris]

The perl code is really difficult to read, although it looks like your 'split' statement is the culprit here. You said the file is pipe delimited but you are splitting on ' ', which splits based on white space.

You might want to try @data = split(/|/, $_) instead of @data = split(' ', $_);

It also looks partially implemented there -- the results of the substitution on $_ are discarded by commenting out the $data = $_.

However, it looks like you can avoid the split completely since you only use the first field. I think that whole block of perl code can be simplified to:

while () {
s/\s*|.*//;
$nooutput{$_}++;
}

Personally, I'd rewrite this script, but if I had to keep it similar to what you have, I'd have to use the perl debugger to make much more progress.