DBI question

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

Esteemed Monks, sorry if I missed the answer to this in an earlier post. I have a bit of code that I can't figure out why it's behaving like it is.

my @return_data_team; # This is not secure in JSON but how dojo requir
+es it.
while ( my $row = $sth->fetchrow_arrayref ) {
        
        push (@return_data_team, $row);
        print STDERR Dumper($row);
    }
    print STDERR Dumper(@return_data_team);
[download]

I see each $row as it should, but when I look at the array after the while loop, I see that each position in the array is pointing to the first position


 
VAR1 = [
          'Richard Smith - Traffic Team',
          '106990',
          '156446',
          'Traffic Team',
          'Unknown'
        ];
$VAR2 = $VAR1;
$VAR3 = $VAR1;
$VAR4 = $VAR1;
$VAR5 = $VAR1;
$VAR6 = $VAR1;
$VAR7 = $VAR1;
$VAR8 = $VAR1;
$VAR9 = $VAR1;
$VAR10 = $VAR1;
$VAR11 = $VAR1;
[download]

I did find a solution by just passing the $sth->fetchall_arrayref instead of the array. However, I still want to understand why. Thanks for your help in advance

Comment on DBI question Select or Download Code

Replies are listed 'Best First'.
Re: DBI question by Corion (Patriarch) on Mar 07, 2008 at 20:06 UTC
This behaviour is documented in the DBI documentation for fetchrow_arrayref: Note that the same array reference is returned for each fetch, so don't store the reference and then use it after a later fetch.	[reply]
Re^2: DBI question by Herkum (Parson) on Mar 07, 2008 at 21:12 UTC
To further expand upon what Corion pointed out, it is always returning the same array reference, the question is why? The point of using the fetchrow_* is to allow the programmer to reduce their memory footprint by limiting the amount data (and therefore memory) be accessed. One of the ways it does this is to return the same array reference for each 'fetch' instead of generating and returning a new array (and an associated array reference) for each row. If you did not care about memory management than you could use 'selectall_arrayref()' instead.	[reply]
Re: DBI question by punkish (Priest) on Mar 07, 2008 at 21:18 UTC
besides the excellent advice that Corion and dragonchild provided, let me comment on what you are doing... you are getting a packed suitcase (the reference to the result), unpacking it one by one and packing it once again (your while loop), only to dump it all out again. why not just do the following? `my $res = $sth->fetchall_arrayref; print Dumper $res;` [download] You can even do the following `use JSON; my $json = to_json($res);` [download] -- when small people start casting long shadows, it is time to go to bed	[reply] [d/l] [select]
Re: DBI question by dragonchild (Archbishop) on Mar 07, 2008 at 20:19 UTC
`push @return_data_team, [ @$row ];`	[reply] [d/l]