Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

array references in DBI

by BrianC (Acolyte)
on May 13, 2006 at 02:36 UTC ( #549158=perlquestion: print w/replies, xml ) Need Help??
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 (Chancellor) 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 (Prior) 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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://549158]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (4)
As of 2017-04-23 17:03 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (431 votes). Check out past polls.