perl DBI question about fetchrow_arrayref()

[lightoverhead]

if I use

while($ref=fetchrow_arrayref()){
print "@{$ref}\n";
}

it works fine (printing all the rows I wanted).

but if I use

while($ref=fetchrow_arrayref()){
push @ary,$ref;
}
foreach (@ary){
print "@{$_}\n";
}

it can only print the last record several times.

Could someone explain this to me?

I know fetchrow_arrayref() has weird behavor, but can not understand why the above two code snippets generate different results.

Thanks.

[ahmad]

Your question says "fetchrow_arrayref", but in your example you are using fetchrow_array.

Maybe that's why it doesn't work ??

Update: As per the sabari Answer which is correct, here's what you need

my $list = $db->prepare("SELECT * FROM `table`");
$list->execute();
my @array;
while ( my $ref = $list->fetchrow_arrayref() ) {
    push @array,[@$ref];
}

foreach (@array) {
    print @{ $_ },"\n";    
}

OR

my @array;
while ( my @arr = $list->fetchrow_array() ) {
    push @array,\@arr;
}

foreach (@array) {
         print @{ $_ },"\n";    
}

[sabari]

The issue is simple ,during the push your inserting the ref not the value .So the last value printed many time while printing ,since the array contains the only list of ref . Like what your doing during in the first print here also you needs to insert the value not ref .

[lightoverhead]

Thank you Sabarinathan. This thing really confuses me.

now I understand, that when I used push, the ref finally pointed to the last record, so when I deferenced it, all the values became the last record.

[lightoverhead]

Thank you ahamad for giving me a detailed solution.

This should work

[Anonymous Monk]

Your question says "fetchrow_arrayref", but in your example you are using fetchrow_array.

Did someone update the post without noting it? The post does say fetchrow_arrayref. :-(

[ikegami]

yes

[ikegami]

fetchrow_arrayref is documented to reuse the same array, so you are saving multiple references to the same array. The simple solution is

my $ary = $dbh->selectall_arrayref($sth_or_stmt);
  -or-
my $ary = $dbh->selectall_arrayref($sth_or_stmt, undef, @bind_values);

for (@$ary) {
   print "@{$_}\n";
}

[lightoverhead]

Yes ikegami. That's why I asked the question.

"using the same array", what is this same array?

By my original understanding, $ref returned by fetchrow_arrayref() is the reference points to the row returned by fetchrow_arrayref(), so when I used "push", it should be no problem.

After I got the answer from sabari, I realized, that the reference pushed into an array is the reference pointed to the current row that fetchrow_arrayref working on.So all the references is a pushed array pointed to the last record. Did I understand this right?

I don't undertand why perl DBI has created such a seemly unuseful function.

Thank you.

[ikegami]

"using the same array", what is this same array?

The one referenced by the reference returned by a call to fetchrow_array and the one reference by the reference returned by a earlier call to fetchrow_array.

I don't undertand why perl DBI has created such a seemly unuseful function.

Efficiency. It can avoid creating new arrays all the time, which in turn, allows it to use bind_col internally.

And contrary to your claims, it's not unusable.
If you want just one row at a time, then what's the problem?
If you want all rows, then why aren't you using selectall?

[runrig]

I don't undertand why perl DBI has created such a seemly unuseful function.

It's only unuseful if you misuse it. I often want to process result sets row by row, and I only care about the current row, not what the last row was. If I want all the results at once, then (as shown) you can use one of the selectall_* methods.