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 – 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; }
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: Array of Hashes (updated)
by haukex (Archbishop) on Feb 07, 2019 at 13:15 UTC | |
by Ken_M (Initiate) on Feb 07, 2019 at 14:02 UTC | |
Re: Array of Hashes
by hippo (Bishop) on Feb 07, 2019 at 13:18 UTC | |
by Ken_M (Initiate) on Feb 07, 2019 at 14:05 UTC | |
by hippo (Bishop) on Feb 07, 2019 at 14:26 UTC | |
Re: Array of Hashes
by 1nickt (Canon) on Feb 07, 2019 at 15:02 UTC | |
by GrandFather (Saint) on Feb 07, 2019 at 19:42 UTC | |
by 1nickt (Canon) on Feb 08, 2019 at 02:28 UTC |
Back to
Seekers of Perl Wisdom