Code going into an infinte loop in perl

sunny82
sunny82 used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ozo
Most Valuable Expert 2014
Top Expert 2015

Commented:
"@upemplid " ne "0"
will always be true, becase "@upemplid "  contains a " " and "0" does not
ozo
Most Valuable Expert 2014
Top Expert 2015

Commented:
did you mean to say
while( @upemplid )
?

Author

Commented:
I wanted to check  if @upemplid has any value, if it does not then push a value 0 inside

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


Now checking while ("@upemplid " ne "0") execute the portion inside the Do loop. Once @upemplid = 0, the program will exit.

We can replace the "@upemplid " ne "0" with while( @upemplid ) as you said and also try to replace the do-while with just a while. We have to finally check if @upemplid does not contain any value and if it does not just exit.
 
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Author

Commented:
I mean, we can remove Line 78 and Line 123 and modify Line 121 as while (@upemplid) and 124 as i
while (!@upemplid) {exit;}. But it is still going into infinite loop. Initially @upemplid has many values one after another, but a time comes when I repeat the do block that it becomes 0, hence the code should exit

Author

Commented:
I am getting the output as this

Now processing AREA_ID 6
10040
10043
The current distinct upstream empl id is 10040
The current distinct upstream empl id is10043
10040
10079
The current distinct upstream src id is 10040
The current distinct upstream src id is 10079

and this goes on and on and on...There should be a time when ---

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

will be 0 and hence code will exit...

Author

Commented:
The SQL queries are correct as it manually gives me results so it looks a problem with the while block that it cannot find @upemplid = 0..
ozo
Most Valuable Expert 2014
Top Expert 2015

Commented:
if(grep(/ /, @upemplid) == @ upemplid )
checks that the number of @upemplid containing  
is the same as the number of @upemplid entries,
i.e. if all @upemplid entries contain  
and is equivalent to
unless( grep(!/ /, @upemplid) )
ozo
Most Valuable Expert 2014
Top Expert 2015

Commented:
when @upemplid is empty, then
grep(/ /, @upemplid)
and
 @ upemplid
will both ==0
so after
if(grep(/ /, @upemplid) == @ upemplid) { push @ upemplid, 0};
@ upemplid will never be empty

Author

Commented:
ok so, after making it -> do { } while(@upemplid) also is taking it to infinite loop. What should I do in this case?

The logic is simple --

1. There is a table A and view B.
2. select all values from view B and put into A for area id 6
3. select empl id upstream values for Empl type Contractors and flag N from A and out into an array
4.For every element of this array make empl flag as N (just ot be sure)
5.now take these empl id upstream values from A for flag N and empl type contractor, query B by making its downstream value  of B equal to this upstream value of A and insert into A
6. make all flags as Y for that empl id upstream value.
7. repeat the steps 4-6 till now rows are found which have empl type as CONTR and Flag N and exit.
ozo
Most Valuable Expert 2014
Top Expert 2015

Commented:
so after
 update AREA_INFO fsi set fsi.EMPL_FLAG = 'Y' where fsi.EMPL_ID_UP = 10040
you still get 10040 from
 select distinct fsi.EMPL_ID_UP FROM AREA_INFO fsi where fsi.EMPL_TYPE_UP = 'CONTR' and fsi.EMPL_FLAG = 'N'

do you know that the update succeeded?
ozo
Most Valuable Expert 2014
Top Expert 2015

Commented:
Is it because one is using $tr_dbh2 and one is using $sel_sth2?

Author

Commented:
actually you are correct..I know where the problem is...

first empl id upstream value is 10052, so when it is made equal to empl id downstream value from the view and inserted in the table, its flag is made Y. Now only 10079 remain whose flag is N and type CONTR. It is inserted in a similar way and made Y as flag and now the values are 10040 and 10043. Now when the downstream value is made equal to them and inserted to the table and flag is made Y, again 10079 comes. But this time its flag is equal to Y so it gets inserted again and the loop starts.

We have to do something like

foreach my $upemplid (@upemplid) {
update AREA_INFO fsi set fsi.EMPL_FLAG = 'Y' where fsi.EMPL_ID_UP = $upemplid
}

so that it covers all the empl ids which come before and do not repeat it.
Just dont know where to place it in the code.

Author

Commented:
I am trying to do something like this below, but still no luck--

=====================================
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);

}

foreach my $upemplid(@upemplid) {
    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);
   
          }

Most Valuable Expert 2014
Top Expert 2015
Commented:
not addressing the sql logic, but the perl logic of
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 /;
is very strange.
the  $tr_sql3 =~ s/\$upemplid /$upemplid /;
seems to have no effect
It would have an effect if the
my $tr_sql3 = "update AREA_INFO fsi set fsi.EMPL_FLAG = 'N' where fsi.EMPL_ID_UP = $upemplid and fsi.EMPL_TYPE_UP = 'CONTR'";
had been
my $tr_sql3 = q"update AREA_INFO fsi set fsi.EMPL_FLAG = 'N' where fsi.EMPL_ID_UP = $upemplid and fsi.EMPL_TYPE_UP = 'CONTR'";
But then, why not just use ""?

Author

Commented:
any luck with the overall code problem..and the infinite loop
 thing...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial