Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

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 (Monsignor) 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
[Corion]: marto: How's things? I hope the kids are fine and you too!
[Corion]: Oh yay. I wonder why a very simple change in a program doesn't elicit a diff, and now I see that my diff program seems to have a bug ;)
[1nickt]: marto thanks for asking, so far so good. A pretty modern stack and decent procedures, although rather too much home-built stuff (e.g. a logging role that should tries to duplicate Log::Any).
[Corion]: No. It's just that I'm comparing the same output file twice, instead of comparing the output files of the two runs %-)
[Corion]: Lo and behold, running a program with the correct input files yields the correct (and expected) output. Yay me.
[1nickt]: Got a MacBook and am expected to develop directly on it, ironic given the recent thread about that.
[marto]: Corion, some not too serious issues with the kids, hopefully, other than that just dealing with commuting by car again in the winter, not much fun so far, and there's no real 'bad' weather yet :)
[marto]: 1nickt, glad to hear it, I'm sure you'll bring improvements to their stack in due course ;)
[1nickt]: Just trying to adjust to a new culture. Better doc than I am used to , but quite a bit more Agiley meta-bullshit; at least half a dozen meetings a week, strict "sprint" schedule etc. I'll adjust. And I hope to just switch off after the "scrum" each day at
[1nickt]: ... the end of the afternoon. marto How are you, snowed in? Mum and Dad expecting snow today in Surrey. (Have 5" on the ground here in NJ. And my son's high school in SOCal closed until after Xmas due to fires.)

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (9)
As of 2017-12-11 11:43 GMT
Find Nodes?
    Voting Booth?
    What programming language do you hate the most?

    Results (289 votes). Check out past polls.