Code going into an infinte loop in perl

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


      }
    }    
  }

Select all Open in new window