Solved

How do I solve this perl spreadsheet problem

Posted on 2010-08-17
10
1,411 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 26

Accepted Solution

by:
wilcoxon earned 500 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 26

Assisted Solution

by:wilcoxon
wilcoxon earned 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now