This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.
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?
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;}
}
}
}
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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
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
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...
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...
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..
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) )
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) )
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
grep(/ /, @upemplid)
and
@ upemplid
will both ==0
so after
if(grep(/ /, @upemplid) == @ upemplid) { push @ upemplid, 0};
@ upemplid will never be empty
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.
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.
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?
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?
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.
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.
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_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);
}
==========================
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_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);
}
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 ""?
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 ""?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial