Array of Hashes

[Ken_M]

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 – I’m not worried about a fix for sub getTwoRows, as it’s 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;
}

[haukex]

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.

[Ken_M]

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.

[hippo]

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.

[Ken_M]

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.

[hippo]

Placeholders aren't relevant here

$find = '0; DROP TABLE mytable;';

Bobby's Mom thinks they are.

[1nickt]

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!

[GrandFather]

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

[1nickt]

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.