?
Solved

How do I solve this perl spreadsheet problem

Posted on 2010-08-17
10
Medium Priority
?
1,476 Views
Last Modified: 2012-05-10
Hi,

For the following code which parses the attached excel, it is not being able to process Row No. 6 (The one with Srl Num 5). I want to say that as soon as it encounters "Manual Match Required" in that row in column 3, it should just skip that row. But I think here it is entering either the 1st or 3rd loops so it is trying to find an sql which is not there and hence throwing the error by firing that in the database.

Where am I going wrong?

here is my output till it encounters ACCTNG_PERIOD_ID
=================================================================
TEST PASSED.



The query is --


                  Select  E.POSTING_EVENT_TYPE_CDE, E.FUNDING_RULE_NME  from PFND.ETL_RULES E where E.POSTING_EVENT_TYPE_CDE = 'PBTRDPL'.


ACCTNG_PERIOD_ID
====================================================

Here is the error I am getting--

===================================================

Use of uninitialized value $col_fmt in system at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 228.
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 30
        main::__ANON__('Use of uninitialized value $col_fmt in system at /ms/user/s/s...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 228
DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000
 [state was 02000 now 08007]
[IBM][CLI Driver] SQL30081N  A communication error has been detected.  Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "10.191.61.243".  Communication function detecting the error: "recv".  Protocol specific error code(s): "*", "*", "0".  SQLSTATE=08001
 [err was -30081 now -99999] [state was 08007 now 40003]
[IBM][CLI Driver] CLI0108E  Communication link failure. SQLSTATE=40003 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158.
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 31
        main::__ANON__('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 30
        main::__ANON__('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at //ms/dist/perl5/PROJ/core/5.10.1-0/.exec/ia32.linux.2.6.glibc.2.3/lib/perl5/Carp.pm line 45
        Carp::confess('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 31
        main::__ANON__('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
        (in cleanup) DBD::MSDB2::st DESTROY failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000
 [state was 02000 now 08007]
==================================================



use warnings FATAL => qw(all);
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel::Big;

use strict;
use IO::Scalar;
use Data::Dumper;
use DBI;
use DBD::MSDB2;
use File::Copy qw(cp);
use Carp;



$SIG{__DIE__} = sub { confess @_ };
$SIG{__WARN__} = sub { confess @_ };

my $posting_event_type = shift;#"PBCLCASHPAY";

#shift;

my $audit_funding_rule = shift;#"PB1";

#shift;
#"PBCLCASHPAY";
#shift;

my $col_fmt = shift;#"CCY";

#shift;
#"INPUT"; 

#shift;

#"INPUT";
my $ba_nid = shift;#10180184;

#shift;
#shift; 
#11082557;
my $le_nid = shift;#20000033;

#shift;
#20000050;
#shift;
#my @c_arr = ($ba_nid, $le_nid);
#11082557;

#my $sf = shift;
my $actual_file2 = shift;#"/v/region/na/appl/phoenixfunding/etl/data/qa/TgtFiles/delta_LibraPrimeBrokerage.txt";

#shift;

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";
#shift

#my $cc1 = "BA_NID_VAL"; 
 
#shift;
#print "the ba-nid value in Verify Col script is $ba_nid\n"; 

my $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Value_Type_Query.xls");
my $sql_worksheet = $sql_workbook->worksheet("Queries");

my ( $row_min, $row_max ) = $sql_worksheet->row_range();

for my $row ( $row_min+1 .. $row_max ) {

my $cell =  $sql_worksheet->get_cell($row, 0); 
#print $cell->value . "\n";


my $srl = $sql_worksheet->get_cell($row, 0)->value;
my $cc1 = $sql_worksheet->get_cell($row, 1)->value;
my $vt1 = $sql_worksheet->get_cell($row, 2)->value;
#my $flag =  $sql_worksheet->get_cell($row, 3)->value;

 if (defined $cell and $cell->value eq "$srl") {
   my $cell1 = $sql_worksheet->get_cell($row, 1);
   if (defined $cell1 and $cell1->value eq "$cc1") {
    
   my $cell2 = $sql_worksheet->get_cell($row, 2);
    if (defined $cell2 and $cell2->value eq "$vt1" and $cell2->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 3)->value eq "D" ) {
      my $query1 = $sql_worksheet->get_cell($row, 4)->value;

      $query1 =~ s/\$le_nid/$le_nid/;
      $query1 =~ s/\$ba_nid/$ba_nid/;
      $query1 =~ s/\$posting_event_type/$posting_event_type/;
      $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;

      print "\n\nThe First Final Query is --\n\n $query1\n\n";
      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);  
        
          }
    elsif (defined $cell2 and $cell2->value eq "$vt1" and $cell2->value ne "NA" and $cell2->value ne "Manual Match Required" and $cell2->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 3)->value eq "H" ) 
         {my $query1 = $sql_worksheet->get_cell($row, 5)->value;
          print "\n\nThe Hard-coded value is --\n\n $query1\n\n";
         # system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);
          
         #########################################################################################################



    my @row;
   
    open(SF, $actual_file2) || die "Can't open $actual_file2: $!\n";
    my @data;

    while( <SF> ) {
    chomp;
    push @data, [split /\|/];

   }
   close(SF);

    push @row, $query1;    
    while (my @row) {
    foreach my $line (@data) {
     my $found = 0;
        foreach my $val (@row) {
            $found++ if grep({ $_ eq $val } @$line);

        }

        if ($found == @row)   {
            print "\n\nTEST PASSED.
                  \n\nAt the row ---\n\n
                  @$line
                  \n\nThe Value is --\n\n
                  $query1.\n\n";
        } else {
#            print "\n\nTEST FAILED .
#                  The Value is --
#                  $query1.\n\n";
        }
    }
}

         #########################################################################################################
           }

    elsif (defined $cell2  and $cell2->value eq "NA" and $sql_worksheet->get_cell($row, 3)->value eq "D" ) {
       ##############################################################################################
       my $query1 = $sql_worksheet->get_cell($row, 4)->value;

      $query1 =~ s/\$le_nid/$le_nid/;
      $query1 =~ s/\$ba_nid/$ba_nid/;
      $query1 =~ s/\$posting_event_type/$posting_event_type/;
      $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;

      print "\n\nThe Second Final Query is --\n\n $query1\n\n";
      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);

       ############################################################################################

       }
    elsif (defined $cell2 and $cell2->value eq "Manual Match Required" and  $cell2->value ne "$vt1" and $cell2->value ne "$col_fmt" and $cell2->value ne "NA" and $sql_worksheet->get_cell($row, 3)->value eq "X" and $sql_worksheet->get_cell($row, 4)->value eq "X" and  $sql_worksheet->get_cell($row, 5)->value eq "X") {
          next;
          }
    else {next;}
        }
       }
 
  
      }

Open in new window

Value-Type-Query.xls
0
Comment
Question by:sunny82
  • 8
  • 2
10 Comments
 

Author Comment

by:sunny82
ID: 33457815
This is the error again --

Use of uninitialized value $col_fmt in system at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 228.
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        main::__ANON__('Use of uninitialized value $col_fmt in system at /ms/user/s/s...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000
 [state was 02000 now 08007]
[IBM][CLI Driver] SQL30081N  A communication error has been detected.  Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "10.191.61.243".  Communication function detecting the error: "recv".  Protocol specific error code(s): "*", "*", "0".  SQLSTATE=08001
 [err was -30081 now -99999] [state was 08007 now 40003]
[IBM][CLI Driver] CLI0108E  Communication link failure. SQLSTATE=40003 at /ms/user/s/sayantag/Access_ETL_Rules1.pl .
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        main::__ANON__('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        main::__ANON__('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at //ms/dist/perl5/PROJ/core/5.10.1-0/.exec/ia32.linux.2.6.glibc.2.3/lib/perl5/Carp.pm
        Carp::confess('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        main::__ANON__('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        (in cleanup) DBD::MSDB2::st DESTROY failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000
 [state was 02000 now 08007]
[IBM][CLI Driver] SQL30081N  A communication error has been detected.  Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "10.191.61.243".  Communication function detecting the error: "recv".  Protocol specific error code(s): "*", "*", "0".  SQLSTATE=08001
 [err was -30081 now -99999] [state was 08007 now 40003]
[IBM][CLI Driver] CLI0108E  Communication link failure. SQLSTATE=40003
[IBM][CLI Driver] CLI0108E  Communication link failure. SQLSTATE=40003 at /ms/user/s/sayantag/Access_ETL_Rules1.pl .
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        main::__ANON__('DBD::MSDB2::st DESTROY failed: [IBM][CLI Driver][DB2/LINUXX86...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        eval {...} called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        main::__ANON__('DBD::MSDB2::st DESTROY failed: [IBM][CLI Driver][DB2/LINUXX86...') called at //ms/dist/perl5/PROJ/core/5.10.1-0/.exec/ia32.linux.2.6.glibc.2.3/lib/perl5/Carp.pm
        Carp::confess('DBD::MSDB2::st DESTROY failed: [IBM][CLI Driver][DB2/LINUXX86...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        main::__ANON__('DBD::MSDB2::st DESTROY failed: [IBM][CLI Driver][DB2/LINUXX86...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        eval {...} called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        (in cleanup) DBI st handle 0x8b17550 cleared whilst still active at /ms/user/s/sayantag/Access_ETL_Rules1.pl
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        main::__ANON__('DBI st handle 0x8b17550 cleared whilst still active at /ms/us...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        eval {...} called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        main::__ANON__('DBI st handle 0x8b17550 cleared whilst still active at /ms/us...') called at //ms/dist/perl5/PROJ/core/5.10.1-0/.exec/ia32.linux.2.6.glibc.2.3/lib/perl5/Carp.pm
        Carp::confess('DBI st handle 0x8b17550 cleared whilst still active at /ms/us...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        main::__ANON__('DBI st handle 0x8b17550 cleared whilst still active at /ms/us...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
        eval {...} called at /ms/user/s/sayantag/Access_ETL_Rules1.pl
0
 

Author Comment

by:sunny82
ID: 33457888
I think defining this improperly is creating the problem and the row 6 is not falling here

elsif (defined $cell2 and $cell2->value eq "Manual Match Required" and  $cell2->value ne "$vt1" and $cell2->value ne "$col_fmt" and $cell2->value ne "NA" and $sql_worksheet->get_cell($row, 3)->value eq "X" and $sql_worksheet->get_cell($row, 4)->value eq "X" and  $sql_worksheet->get_cell($row, 5)->value eq "X") {
          next;
          }


It somehow falls into the 1st if or the 2nd elsif. I tried putting all the conditions to negate the other loops but not being able to skip it.  
0
 
LVL 27

Accepted Solution

by:
wilcoxon earned 2000 total points
ID: 33458282
I think this will work.  It reformats the code so that it is easier to see what nesting level you are at, reorders the code so the next condition is first, and simplifies the next condition (I think the problem was really just $cell2->value ne "$vt1" because $vt1 will always eq $cell2->value).
use warnings FATAL => qw(all);
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel::Big;

use strict;
use IO::Scalar;
use Data::Dumper;
use DBI;
use DBD::MSDB2;
use File::Copy qw(cp);
use Carp;


$SIG{__DIE__} = sub { confess @_ };
$SIG{__WARN__} = sub { confess @_ };

my $posting_event_type = shift;#"PBCLCASHPAY";

#shift;

my $audit_funding_rule = shift;#"PB1";

#shift;
#"PBCLCASHPAY";
#shift;

my $col_fmt = shift;#"CCY";

#shift;
#"INPUT";

#shift;

#"INPUT";
my $ba_nid = shift;#10180184;

#shift;
#shift;
#11082557;
my $le_nid = shift;#20000033;

#shift;
#20000050;
#shift;
#my @c_arr = ($ba_nid, $le_nid);
#11082557;

#my $sf = shift;
my $actual_file2 = shift;#"/v/region/na/appl/phoenixfunding/etl/data/qa/TgtFiles/delta_LibraPrimeBrokerage.txt";

#shift;

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";
#shift

#my $cc1 = "BA_NID_VAL";

#shift;
#print "the ba-nid value in Verify Col script is $ba_nid\n";

my $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Value_Type_Query.xls");
my $sql_worksheet = $sql_workbook->worksheet("Queries");

my ( $row_min, $row_max ) = $sql_worksheet->row_range();

for my $row ( $row_min+1 .. $row_max ) {

    my $cell =  $sql_worksheet->get_cell($row, 0);
    #print $cell->value . "\n";

    my $srl = $sql_worksheet->get_cell($row, 0)->value;
    my $cc1 = $sql_worksheet->get_cell($row, 1)->value;
    my $vt1 = $sql_worksheet->get_cell($row, 2)->value;
    #my $flag =  $sql_worksheet->get_cell($row, 3)->value;

    if (defined $cell and $cell->value eq "$srl") {
        my $cell1 = $sql_worksheet->get_cell($row, 1);
        if (defined $cell1 and $cell1->value eq "$cc1") {

            my $cell2 = $sql_worksheet->get_cell($row, 2);

            if (defined $cell2 and $cell2->value eq "Manual Match Required" and  and $cell2->value ne "$col_fmt" and $sql_worksheet->get_cell($row, 3)->value eq "X" and $sql_worksheet->get_cell($row, 4)->value eq "X" and  $sql_worksheet->get_cell($row, 5)->value eq "X") {
                next;
            }

            elsif (defined $cell2 and $cell2->value eq "$vt1" and $cell2->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 3)->value eq "D" ) {
                my $query1 = $sql_worksheet->get_cell($row, 4)->value;

                $query1 =~ s/\$le_nid/$le_nid/;
                $query1 =~ s/\$ba_nid/$ba_nid/;
                $query1 =~ s/\$posting_event_type/$posting_event_type/;
                $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;

                print "\n\nThe First Final Query is --\n\n $query1\n\n";
                system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);

            }

            elsif (defined $cell2 and $cell2->value eq "$vt1" and $cell2->value ne "NA" and $cell2->value ne "Manual Match Required" and $cell2->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 3)->value eq "H" ) {
                my $query1 = $sql_worksheet->get_cell($row, 5)->value;
                print "\n\nThe Hard-coded value is --\n\n $query1\n\n";
                # system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);

                #########################################################################################################

                my @row;

                open(SF, $actual_file2) || die "Can't open $actual_file2: $!\n";
                my @data;

                while( <SF> ) {
                    chomp;
                    push @data, [split /\|/];
                }
                close(SF);

                push @row, $query1;
                while (my @row) {
                    foreach my $line (@data) {
                    my $found = 0;
                        foreach my $val (@row) {
                            $found++ if grep({ $_ eq $val } @$line);
                        }

                    if ($found == @row)   {
                        print "\n\nTEST PASSED.
                            \n\nAt the row ---\n\n
                            @$line
                            \n\nThe Value is --\n\n
                            $query1.\n\n";
                    } else {
#                        print "\n\nTEST FAILED .
#                          The Value is --
#                          $query1.\n\n";
                    }
                }
            }

            #########################################################################################################
        }

        elsif (defined $cell2  and $cell2->value eq "NA" and $sql_worksheet->get_cell($row, 3)->value eq "D" ) {
            ##############################################################################################
            my $query1 = $sql_worksheet->get_cell($row, 4)->value;

            $query1 =~ s/\$le_nid/$le_nid/;
            $query1 =~ s/\$ba_nid/$ba_nid/;
            $query1 =~ s/\$posting_event_type/$posting_event_type/;
            $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;

            print "\n\nThe Second Final Query is --\n\n $query1\n\n";
            system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);

            ############################################################################################

            }

            else {
                next;
            }
        }
    }

}

Open in new window

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 

Author Comment

by:sunny82
ID: 33458620
Hi,

Getting this error in the code which calls this one after I made the changes--

----------------------------------------------------------------------------------

Use of uninitialized value $col_fmt in system at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 228.
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 30
        main::__ANON__('Use of uninitialized value $col_fmt in system at /ms/user/s/s...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 228
DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000
 [state was 02000 now 08007]
[IBM][CLI Driver] SQL30081N  A communication error has been detected.  Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "10.191.61.243".  Communication function detecting the error: "recv".  Protocol specific error code(s): "*", "*", "0".  SQLSTATE=08001
 [err was -30081 now -99999] [state was 08007 now 40003]
[IBM][CLI Driver] CLI0108E  Communication link failure. SQLSTATE=40003 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158.
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 31
        main::__ANON__('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 30
        main::__ANON__('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at //ms/dist/perl5/PROJ/core/5.10.1-0/.exec/ia32.linux.2.6.glibc.2.3/lib/perl5/Carp.pm line 45
        Carp::confess('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 31
        main::__ANON__('DBD::MSDB2::st fetchrow_hashref failed: [IBM][CLI Driver][DB2...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
        (in cleanup) DBD::MSDB2::st DESTROY failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000
 [state was 02000 now 08007]
[IBM][CLI Driver] SQL30081N  A communication error has been detected.  Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "10.191.61.243".  Communication function detecting the error: "recv".  Protocol specific error code(s): "*", "*", "0".  SQLSTATE=08001
 [err was -30081 now -99999] [state was 08007 now 40003]
[IBM][CLI Driver] CLI0108E  Communication link failure. SQLSTATE=40003
[IBM][CLI Driver] CLI0108E  Communication link failure. SQLSTATE=40003 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158.
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 31
        main::__ANON__('DBD::MSDB2::st DESTROY failed: [IBM][CLI Driver][DB2/LINUXX86...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
        eval {...} called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 30
        main::__ANON__('DBD::MSDB2::st DESTROY failed: [IBM][CLI Driver][DB2/LINUXX86...') called at //ms/dist/perl5/PROJ/core/5.10.1-0/.exec/ia32.linux.2.6.glibc.2.3/lib/perl5/Carp.pm line 45
        Carp::confess('DBD::MSDB2::st DESTROY failed: [IBM][CLI Driver][DB2/LINUXX86...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 31
        main::__ANON__('DBD::MSDB2::st DESTROY failed: [IBM][CLI Driver][DB2/LINUXX86...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
        eval {...} called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
        (in cleanup) DBI st handle 0x8b17550 cleared whilst still active at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158.
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 31
        main::__ANON__('DBI st handle 0x8b17550 cleared whilst still active at /ms/us...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
        eval {...} called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 30
        main::__ANON__('DBI st handle 0x8b17550 cleared whilst still active at /ms/us...') called at //ms/dist/perl5/PROJ/core/5.10.1-0/.exec/ia32.linux.2.6.glibc.2.3/lib/perl5/Carp.pm line 45
        Carp::confess('DBI st handle 0x8b17550 cleared whilst still active at /ms/us...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 31
        main::__ANON__('DBI st handle 0x8b17550 cleared whilst still active at /ms/us...') called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
        eval {...} called at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 158
------------------------------------------------------------------------------

I am attaching the previous code also.

This is my present code, the calling code is in the code snippet--

==========================================
#!/ms/dist/perl5/bin/perl5.10 -w


use MSDW::Version
  'Spreadsheet-WriteExcel' => '2.35',
  'Spreadsheet-ParseExcel' => '0.57',
  'OLE-Storage_Lite'   => '0.19', # Used by Spreadsheet::WriteExcel
  'IO-stringy'         => '2.110',# IO::Scalar may be used by Spreadsheet::WriteExcel
  ;

use MSDW::Version ('DBD-DB2'   => '1.76-9.5.4', # or higher
                   'DBI'       => '1.609', # or higher
                   'DBD-MSDB2' => '1.6');


use warnings FATAL => qw(all);
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel::Big;

use strict;
use IO::Scalar;
use Data::Dumper;
use DBI;
use DBD::MSDB2;
use File::Copy qw(cp);
use Carp;


$SIG{__DIE__} = sub { confess @_ };
$SIG{__WARN__} = sub { confess @_ };

my $posting_event_type = shift;#"PBCLCASHPAY";

#shift;

my $audit_funding_rule = shift;#"PB1";

#shift;
#"PBCLCASHPAY";
#shift;

my $col_fmt = shift;#"CCY";
print $col_fmt;
#shift;
#"INPUT";

#shift;

#"INPUT";
my $ba_nid = shift;#10180184;

#shift;
#shift;
#11082557;
my $le_nid = shift;#20000033;

#shift;
#20000050;
#shift;
#my @c_arr = ($ba_nid, $le_nid);
#11082557;

#my $sf = shift;
my $actual_file2 = shift;#"/v/region/na/appl/phoenixfunding/etl/data/qa/TgtFiles/delta_LibraPrimeBrokerage.txt";

#shift;

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";
#shift

#my $cc1 = "BA_NID_VAL";

#shift;
#print "the ba-nid value in Verify Col script is $ba_nid\n";

my $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Value_Type_Query.xls");
my $sql_worksheet = $sql_workbook->worksheet("Queries");

my ( $row_min, $row_max ) = $sql_worksheet->row_range();

for my $row ( $row_min+1 .. $row_max ) {

    my $cell =  $sql_worksheet->get_cell($row, 0);
    #print $cell->value . "\n";

    my $srl = $sql_worksheet->get_cell($row, 0)->value;
    my $cc1 = $sql_worksheet->get_cell($row, 1)->value;
    my $vt1 = $sql_worksheet->get_cell($row, 2)->value;
    #my $flag =  $sql_worksheet->get_cell($row, 3)->value;

    if (defined $cell and $cell->value eq "$srl") {
        my $cell1 = $sql_worksheet->get_cell($row, 1);
        if (defined $cell1 and $cell1->value eq "$cc1") {

            my $cell2 = $sql_worksheet->get_cell($row, 2);

            if (defined $cell2 and $cell2->value eq "Manual Match Required" and   $cell2->value ne "$col_fmt" and $sql_worksheet->get_cell($row, 3)->value eq "X" and $sql_worksheet->get_cell($row, 4)->value eq "X" and  $sql_worksheet->get_cell($row, 5)->value eq "X") {
                next;
            }

            elsif (defined $cell2 and $cell2->value eq "$vt1" and $cell2->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 3)->value eq "D" ) {
                my $query1 = $sql_worksheet->get_cell($row, 4)->value;

                $query1 =~ s/\$le_nid/$le_nid/;
                $query1 =~ s/\$ba_nid/$ba_nid/;
                $query1 =~ s/\$posting_event_type/$posting_event_type/;
                $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;

                print "\n\nThe First Final Query is --\n\n $query1\n\n";
                system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);

            }

            elsif (defined $cell2 and $cell2->value eq "$vt1" and $cell2->value ne "NA" and $cell2->value ne "Manual Match Required" and $cell2->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 3)->value eq "H" ) {
                my $query1 = $sql_worksheet->get_cell($row, 5)->value;
                print "\n\nThe Hard-coded value is --\n\n $query1\n\n";
                # system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);

                #########################################################################################################

                my @row;

                open(SF, $actual_file2) || die "Can't open $actual_file2: $!\n";
                my @data;

                while( <SF> ) {
                    chomp;
                    push @data, [split /\|/];
                }
                close(SF);

                push @row, $query1;
                while (my @row) {
                    foreach my $line (@data) {
                    my $found = 0;
                        foreach my $val (@row) {
                            $found++ if grep({ $_ eq $val } @$line);
                        }

                    if ($found == @row)   {
                        print "\n\nTEST PASSED.
                            \n\nAt the row ---\n\n
                            @$line
                            \n\nThe Value is --\n\n
                            $query1.\n\n";
                    } else {
#                        print "\n\nTEST FAILED .
#                          The Value is --
#                          $query1.\n\n";
                    }
                }
            }

            #########################################################################################################
        }

        elsif (defined $cell2  and $cell2->value eq "NA" and $sql_worksheet->get_cell($row, 3)->value eq "D" ) {
            ##############################################################################################
            my $query1 = $sql_worksheet->get_cell($row, 4)->value;

            $query1 =~ s/\$le_nid/$le_nid/;
            $query1 =~ s/\$ba_nid/$ba_nid/;
            $query1 =~ s/\$posting_event_type/$posting_event_type/;
            $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;

            print "\n\nThe Second Final Query is --\n\n $query1\n\n";
            system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);

            ############################################################################################

            }

            else {
                next;
            }
        }
    }

}
 

===============================================================


#!/ms/dist/perl5/bin/perl5.10 -w


use MSDW::Version
  'Spreadsheet-WriteExcel' => '2.35',
  'Spreadsheet-ParseExcel' => '0.57',
  'OLE-Storage_Lite'   => '0.19', # Used by Spreadsheet::WriteExcel
  'IO-stringy'         => '2.110',# IO::Scalar may be used by Spreadsheet::WriteExcel
  ;

use MSDW::Version ('DBD-DB2'   => '1.76-9.5.4', # or higher
                   'DBI'       => '1.609', # or higher
                   'DBD-MSDB2' => '1.6');

use warnings FATAL => qw(all);
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel::Big;

use strict;
use IO::Scalar;
use Data::Dumper;
use DBI;
use DBD::MSDB2;
use File::Copy qw(cp);
use Carp;



$SIG{__DIE__} = sub { confess @_ };
$SIG{__WARN__} = sub { confess @_ };

#my @etl_cc = @ARGV;

#print "$etl_cc[1]" . "\n";

my $posting_event_type = shift;

#"PBCLCASHPAY";

#shift;

#shift;
#my $ledger_type = shift;
my $audit_funding_rule = shift;

#"PB1";

#shift;
#shift;
my $ba_nid = shift;

#10589946;
#shift;
my $le_nid = shift;

#20000050;
#shift;
#my $sf = shift; 
my $actual_file2 = shift;

#"/v/region/na/appl/phoenixfunding/etl/data/qa/TgtFiles/delta_LibraPrimeBrokerage.txt";

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt"; 
#shift;

my $workbook  = Spreadsheet::WriteExcel::Big->new("ETL_Rules_Res.xls");
my $worksheet = $workbook->addworksheet("Results");

my $format1 = $workbook->add_format();
my $format2 = $workbook->add_format();
my $format3 = $workbook->add_format(num_format => 'mm/dd/yy');


$format1->set_size(12);
$format1->set_bold();
$format1->set_color('blue');
$format1->set_align('center');
$format1->set_bg_color('yellow');



my $col=0;
my $row=0;

foreach my $i(

 "FUNDING_RULE_NME"
,"INPUT_VALUE_SIGN"
,"POSTING_EVENT_TYPE_CDE"
,"CHANGE_CATEGORY_ID_VAL"
,"AQUISITION_DTE_VAL"
,"AQUISITION_DTE_LPT_ID_VAL"
,"DUE_DTE_VAL"
,"DUE_DTE_LPT_ID_VAL"
,"CCY_VAL"
,"TRNSLTED_CCY_VAL"
,"ASSET_ID_VAL"
,"EXT_CNTRPRTY_ID_VAL"
,"BA_CNTRPRTY_ID_VAL"
,"INTERCPNY_CNTRPRTY_ID_VAL"
,"REGION_CNTRPRTY_ID_VAL"
,"CLIENT_ID_VAL"
,"UOM_CCY_VAL"
,"PRIMARY_FCP_ID"
,"MULTIPLIER_FTR"
,"LDGR_TYPE_CDE"
,"LAST_UPDT_ID"
,"LAST_UPDT_DTE_TME"
,"CLEARING_HOUSE_CDE_VAL"
,"SEC_POOL_ID_VAL"
,"TICKET_REF_NO_VAL"
,"MASTER_TICKET_REF_NO_VAL"
,"LEG_NME_VAL"
,"NETTING_GRP_ID_VAL"
,"COLLATERAL_GRP_ID_VAL"
,"PROD_GRP_VAL"
,"HEADER_ID_VAL"
,"DEBT_TENURE_NME_VAL"
,"BA_NID_VAL"
,"REGION_NID_VAL"
,"LE_NID_VAL"


)


  
{

$worksheet->write(0, $col++, $i, $format1);

}


my $dbh = DBI->connect('dbi:MSDB2:NQ230001', '', '', { 'RaiseError' => 1 }) or
          die "Can't connect to database: $DBI::errstr";


my $query = 
"SELECT * FROM PFND.ETL_RULES er where er.POSTING_EVENT_TYPE_CDE = '$posting_event_type' and er.FUNDING_RULE_NME = '$audit_funding_rule'";


my $sql = "$query";

print "\n\nThe ETL Rules Table Query is $query" . "\n";



my $sth = $dbh->prepare($sql);

$sth->execute();



$row = 1;

while (my $a = $sth->fetchrow_hashref()) {

$worksheet->write($row,0, (defined($a->{"FUNDING_RULE_NME"})? $a->{"FUNDING_RULE_NME"} : '[NULL]'),  $format2);
$worksheet->write($row,1, (defined($a->{"INPUT_VALUE_SIGN"})? $a->{"INPUT_VALUE_SIGN"} : '[NULL]'), $format2);
$worksheet->write($row,2, (defined($a->{"POSTING_EVENT_TYPE_CDE"})? $a->{"POSTING_EVENT_TYPE_CDE"} : '[NULL]'), $format2);
$worksheet->write($row,3, (defined($a->{"CHANGE_CATEGORY_ID_VAL"})? $a->{"CHANGE_CATEGORY_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,4, (defined($a->{"AQUISITION_DTE_VAL"})? $a->{"AQUISITION_DTE_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,5, (defined($a->{"AQUISITION_DTE_LPT_ID_VAL"})? $a->{"AQUISITION_DTE_LPT_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,6, (defined($a->{"DUE_DTE_VAL"})? $a->{"DUE_DTE_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,7, (defined($a->{"DUE_DTE_LPT_ID_VAL"})? $a->{"DUE_DTE_LPT_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,8, (defined($a->{"CCY_VAL"})? $a->{"CCY_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,9, (defined($a->{"TRNSLTED_CCY_VAL"})? $a->{"TRNSLTED_CCY_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,10,(defined($a->{"ASSET_ID_VAL"})? $a->{"ASSET_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,11,(defined($a->{"EXT_CNTRPRTY_ID_VAL"})? $a->{"EXT_CNTRPRTY_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,12,(defined($a->{"BA_CNTRPRTY_ID_VAL"})? $a->{"BA_CNTRPRTY_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,13,(defined($a->{"INTERCPNY_CNTRPRTY_ID_VAL"})? $a->{"INTERCPNY_CNTRPRTY_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,14,(defined($a->{"REGION_CNTRPRTY_ID_VAL"})? $a->{"REGION_CNTRPRTY_ID_VAL"} :  '[NULL]'), $format2);
$worksheet->write($row,15,(defined($a->{"CLIENT_ID_VAL"})? $a->{"CLIENT_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,16,(defined($a->{"UOM_CCY_VAL"})? $a->{"UOM_CCY_VAL"} : '[NULL]'), $format3);
$worksheet->write($row,17,(defined($a->{"PRIMARY_FCP_ID"})? $a->{"PRIMARY_FCP_ID"} : '[NULL]'), $format2);
$worksheet->write($row,18,(defined($a->{"MULTIPLIER_FTR"})? $a->{"MULTIPLIER_FTR"} : '[NULL]'), $format2);
$worksheet->write($row,19,(defined($a->{"LDGR_TYPE_CDE"})? $a->{"LDGR_TYPE_CDE"} : '[NULL]'), $format2);
$worksheet->write($row,20,(defined($a->{"LAST_UPDT_ID"})? $a->{"LAST_UPDT_ID"} : '[NULL]'), $format2);
$worksheet->write($row,21,(defined($a->{"LAST_UPDT_DTE_TME"})? $a->{"LAST_UPDT_DTE_TME"} : '[NULL]'), $format2);
$worksheet->write($row,22,(defined($a->{"CLEARING_HOUSE_CDE_VAL"})? $a->{"CLEARING_HOUSE_CDE_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,23,(defined($a->{"SEC_POOL_ID_VAL"})? $a->{"SEC_POOL_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,24,(defined($a->{"TICKET_REF_NO_VAL"})? $a->{"TICKET_REF_NO_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,25,(defined($a->{"MASTER_TICKET_REF_NO_VAL"})? $a->{"MASTER_TICKET_REF_NO_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,26,(defined($a->{"LEG_NME_VAL"})? $a->{"LEG_NME_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,27,(defined($a->{"NETTING_GRP_ID_VAL"})? $a->{"NETTING_GRP_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,28,(defined($a->{"COLLATERAL_GRP_ID_VAL"})? $a->{"COLLATERAL_GRP_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,29,(defined($a->{"PROD_GRP_VAL"})? $a->{"PROD_GRP_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,30,(defined($a->{"HEADER_ID_VAL"})? $a->{"HEADER_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,31,(defined($a->{"DEBT_TENURE_NME_VAL"})? $a->{"DEBT_TENURE_NME_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,32,(defined($a->{"BA_NID_VAL"})? $a->{"BA_NID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,33,(defined($a->{"REGION_NID_VAL"})? $a->{"REGION_NID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,34,(defined($a->{"LE_NID_VAL"})? $a->{"LE_NID_VAL"} : '[NULL]'), $format2);


############ FINDING THE COL FORMAT FROM EXCEL SHEET #######################

my $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Value_Type_Query.xls");
my $sql_worksheet = $sql_workbook->worksheet("Queries");

my ( $row_min, $row_max ) = $sql_worksheet->row_range();

for my $row ( $row_min+1 .. $row_max ) {

my $cell =  $sql_worksheet->get_cell($row, 0);

my $srl = $sql_worksheet->get_cell($row, 0)->value;
my $cc1 = $sql_worksheet->get_cell($row, 1)->value;
my $vt1 = $sql_worksheet->get_cell($row, 2)->value;

if (defined $cell and $cell->value eq "$srl") {
   my $cell1 = $sql_worksheet->get_cell($row, 1);
   
   my $fmt_val = $cell1->value;
  print $fmt_val . "\n";
   my $col_fmt = $a->{"$fmt_val"};
   
#   print "$col_fmt\n";
  


###########################################################################
#print $a->{"BA_NID_VAL"} . "\n";
#my $ba_nid_fmt = $a->{"BA_NID_VAL"};

system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Verify_Col_Value3.pl", $posting_event_type, $audit_funding_rule, $col_fmt, $ba_nid, $le_nid, $actual_file2);
  }
}

$row++;
#`rm -rf ETL_Rules_Res.xls`;
}

$sth->finish();

Open in new window

0
 

Author Comment

by:sunny82
ID: 33458667
Manual Match Required is not a column format, like the others, it is just a message inserted by me...Is that the reason, that since it is at the beginning now, it tries to match it as a $col_fmt and hence throwing the error??
0
 

Author Comment

by:sunny82
ID: 33458782
I think I figured out where the problem is..

If you see my calling code, when I am writing the worksheet, the cell1 value "ACCTNG_PERIOD_ID" is not a column in the table, hence this  hash
$a->{"ACCTNG_PERIOD_ID"} will always be null, hence $col_fmt will not be initialized for that value of ACCTNG_PERIOD_ID. For the others, it gets initialized, since they are cols in the table.

hence the error.
0
 
LVL 27

Assisted Solution

by:wilcoxon
wilcoxon earned 2000 total points
ID: 33458912
It looks to me like the issue now (and possibly before) is in the calling code at line 214-218.  You might want to change the code to something like this:

my $cell1 = $sql_worksheet->get_cell($row, 1);
die "col 1 not defined for row $row" unless defined($cell1);
my $fmt_val = $cell1->value;
print $fmt_val, "\n";
die "fmt_val not defined from col 1 and row $row" unless defined($fmt_val);
die "$fmt_val is not a valid column name from $query" unless exists($a->{$fmt_val});
my $col_fmt = $a->{$fmt_val};
0
 

Author Comment

by:sunny82
ID: 33459266
yes, that solved the problem, getting warnings like these, exactly what I wanted...Thx a lot.
0
 

Author Closing Comment

by:sunny82
ID: 33459271
Both the solutions were excellent..
0
 

Author Comment

by:sunny82
ID: 33459284
ACCTNG_PERIOD_ID is not a valid column name from SELECT * FROM PFND.ETL_RULES er where er.POSTING_EVENT_TYPE_CDE = 'PBCOMM' and er.FUNDING_RULE_NME = 'PB35' at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 226.
 at /ms/user/s/sayantag/Access_ETL_Rules1.pl line 30
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question