I find this function useful and I'm tired of retyping it on
every project.
Sometimes I need to preserve the order from an ORDER BY or GROUP
BY clause, but I want to use selectall_arrayref for convenience.
Only then, I have to know the order of the field names.
This routine returns an arrayref of each row as returned by
fetchrow_hashref(). So each item in the array is a hash.
Makes it easy to do transformations like:
my $sql = q{ SELECT * FROM sites WHERE url like '%.org' };
my $dot_orgs = $dbh->selectall_arrayref_hashrefs( $sql );
if ($want_to_download_excel) {
my $uri = generate_chart( $dot_orgs );
forward_browser_to( $uri );
}
else {
# ***
print_autotable([
map { $_->{url} = make_link( $_->{url} ) } @{$dot_orgs}
]);
}
# make_link wraps the url in an <a href...>
Well, no one's under any obligation of course, just thought I'd see
what people think.
Mark Hedges
Index: DBI.pm
===================================================================
--- DBI.pm (revision 2322)
+++ DBI.pm (working copy)
@@ -31,6 +31,7 @@
$rv = $dbh->do($statement, \%attr, @bind_values);
$ary_ref = $dbh->selectall_arrayref($statement);
+ $ary_ref = $dbh->selectall_arrayref_hashrefs($statement);
$hash_ref = $dbh->selectall_hashref($statement, $key_field);
$ary_ref = $dbh->selectcol_arrayref($statement);
@@ -405,6 +406,7 @@
selectrow_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ]
]'], O=>0x2000 },
selectrow_hashref=>{ U =>[2,0,'$statement [, \%attr [, @bind_params ]
]'], O=>0x2000 },
selectall_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ]
]'], O=>0x2000 },
+ selectall_arrayref_hashrefs=>{U =>[2,0,'$statement [, \%attr [,
@bind_params ] ]'], O=>0x2000 },
selectall_hashref=>{ U =>[3,0,'$statement, $keyfield [, \%attr [,
@bind_params ] ]'], O=>0x2000 },
selectcol_arrayref=>{U =>[2,0,'$statement [, \%attr [, @bind_params ]
]'], O=>0x2000 },
ping => { U =>[1,1], O=>0x0404 },
@@ -1594,6 +1596,33 @@
return $rows;
}
+ # select an arrayref, but each row is hashref returned by fetchrow_hashref
+ sub selectall_arrayref_hashrefs {
+ my ($dbh, $stmt, $attr, @bind) = @_;
+ my $max_rows = delete $attr->{MaxRows};
+ my $sth = (ref $stmt) ? $stmt : $dbh->prepare($stmt, $attr);
+ return unless $sth;
+ $sth->execute(@bind) || return;
+ my @rows = undef;
+ if (defined $max_rows) {
+ @rows = (undef)x$max_rows;
+ ROW:
+ for (my $i = 0; $i <= $max_rows; $i++) {
+ $rows[$i] = $sth->fetchrow_hashref();
+ }
+ $sth->finish();
+ }
+ else {
+ @rows = ( );
+ while ( my $row = $sth->fetchrow_hashref() ) {
+ push @rows, $row;
+ }
+ }
+
+ return [EMAIL PROTECTED];
+ }
+
+
sub selectall_hashref {
my ($dbh, $stmt, $key_field, $attr, @bind) = @_;
my $sth = (ref $stmt) ? $stmt : $dbh->prepare($stmt, $attr);
@@ -4014,7 +4043,26 @@
In which case the array is copied and each value decremented before
passing to C</fetchall_arrayref>.
+=item C<selectall_arrayref_hashrefs>
+ $ary_ref = $dbh->selectall_arrayref_hashref($statement);
+ $ary_ref = $dbh->selectall_arrayref_hashref($statement, \%attr);
+ $ary_ref = $dbh->selectall_arrayref_hashref($statement, \%attr, @bind_vals);
+
+This utility method does L</prepare>, L</execute> and then returns
+an arrayref of each row of data fetched, but the contents of each array
+element is the result of L</fetchrow_hashref>.
+
+The C<$statement> parameter can be a previously prepared statement handle,
+in which case the C<prepare> is skipped. This is recommended if the
+statement is going to be executed many times.
+
+L</selectall_arrayref_hashrefs>
+supports a $max_rows parameter. You can specify a value for $max_rows
+by including a 'C<MaxRows>' attribute in \%attr. In which case finish()
+is called for you.
+
+
=item C<selectall_hashref>
$hash_ref = $dbh->selectall_hashref($statement, $key_field);
