Pushing Array ref to array

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

Hello Monks

I am having a brain melt at the moment and I can't seem to figure out why this isn't working.
The code is simple, it connects to a DB and queries it return the results to an array called @row. If I print this array within the loop each results is outputted as expected. However, if I tried and push each iteration of the array into another array the results are unexpected. The code is below -> be nice!


#!/usr/bin/perl
use strict;
use DBI;
use Data::Dumper;
my @result;
my @row;

my $dsn = 'DBI:Sybase:server=host.db.local';

my $dbh = DBI->connect($dsn, "sa", 'password');
die "unable to connect to server $DBI::errstr" unless $dbh;

$dbh->do("use mydatabasename");

my $query = "select c.uniq,c.ticketid,c.tickettitle,c.assignedto1,c.st
+atus,c.area,c.a_email1,
c.email
from
csCustIncident c, csIncident_lineitems l
where c.uniq = l.incident_uniq
and
c.compdate > (select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0))
and c.ticketid not in (select ticketid from email_ticket)
";

my $sth = $dbh->prepare ($query) or die "prepare failed\n";
$sth->execute( ) or die "unable to execute query $query   error $DBI::
+errstr";

while (@row = $sth->fetchrow_array())
    {
        # This line prints the list @row to screen fine
        print "@row\n";

        # But if I try and push the @row list into the @result list I 
+get strange results
        push (@result,\@row);

}
[download]

I am expecting to end up with a list called @result which will contain array references for each iteration of the @row array in the loop. That isn't happening!

I'm guessing the @result arary is being reset on each iteration but I can't figure out how to fill it and have a full list at the end?

Comment on Pushing Array ref to array Download Code

Replies are listed 'Best First'.
Re: Pushing Array ref to array by tobyink (Canon) on Feb 01, 2012 at 16:10 UTC
Ditch your `my @row` near the top of the script, and do your loop like this: `while (my @row = $sth->fetchrow_array())` [download] In your current code, the scope for `@row` is too large, so you're effectively pushing multiple references to the same array onto `@results`. As a general rule, always declare a variable as close as possible to where it's going to be used.	[reply] [d/l] [select]
Re: Pushing Array ref to array by hardburn (Abbot) on Feb 01, 2012 at 16:15 UTC
The problem is that each entry in `@result` is a reference to `@row`, but because `@row` is declared at the top of the code, you change the contents of `@row` each time through. Instead, you need to create a new variable each time through the loop. This is quite easy: `while (my @row = $sth->fetchrow_array()) { ... }` [download] And you should be able to delete the `my @row;` line at the top of the code. Here's something that may help you understand better: Lexical scoping like a fox. "There is no shame in being self-taught, only in not trying to learn in the first place." -- Atrus, Myst: The Book of D'ni.	[reply] [d/l] [select]
Re^2: Pushing Array ref to array by packetstormer (Monk) on Feb 01, 2012 at 16:42 UTC
Thanks to you both. That worked but I really should have known better! Thanks again.	[reply]
Re: Pushing Array ref to array by Anonymous Monk on Feb 02, 2012 at 02:24 UTC
Another thing to watch out for, with code like this ... another compelling reason to use tightly-scoped `my` variables ... is that you do not want references to be “left over.” Not outside the loop, after it’s over, and especially not from one loop iteration to the next.	[reply]