troubleshooting Question

Code going into an infinte loop in perl

Avatar of sunny82
sunny82 asked on
Perl
15 Comments1 Solution249 ViewsLast Modified:
Hi,
I have a table AREA_SOURCE_VW  and a view AREA_INFO .

I am passing an area id to the code. For that area id, I am fetching upstream employee id and name from a view (AREA_INFO ) and inserting it into a table (AREA_SOURCE_VW ). Then that upstream employee id is selected from the table AREA_INFO which just got inserted and is then passed as  a downstream employee id to the view (AREA_SOURCE_VW )  and corresponding rows are inserted to the table AREA_INFO. In this way I can get all the employee upstream and downstream dependencies. Once rows are inserted into a table for an EMPL_ID, its flag is turned as 'Y' so that it does not get into the table again.

In this way the process finds out all the upstream dependencies (i.e. the do-while loop runs) till the query to fetch EMPL_ID_UP  below does not retrieve any rows ----

select distinct fsi.EMPL_ID_UP FROM AREA_INFO fsi where fsi.EMPL_TYPE_UP = 'CONTR' and fsi.EMPL_FLAG = 'N'

The sql queries are correct but while implementing the code below inside the while loop it is going to an infinite loop in the while block.

What is the problem here?

#!/usr/bin/perl5.10 -w

use warnings;
use Spreadsheet::WriteExcel::Big;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use strict;
use IO::Scalar;
use Data::Dumper;
use DBI;
use DBD::MSDB2;

my @area_id = 6;
my @upemplid;

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

my ( $sql_row_min, $sql_row_max ) = $sql_worksheet->row_range();



foreach my $area_id (@area_id) {

print "\nNow processing AREA_ID $area_id\n";

for my $sql_row ($sql_row_min+1 .. $sql_row_max) {
  my $status_cell = $sql_worksheet->get_cell($sql_row, 2)->value;
  next unless defined $status_cell;
  if ($status_cell eq "Y") {
    my $server = $sql_worksheet->get_cell($sql_row, 1)->value;
    next unless defined $server;
#    print "The server value is $server\n\n";
    chomp $server;
    $server =~ s/\$server/$server/;


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

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

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

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

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

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



    my $tr_sql = "insert into pfnd.AREA_INFO (AREA_ID,EMPL_ID_UP,EMPL_NME_UP,EMPL_TYPE_UP, IGNORE_UP,EMPL_FLAG) select distinct sv.AREA_ID,sv.EMPL_ID_UP,sv.EMPL_NME_UP,sv.EMPL_TYPE_UP,sv.IGNORE_UP,'N'  from PFND.AREA_SOURCE_VW sv where sv.AREA_ID = $area_id"; 

   $tr_sql =~ s/\$area_id/$area_id/;

   $tr_dbh->do($tr_sql);


   my $sel_sql = "select distinct fsi.EMPL_ID_UP FROM AREA_INFO fsi where fsi.EMPL_TYPE_UP = 'CONTR' and fsi.EMPL_FLAG = 'N'";

    

  my $sel_sth = $sel_dbh->prepare($sel_sql);
  $sel_sth->execute();

  while (my @row = $sel_sth->fetchrow_array()) {
   
    push @upemplid, @row;
  }

 if(grep(/ /, @upemplid) == @ upemplid) { push @ upemplid, 0};


do {  
  foreach my $upemplid(@upemplid) {
    print "The current distinct upstream empl id is $upemplid\n"; 

    my $tr_sql3 = "update AREA_INFO fsi set fsi.EMPL_FLAG = 'N' where fsi.EMPL_ID_UP = $upemplid and fsi.EMPL_TYPE_UP = 'CONTR'";
    $tr_sql3 =~ s/\$upemplid /$upemplid /;

    $tr_dbh3->do($tr_sql3);
   

   my $tr_sql1 = "insert into AREA_INFO (AREA_ID,EMPL_ID_UP,EMPL_NME_UP,EMPL_TYPE_UP,IGNORE_UP,EMPL_FLAG) select distinct sv.AREA_ID,sv.EMPL_ID_UP,sv.EMPL_NME_UP,sv.EMPL_TYPE_UP,sv.IGNORE_UP,'N'  from AREA_SOURCE_VW sv where sv.EMPL_ID_DOWN in (select distinct fsi.EMPL_ID_UP FROM AREA_INFO fsi where fsi.EMPL_TYPE_UP = 'CONTR' and fsi.EMPL_FLAG = 'N')"; 


   $tr_dbh1->do($tr_sql1);



    my $tr_sql2 = "update AREA_INFO fsi set fsi.EMPL_FLAG = 'Y' where fsi.EMPL_ID_UP = $upemplid ";
    $tr_sql2 =~ s/\$upemplid /$upemplid/;    
       
    $tr_dbh2->do($tr_sql2);
   
          }

   @upemplid = ();


   my $sel_sql2 = "select distinct fsi.EMPL_ID_UP FROM AREA_INFO fsi where fsi.EMPL_TYPE_UP = 'CONTR' and fsi.EMPL_FLAG = 'N'";


  my $sel_sth2 = $sel_dbh2->prepare($sel_sql2);
  $sel_sth2->execute();
 
  while (my @row = $sel_sth2->fetchrow_array()) {

    print "@row\n";
    push @upemplid, @row;
  }

             
        } while ("@upemplid " ne "0"); 
        
 if(grep(/ /, @upemplid) == @upemplid) { push @upemplid, 0};
 if ("@upemplid " eq "0") {exit;} 


      }
    }    
  }

Open in new window

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros