CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Problem with while loop, probably simple [SOLVED]

 



mn_kthompson
Novice

Aug 13, 2007, 7:39 AM

Post #1 of 5 (520 views)
Problem with while loop, probably simple [SOLVED] Can't Post

Maybe I just need another set of eyes to look at this. I can't figure out why my script is dying. It reads a CSV file like a database, performs a query and output the result. Actually, it does a lot more than that, but I've trimmed the code down to just the relevant parts so you wont have to read through the whole thing to find the part that is giving me trouble.

Here is the code:

Code
#!/usr/bin/perl -w 

# Generate a report on the completion of the security awareness training. Input file is a csv file with a header row
# and newlines at the end of each line. The csv file must be in the local directory.

use warnings;
use strict;
use DBD::CSV;

#Create a database handler and point it to the local directory
my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;") or die;
my $sql_department_list = 'select distinct department from ' . $ARGV[0];

print "\n\nDetailed report by department and supervisor\n";
my $sth_department_list = $dbh->prepare( $sql_department_list ) or die;
$sth_department_list->execute() or die;
while (my $department_results = $sth_department_list->fetchrow_hashref) {
my $department_name = $department_results->{'department'};
print "$department_name\n";

# For each department we need a list of supervisors
my $sql_supervisor_list = 'select distinct supvlastname,supvfirstname from ' . $ARGV[0] . ' where department=\'' . $department_name . '\'';
print "$sql_supervisor_list\n";
my $sth_supervisor_list = $dbh->prepare( $sql_supervisor_list ) or die;
$sth_supervisor_list->execute() or die;
while (my $supervisor_results = $sth_supervisor_list->fetchrow_hashref or die) {
print "\t$supervisor_results->{'supvfirstname'} $supervisor_results->{'supvlastname'}\n";
}
print "For some reason you never get this far.\n";
}


Here is the output:

Code
kevin@MSU1375020:~$ ./report2.pl testing.csv  


Detailed report by department and supervisor
Academic Computer Center
select distinct supvlastname,supvfirstname from testing.csv where department='Academic Computer Center '
Wayne Sharp
Kevin Thompson
Died at ./report2.pl line 27.


It looks like the inner while loop successfully prints out all of the distinct supervisors and then dies before returning control to the outer while loop. I suppose that it is possible that there is another row being returned that has bad data, but I'm not sure how I can find that. Does anyone have any suggestions? Also, does anyone have any tips on making the error report more detailed?


(This post was edited by mn_kthompson on Aug 14, 2007, 11:05 AM)


ysawant
Novice

Aug 14, 2007, 7:05 AM

Post #2 of 5 (511 views)
Re: [mn_kthompson] Problem with while loop, probably simple [In reply to] Can't Post

add the error message along with die. For example, convert this:

Code
 my $sth_supervisor_list = $dbh->prepare( $sql_supervisor_list ) or die;

to this:

Code
 my $sth_supervisor_list = $dbh->prepare( $sql_supervisor_list ) or die "Failed to prepare query : $dbh->errstr() ";



Also, convert this:

Code
 while (my $supervisor_results = $sth_supervisor_list->fetchrow_hashref or die) {

to this:

Code
 while (my $supervisor_results = $sth_supervisor_list->fetchrow_hashref or die "Failed to execute : $sth_supervisor_list->errstr() ") {



mn_kthompson
Novice

Aug 14, 2007, 8:37 AM

Post #3 of 5 (509 views)
Re: [ysawant] Problem with while loop, probably simple [In reply to] Can't Post

Thanks. I'm getting an error string now, but I'm not sure what to do with it.

The thing that is messing me up is that the script lists every single supervisor in the department. If I add some test data with more supervisors then it will list all of them. Only after they are all listed does the script die, which makes no sense because I would think that it would return program control to the outer while loop.

Here is the error string that I get:

Quote
Failed to execute : DBI::st=HASH(0x8263470)->errstr() at ./report2.pl line 27.


Is there a way that I can catch this error condition and break the while loop? I understand that doing that isn't very elegant, but it would probably work. Otherwise, does anyone have any tips on figuring out what happens here that bombs out the script?

Googling for that exact error string returns nothing, and searching for DBI::st=HASH returns a whole lot of stuff. What does that string mean?


(This post was edited by mn_kthompson on Aug 14, 2007, 8:42 AM)


mn_kthompson
Novice

Aug 14, 2007, 11:04 AM

Post #4 of 5 (501 views)
Re: [mn_kthompson] Problem with while loop, probably simple [In reply to] Can't Post

OK, I had a chance to talk to one of my coworkers over lunch, and he found something interesting. If I change the inner while loop so that there is no die command, the program runs successfully.

Here is line 27:

Code
   while (my $supervisor_results = $sth_supervisor_list->fetchrow_hashref){ # or die "Failed to execute : $sth_supervisor_list->errstr() ") {


It seems that each time the fetchrow_hashref command runs it is successful until it has run through all the rows in the dataset. Then it runs again and fails. Because of the or statement the die command runs which is why the script bombs. The outer while works properly because I didn't put a die statement in that one. I knew this would probably be something simple
Now it runs. My question is, how can I catch an error condition in my code with it written this way?


KevinR
Veteran


Aug 14, 2007, 11:48 AM

Post #5 of 5 (499 views)
Re: [mn_kthompson] Problem with while loop, probably simple [In reply to] Can't Post

read the "error handling" section of the dbd::csv module.
-------------------------------------------------

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives