How is this different to
$dbh->selectall_arrayref($sql, { Slice => {} });
If it isn't, then perhaps you could offer a patch to the docs to make it
easier for people to see that selectall_arrayref can return an array of
has refs.
Tim.
On Thu, Dec 22, 2005 at 06:15:32PM -0800, Mark Hedges wrote:
>
> 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
Content-Description: selectall_arrayref_hashrefs.patch
> 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);
