array references in DBI

[BrianC]

Hi,

I'm trying to figure out why

while (my @an = $sth->fetchrow_array) {
     push (@OrderedQuestions, \@an);
}

works but

while (my $ran = $sth->fetchrow_arrayref) {
    push (@OrderedQuestions, $ran);
}

doesn't.

Could anyone enlighten me?

Thanks in advance,
Brian

Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips

[Fletch]

If you read the DBI documentaion for fetchrow_arrayref it explicitly notes that it returns a reference to the same array each time it's called (I believe for efficiency reasons) and that you shouldn't try and use it after a subsequent fetch and expect the old data.

[BrianC]

Thank you. I misunderstood the significance of "subsequent fetch". Brian

[ptum]

Is there any particular reason that you're not using the selectall_arrayref method? When I want an array of references to retrieved rows, I usually do it like this (untested):

my $tableref = $dbh->selectall_arrayref($statement);
# check for $DBI::errstr or whatever
my @ordered_questions = @{$tableref} if ref($tableref} eq 'ARRAY';

Of course, maybe you have your reasons. :)

[BrianC]

I shall try!

[ioannis]

Note. The code that worked came close to not working either. Each iteration tried to save the same reference!
push @OrderedQuestions, \@an ;
It only worked because @an was declared lexical and not global:

This almost identical code will not work
with the keyword my is missing:

while ( @an = $sth->fetchrow_array) {
     push (@OrderedQuestions, \@an);
}

[BrianC]

Thank you for pointing this out. What if @an was lexical but declared before the while loop? I would have the same problem, no?

[bart]

Yes, you would be pushing the same array ref every time, and the contents would change afterwards.

Test code (no DBI involved):

my @rows;
my @row = qw(one two);
push @rows, \@row;
@row = qw(three four);

use Data::Dumper;
print Dumper \@rows;

__END__
$VAR1 = [
          [
            'three',
            'four'
          ]
        ];

As you can see, the contents of the collating array changes after you put it in there.

[BrianC]

I just read the Adv Perl Prog 1st edition and so am trying to use references more. Conceptually it seems straight-forward, but in practice can be a little tricky. So in the same vain, I'm wondering why I can't seem to do:

$temp = pop @$arrayref

?

[CountZero]

It works as expected on my computer (Activestate Perl 5.8.7)

# perl v5.8.7 [MSWin32-x86-multi-thread]
> my @array= qw/een twee drie vier/;
> my $arrayref = \@array;
> print $arrayref;
ARRAY(0x2089584)
> pop @$arrayref;
vier
> my $temp = pop @$arrayref;
> print $temp;
drie

[BrianC]

Sorry, I should have been much more specific, as it's probably also my confusion as to how DBI works. If I do something like:

my $ratest = $dbh->selectrow_arrayref($sql);
my $testorder = pop @$ratest;

I get an error: Modification of a read-only value attempted

[BrianC]

Thank you, ptum, for suggesting a better approach.

Thanks to the rest of you for the answers to my question. I looked at Programming the Perl DBI, and they do indeed point this out and suggest the following syntax:

while ($ra = $sth->fetchrow_arrayref) {
    push (@array, [@$ra]);
}

which, if I understand correctly, creates a new reference pointing to the underlying data so that when $ra changes the underlying data is still pointed to in @array.

Cheers, Brian