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;
}