Perl Guru Forums: Perl Programming Help: Advanced: Problem with fetchrow

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: Advanced:
Problem with fetchrow_array

kwatts59
Novice

Jun 23, 2017, 1:08 PM

Post #1 of 8 (24757 views)

Problem with fetchrow_array	Can't Post

Hello all,
I am having problems with fetchrow_array.
It prints all the gene symbols and runs fine until the last record, then it fails.
Below is the code:

Code

$sql_cmd = "select gene_symbol,refseq_id from gene_symbols where gene_symbol is not null"; 
my $sqlquery = $dbh->prepare($sql_cmd) or die "Cannot prepare query \n"; 
my $rv1 = $sqlquery->execute or die "Cannot execute query $sql_cmd\n"; 
my @row = $sqlquery->fetchrow_array or die "Cannot fetch $sql_cmd\n"; 
my $i = 0; 
while (defined $row[0]) 
{ 
	$i++; 
	$gene_symbols[$i] = $row[0]; 
	print "$i\t$row[0]\n"; 
	@row = $sqlquery->fetchrow_array or die "Cannot fetch $sql_cmd\n"; 
} 
print "Loaded $i gene symbols\n"; 
$TOTAL_GENES= $i;

When it gets to the last row in the query, it just fails with "Cannot fetch select gene_symbol,refseq_id from gene_symbols where gene_symbol is not null"

It doesn't exit the loop and it doesn't get to the print command "Loaded xxx gene symbols" at the end of the while loop.

Any help would be appreciated.
Thanks in advance.
Ken

(This post was edited by kwatts59 on Jun 23, 2017, 1:10 PM)

Chris Charley
User

Jun 23, 2017, 3:55 PM

Post #2 of 8 (24750 views)

Re: [kwatts59] Problem with fetchrow_array [In reply to]	Can't Post

Your approach doesn't look right. Here is an example fetching some data from the DBI docs.

BillKSmith
Veteran

Jun 24, 2017, 6:57 AM

Post #3 of 8 (24740 views)

Re: [kwatts59] Problem with fetchrow_array [In reply to]	Can't Post

I assume that you mean that you print the last row, and then die with an error message when you try to fetch another row. This is exactly what I would expect if the 'RaiseError' attribute is set. If you really want to use this attribute, you could trap the error with an eval block. Exit the loop on this error. Re-throw the exception for any other error. (Refer eval)

UPDATE: See my new reply below.
Good Luck,
Bill

(This post was edited by BillKSmith on Jun 24, 2017, 3:16 PM)

FishMonger
Veteran / Moderator

Jun 24, 2017, 7:15 AM

Post #4 of 8 (24738 views)

Re: [kwatts59] Problem with fetchrow_array [In reply to]	Can't Post

Why are you fetching the row inside the body of the loop? Doing That is the cause of your problem.

The fetch statement (@row assignment) should be in the while loop initialization.

Why are you fetching 2 fields but only ever use the first?

You should enable RaiseError in the connection statement and not worry about using the die statement on the prepare, execute and fetch statements. If you must, you could use eval as Bill suggested to trap the errors.

Code

$gene_symbols[$i] = $row[0];

Is better written as:

Code

push @gene_symbols, $row[0];

(This post was edited by FishMonger on Jun 24, 2017, 7:18 AM)

BillKSmith
Veteran

Jun 24, 2017, 3:43 PM

Post #5 of 8 (24726 views)

Re: [kwatts59] Problem with fetchrow_array [In reply to]	Can't Post

After reading Chris and Ron's replies, I have to revise my previous reply. The error message that you posted is your own message from the die function on the fetch inside the loop. Right after you print your final gene_symbol, you do one more fetch. There is no data to fetch, so it returns undef. Your 'OR' interprets this as FALSE so it runs the die. I now believe that your code would run as intended if you remove the 'or' and 'die' from that fetch. ($row[0] would be undefined. You would continue to the top of the loop and exit)

I hope that this post explains your problem, but that you improve your code with the advice of the others.
Good Luck,
Bill

kwatts59
Novice

Jun 25, 2017, 9:46 AM

Post #6 of 8 (24716 views)

Re: [BillKSmith] Problem with fetchrow_array [In reply to]	Can't Post

Thanks for all your replies.
It turned out that Bill was correct.
I commented out the OR portion of my fetchrow_array and it worked fine.
Problem solved.
Ken

Code

@row = $sqlquery->fetchrow_array; # or die "Cannot fetch $sql_cmd\n";

FishMonger
Veteran / Moderator

Jun 25, 2017, 9:56 AM

Post #7 of 8 (24714 views)

Re: [kwatts59] Problem with fetchrow_array [In reply to]	Can't Post

Even though that fixed the problem of receiving the warning, it's not the proper solution.

BillKSmith
Veteran

Jun 25, 2017, 2:30 PM

Post #8 of 8 (24709 views)

Re: [FishMonger] Problem with fetchrow_array [In reply to]	Can't Post

Identifying a problem is only the first step in solving it. Your success demonstrates that we have identified your original problem. You put error checking on this statement in the first place because you recognized the possibility of errors. The fact that your error test produces a false alarm at end-of-data is hardly a reason to abandon all error checking.

As long as you are working on this section of code, you may as well fix the poor practices which have been indicated. Chris gave you a reference. Have you read it? FishMonger pointed out that your SQL queries a field which you ignore. and he questioned your choice of fetch routines. You should be able to rewrite your loop to eliminate the need for one fetch outside and another inside. (Consider using 'last' to exit the loop.) Part of the reason it took us so long to identify your problem is that your error message was so poor. All of your error messages probably could be improved.

Please do not consider this issue "SOLVED" just because your code 'works' in one error-free case.
Good Luck,
Bill

Search for (options)

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