Implementation Of Fetchall_hashref

[Revelation]

As many may know, fetchall_hashref was never implemented for DBI, although fetchall_arrayref has been implemented. I've been focusing on strategies to create my own cookie cutter subroutine, which will let me fetch a value like so: my @recs = subroutine("with certain input that will be specified for each sub");, and to return a row of data a user would use: $recs[0 .. last row - 1]->{FIELD}; I've also decided that field should be case sensitive, and I hope that other perl monks will give sugestions to optimizing these versions, or another (faster hopefully) version (maybe implementing fetchall_arrayref, or some other optimization method that I'm not aware of?)

The 3 queries that I have written rely on two differant DBI queries: fetchrow_hashref, and bind_columns/fetchrow_arrayref. The bind_hash_query, and bind_hash_query_arraydec subroutines are very similiar, but deal with binding two differant ways (per the sugestion of perl.com, and perldoc.com). So, here's the three implementations of fetchall_hashref I have, and what exactly is needed for each to function:
Notice: The code is not all my own, in fact it comes from multiple sources, with a few spoofs and spiffs by myself to create hashes, etc.

$moo = "3";
my @recs;
Functions- 

my @fields = qw( MEH MOOSE );
bind_hash_query_arraydec: @recs = bind_hash_query_arraydec('BLEH', \@f
+ields, 'WHERE pissant = ?',$moo);

bind_hash_query: @recs = bind_hash_query('SELECT MEH, MOOSE FROM BLEH 
+WHERE pissant = ?',$moo);

doUpdate:  @recs = doQuery('SELECT MEH, MOOSE FROM BLEH WHERE pissant 
+= ?',$moo);

The queries themselves:

sub bind_hash_query_arraydec {
my $table = shift;
my $fields = shift;
my $extra = shift;
my @fields = @{$fields};
my $sql = 'SELECT ' . join(', ', @fields) . " FROM $table $extra";
my $sth = $db->prepare($sql);
$sth->execute(@_);
my %results;
my @recs;
@results{@fields} = ();
$sth->bind_columns(map { \$results{$_} } @fields);
while ($sth->fetch()) {
push @recs, { %results };
}
return @recs;
} 

sub bind_hash_query {
my $sql = shift;
my $sth = $db->prepare($sql);
$sth->execute(@_);
my %results;
my @recs;
$sth->bind_columns( \( @results{ @{$sth->{NAME} } } ));
while ($sth->fetch()) {
push @recs, { %results };
}
return @recs;
} 

sub doQuery { 

my @recs;
my $sql = shift;
my $st = $db->prepare($sql);
my $rc = $st->execute(@_);
my $rec; 

while ($rec = $st->fetchrow_hashref)
{
push @recs, $rec;
} 

$st->finish; 

return @recs;
}

If Time Bunce is around, his help would be most appreciated ;)

---

Gyan Kapur
[e-mail]

[BeernuT]

As many may know, fetchall_hashref was never implemented for DBI

I just wanted to say that I use fetchall_hashref() from DBI and I have yet to have a problem with it.


-bn

[runrig]

The old fetchall_hashref had its functionality merged into fetchall_arrayref (see the docs), since it didn't actually return a hash reference (and neither does yours), but with the right options, fetchall_arrayref will return an array of hash references as yours does.

There is a new fetchall_hashref, which you give a key field to, and it returns a reference to a hash of hash references.

------------
ooo  O\O  ooo tilly was here :,(