We help IT Professionals succeed at work.
Get Started

Code going into an infinte loop in perl

sunny82
sunny82 asked
on
247 Views
Last Modified: 2012-06-21
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

Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2015
Commented:
This problem has been solved!
Unlock 1 Answer and 15 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE