Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How do I solve this perl spreadsheet problem

Posted on 2010-08-17
10
1,431 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 

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 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

807 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