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

  Main Index MAIN
Search Posts SEARCH
Who's Online WHO'S
Log in LOG

Home: Perl Programming Help: Advanced:
Problem with fetchrow_array



Jun 23, 2017, 1:08 PM

Post #1 of 8 (31071 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:

$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])
$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";

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.

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

Chris Charley

Jun 23, 2017, 3:55 PM

Post #2 of 8 (31064 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.


Jun 24, 2017, 6:57 AM

Post #3 of 8 (31054 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,

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

Veteran / Moderator

Jun 24, 2017, 7:15 AM

Post #4 of 8 (31052 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.

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

Is better written as:

push @gene_symbols, $row[0];

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


Jun 24, 2017, 3:43 PM

Post #5 of 8 (31040 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,


Jun 25, 2017, 9:46 AM

Post #6 of 8 (31030 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.

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

Veteran / Moderator

Jun 25, 2017, 9:56 AM

Post #7 of 8 (31028 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.


Jun 25, 2017, 2:30 PM

Post #8 of 8 (31023 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,


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

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