Oh, it's not. Duh. I didn't read through to fetchall_arrayref. Thanks. Mark
On Fri, 23 Dec 2005, Tim Bunce wrote: > Date: Fri, 23 Dec 2005 11:31:08 +0000 > From: Tim Bunce <[EMAIL PROTECTED]> > To: Mark Hedges <[EMAIL PROTECTED]> > Cc: [email protected] > Subject: Re: selectall_arrayref_hashrefs() > > 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); > >
