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

I'm trying to get the results of a verified SQL query returned by this function: 
sub selectAttributesForReport
{
    my ($self, $viewName) = @_;

    my $result;
    
    my $attrHash;

    ### SQL TO GET THE DATA ###
    my $dbm      = $self->getDBManager();
    my $sqlText  = $dbm->getSQL('selectAttributesForReport'); 

    my $dbh = $dbm->getDBH();
    my $sth = $dbh->prepare($sqlText);

    $self->logger_sql->info("Executing SQL: $sqlText");
    $sth->execute($viewName);
    
    
    #push the data onto our results
    while ( my $resultRowRef = $sth->fetchrow_hashref() )
     {       
         $attrHash->{OBJECT} = $resultRowRef->{'ATTRNAME'};
        $attrHash->{OBJECTATTRIB} = $resultRowRef->{'ATTRNAME2'};
        push (@$result, $attrHash);
     }


    ##There were no results.
    if(not $result)
      {
        $self->logger->error("There was no information available for t
+he view: [$viewName]");
      }

    return $result;
}

[download]
I've verified via print statements that $attrHash->{OBJECT} is getting the correct values on each iteration through the loop, but when I look at the result returned, there are the correct number of them, but they all contain the value of the last row retrieved by the SQL.

I'm sure I'm doing something dumb on the push, but I can't put my finger on it.

Replies are listed 'Best First'.
Re: Problem pushing hashref results
by davorg (Chancellor) on Oct 26, 2006 at 15:57 UTC

    Your re-using the same $attrHash variable each time, so you just overwrite the values on each iteration.

    You need to create a new instance of the variable each time round the loop.

    while (my $resultRowRef = $sth->fetchrow_hashref) {
  my $attrHash;
  $attrHash->{OBJECT} = $resultRowRef->{ATTRNAME};
  $attrHash->{OBJECTATTRIB} = $resultRowRef->{ATTRNAME2};
  push @$result, $attrHash;
}

    [download]
    --
    <http://dave.org.uk>

    "The first rule of Perl club is you do not talk about Perl club."
    -- Chip Salzenberg

[reply]
[d/l]
      I'm lazier than that, I don't like hardwired, manual copying. So I'd make this: 
      while (my $resultRowRef = $sth->fetchrow_hashref) {
  my %attrHash = %$resultRowRef;
  push @$result, \%attrHash;
}

      [download]
[reply]
[d/l]
Re: Problem pushing hashref results
by bobf (Monsignor) on Oct 26, 2006 at 16:11 UTC

    davorg is correct. You are reusing the same hash each time through the loop. Your debug statement printed the correct values in the hash each time, but what you didn't see is that the hashref itself was the same, as the following example shows. (Note: I changed the while loop to a for loop.) 

    use strict;
use warnings;

{
    print "This is the original approach, reusing \$attrHash\n";

    my $result = [];
    my $attrHash = {};

    for( 1..5 )
    {       
        print $attrHash, "\n";
        push( @$result, $attrHash );
    }
}

{
    print "This uses a new hash each time through the loop\n";

    my $result = [];

    for( 1..5 )
    {       
        my $attrHash = {};
        print $attrHash, "\n";
        push( @$result, $attrHash );
    }
}

__END__

This is the original approach, reusing $attrHash
HASH(0x2254c8)
HASH(0x2254c8)
HASH(0x2254c8)
HASH(0x2254c8)
HASH(0x2254c8)
This uses a new hash each time through the loop
HASH(0x225504)
HASH(0x225534)
HASH(0x225324)
HASH(0x2254e0)
HASH(0x183c1d0)

    [download]

    If you declare $attrHash within the loop you'll get a new reference each time.

[reply]
[d/l]
Re: Problem pushing hashref results
by caelifer (Scribe) on Oct 26, 2006 at 17:27 UTC
    You are storing values of the fetched record into the anonymous hash using existing hash reference first and then stores it into an anonymous array. So because you declare $attrHash outside of the while block, each time you assign to $attrHash you acctually override the previous assigned values. The simple fix would be to declare $attrHash localy within the while block to make sure it is a different hash, like in the following: 
    ...

    #push the data onto our results
    while ( my $resultRowRef = $sth->fetchrow_hashref() )
     {  
        my $attrHash;
        $attrHash->{OBJECT} = $resultRowRef->{'ATTRNAME'};
        $attrHash->{OBJECTATTRIB} = $resultRowRef->{'ATTRNAME2'};
        push (@$result, $attrHash);
     }

...

    [download]
    or, simply create it in place: 
    ...

    #push the data onto our results
    while ( my $resultRowRef = $sth->fetchrow_hashref() )
     {  
        push (@$result, \{
                           OBJECT       => $resultRowRef->{ATTRNAME},
                           OBJECTATTRIB => $resultRowRef->{ATTRNAME2}
                         });
     }

...

    [download]
    BR
[reply]
[d/l]
[select]
      Sorry for replying to my own post. Just wanted to point out that while I wrote it, people have already posted the solutions. (Vote for davorg).
[reply]
Re: Problem pushing hashref results
by rashley (Scribe) on Oct 26, 2006 at 16:19 UTC
    Yep, that did it.

    I knew I was doing something dumb. Rookie mistake.

[reply]

Back to Seekers of Perl Wisdom