http://www.perlmonks.org?node_id=1229545

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

I'm trying to write what should be a fairly straightforward sub to read 0 to n rows from a database table and return the data as an array of hashes (sub getAllRows below). The problem is, it doesn't return any data, so I tested using sub getOneRow, which works as expected, proving the dereferencing is correct. The next test was sub getTwoRows, which returns the same row twice. What am I doing wrong?

Note Im not worried about a fix for sub getTwoRows, as its only a test routine to see what happens.

# $dbh already connected using DBI @data = &getOneRow($find); %row = $data[0]; $field1 = $row->{field1}; $field2 = $row->{field2}; $field3 = $row->{f +ield3}; print "Row id is $field1, field 2 is $field2, field 3 is $field3\n"; # Prints valid, correct values for the first matching row @data = &getTwoRows($find); %row = $data[0]; $field1 = $row->{field1}; $field2 = $row->{field2}; $field3 = $row->{f +ield3}; print "Row id is $field1, field 2 is $field2, field 3 is $field3\n"; %row = $data[1]; $field1 = $row->{field1}; $field2 = $row->{field2}; $field3 = $row->{f +ield3}; print "Row id is $field1, field 2 is $field2, field 3 is $field3\n"; # Prints valid, correct values for the first row, but the second print + line gives the first row repeated @data = &getOneRow($find); foreach %row (@data) { $field1 = $row->{field1}; $field2 = $row->{field2}; $field3 = $row +->{field3}; print "Row id is $field1, field 2 is $field2, field 3 is $field3\n +"; } # Prints no values for any of the rows ("Row id is , field 2 is , fiel +d 3 is ") sub getOneRow { my $find = shift; my ($sql, $sth); $sql = "SELECT field1, field2, field3 FROM mytable WHERE number = +$find ORDER BY field1"; $sth = $dbh->prepare($sql) or die "Couldn't prepare: " . $dbh->err +str(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); my @data = (); $rowref = $sth->fetchrow_hashref(); push @data, %{$rowref}; return @data; } sub getTwoRows { my $find = shift; my ($sql, $sth); $sql = "SELECT field1, field2, field3 FROM mytable WHERE number = +$find ORDER BY field1"; $sth = $dbh->prepare($sql) or die "Couldn't prepare: " . $dbh->err +str(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); my @data = (); $rowref = $sth->fetchrow_hashref(); push @data, %{$rowref}; $rowref = $sth->fetchrow_hashref(); push @data, %{$rowref}; return @data; } sub getAllRows { my $find = shift; my ($sql, $sth); $sql = "SELECT field1, field2, field3 FROM mytable WHERE number = +$find ORDER BY field1"; $sth = $dbh->prepare($sql) or die "Couldn't prepare: " . $dbh->err +str(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); my @data = (); while ( $rowref = $sth->fetchrow_hashref() ) {push @data, %{$rowr +ef};} return @data; }

Replies are listed 'Best First'.
Re: Array of Hashes (updated)
by haukex (Chancellor) on Feb 07, 2019 at 13:15 UTC
    while ( $rowref =  $sth->fetchrow_hashref() ) {push @data, %{$rowref};}

    Without having tested yet, I think your problem is probably here*: You dereference $rowref before pushing it into the array, and a hash used in list context will return its key/value pairs:

    my $hr1 = { foo=>'bar' }; my $hr2 = { quz=>'baz' }; my @data; push @data, %{$hr1}; push @data, %{$hr2}; use Data::Dump; dd @data; # ("foo", "bar", "quz", "baz")

    Maybe you just want to push @data, $rowref; instead?

    You might also be interested in DBI's fetchall_* methods?

    * Update: Also here:

    %row = $data[0]; ... foreach %row (@data)

    These won't do what you expect. I think you want @data to be an array of hashes, which means that the elements of @data will contain references to hashes. You can't just assign a hashref to a hash, you need to either de-reference the hashref (e.g. my %hash = %{$hashref};, which is a bit expensive as it copies everything), or work with the hashref directly, e.g. $hashref->{key}.

    I would strongly recommend having a look at perlreftut, perldsc, and perlref. Also, if you're not doing so already, Use strict and warnings!

    Newer versions of Perl have an experimental feature called refaliasing.

      Thanks - pushing the ref did what I need. I was just trying to dereference too early.

      Yes, I am using stricy and warnings, but failed to look at the error log.

Re: Array of Hashes
by hippo (Chancellor) on Feb 07, 2019 at 13:18 UTC
    so I tested using sub getOneRow, which works as expected, proving the dereferencing is correct.

    Unfortunately not. In each sub you are not pushing a hashref onto the array (as you should) but instead pushing a hash, so the array essentially becomes the hash. To fix, in your subs push the hashref on:

    push @data, $rowref;

    and in your calling routine iterate over each hashref rather than hash:

    foreach $row (@data) {

    Otherwise, please investigate placeholders now before little Bobby Tables comes to tea.

      Thanks - as in haukex's solution, pushing the ref worked.

      Placeholders aren't relevant here, because the sub will only be called once per script execution, but the single call may be from a number of different places in the main body.

Re: Array of Hashes
by 1nickt (Abbot) on Feb 07, 2019 at 15:02 UTC

    Hi, you are debugging with:

    $field1 = $row->{field1}; $field2 = $row->{field2}; $field3 = $row->{f +ield3}; print "Row id is $field1, field 2 is $field2, field 3 is $field3\n";

    You might like sprintf, you can skip all that assigning to temporary variables just to debug an interpolated string:

    print sprintf "Row id is %s, field 2 is %s, field 3 is %s\n", @{$row}{ +qw/field1 field2 field3/};

    Hope this helps!


    The way forward always starts with a minimal test.

      Why print sprintf ... instead of printf ...?

      Optimising for fewest key strokes only makes sense transmitting to Pluto or beyond

        Hi Grandfather, because I would use

        say()
        but didn't want to introduce that change in my comment.

        And because usually I am doing

        $log->debug(sprintf 'format', @values)
        or even
        $msg = sprintf 'format', @values; $DEBUG && say $msg && $log->debug($msg);

        Basically, I've never used printf alone in the last 20 years, because it does not meet my needs.


        The way forward always starts with a minimal test.