BrianC has asked for the wisdom of the Perl Monks concerning the following question:


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); }


Could anyone enlighten me?

Thanks in advance,

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

Replies are listed 'Best First'.
Re: array references in DBI
by Fletch (Bishop) on May 13, 2006 at 02:45 UTC

    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.

      Thank you. I misunderstood the significance of "subsequent fetch". Brian
Re: array references in DBI
by ptum (Priest) on May 13, 2006 at 06:14 UTC

    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. :)

    No good deed goes unpunished. -- (attributed to) Oscar Wilde
      I shall try!
Re: array references in DBI
by ioannis (Abbot) on May 13, 2006 at 09:48 UTC
    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); }
      Thank you for pointing this out. What if @an was lexical but declared before the while loop? I would have the same problem, no?
        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.

Re: array references in DBI
by BrianC (Acolyte) on May 13, 2006 at 16:27 UTC

    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


      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


      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

        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

Re: array references in DBI
by BrianC (Acolyte) on May 16, 2006 at 17:44 UTC

    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