Convenience function selectall_hasharrayref

--DKU6Jbt7q3WqK7+M
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

or selectall_arrayhashref?=20

Anyway, I rather frequently find that the "most natural" way to
represent a query result is an array of hashes: Each row is hashref, but
the the rows are in an array(ref) so that the order is preserved, and
the columns can be accessed by name.

Proposed use:

my $emp =3D $dbh->selectall_hasharrayref("select * from emp order by ename"=
);

for (@$emp) {
print "$_->{ename} ($_->{empno}): $_->{job}\n";
}

or

for ($first .. $last) {
print $emp->[$_]{ename}, "\n";
}

or something like that.

What do you think?

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--DKU6Jbt7q3WqK7+M
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iQDQAwUBQ8Oe6lLjemazOuKpAQGSxgXUCAdUwtZc2T7nrfviD2I9ccGw/7uJ vH1p
tMS6yYWZz1k+whD+T1GalqRZgKxWM/CO8peXk9jsvYhJWG5pl6tLhhVZIH1V Zowv
KrceEYJS1FYaVMfrVYFpGo0qb6nuYl1xoXDGmqHu2iHpNFqoU7t5FvJefdRC PAnU
0qondP3rWkmZhlu28GIeb6FUnYSfqr+yMOmn3LB7N///7PgtwRlYx4MExe4W iMHL
akTDtoo8ytRnV1l16RUbvWbbAg==
=1Z74
-----END PGP SIGNATURE-----

--DKU6Jbt7q3WqK7+M--

Re: Convenience function selectall_hasharrayref

"Peter J. Holzer" wrote

>or selectall_arrayhashref?

>Anyway, I rather frequently find that the "most natural" way to
>represent a query result is an array of hashes: Each row is hashref, but
>the the rows are in an array(ref) so that the order is preserved, and
>the columns can be accessed by name.

>Proposed use:

>my $emp = $dbh->selectall_hasharrayref("select * from emp order by ename");

>for (@$emp) {
> print "$_->{ename} ($_->{empno}): $_->{job}\n";
>}

>or

>for ($first .. $last) {
> print $emp->[$_]{ename}, "\n";
>}

>or something like that.

>What do you think?"

Would be sweet!! I have had the same idea myself as there are times when I
could really use such a function.
Would you like to work on it I have a few hours a day to commit to it?

Re: Convenience function selectall_hasharrayref

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Anyway, I rather frequently find that the "most natural" way to
> represent a query result is an array of hashes: Each row is hashref, but
> the the rows are in an array(ref) so that the order is preserved, and
> the columns can be accessed by name.

It's a little hidden, but we already have this:

$sth = $dbh-prepare($SQL);
$sth->execute(@vals);
$rows = $sth->fetchall_arrayref({});

for (@$rows) {
print "I am row $_->{id} with a value of $_->{fnord}\n";
}

- --
Greg Sabino Mullane greg@endpoint.com greg@turnstep.com
PGP Key: 0x14964AC8 200601100745
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFDw6zbvJuQZxSWSsgRAnu5AJ9Y6oKiBtS4CzHyY1pQDKtedoG4sQCf cGcD
9jZF9frISXkjo4xqGps+DaE=
=l+IS
-----END PGP SIGNATURE-----

Re: Convenience function selectall_hasharrayref

Peter J. Holzer wrote:

> or selectall_arrayhashref?
>
> Anyway, I rather frequently find that the "most natural" way to
> represent a query result is an array of hashes: Each row is hashref, but
> the the rows are in an array(ref) so that the order is preserved, and
> the columns can be accessed by name.
>
> Proposed use:
>
> my $emp = $dbh->selectall_hasharrayref("select * from emp order by ename");
>
> for (@$emp) {
> print "$_->{ename} ($_->{empno}): $_->{job}\n";
> }
>
> or
>
> for ($first .. $last) {
> print $emp->[$_]{ename}, "\n";
> }
>
> or something like that.
>
> What do you think?


The names are to vague and not accurate

selectall_arrayref_of_hashrefs() is what it is and anyone knows that
immediately by looking at its name instead of having to look up its
documentation

Re: Convenience function selectall_hasharrayref

On Tue, Jan 10, 2006 at 12:47:54PM +0100, Peter J. Holzer wrote:
> or selectall_arrayhashref?
>
> Anyway, I rather frequently find that the "most natural" way to
> represent a query result is an array of hashes: Each row is hashref, but
> the the rows are in an array(ref) so that the order is preserved, and
> the columns can be accessed by name.
>
> Proposed use:
>
> my $emp = $dbh->selectall_hasharrayref("select * from emp order by ename");
>
> for (@$emp) {
> print "$_->{ename} ($_->{empno}): $_->{job}\n";
> }
>
> or
>
> for ($first .. $last) {
> print $emp->[$_]{ename}, "\n";
> }
>
> or something like that.
>
> What do you think?

Anything wrong with the existing

my $emp = $dbh->selectall_arrayref("...", { Slice => {} });

?

Note that selectall_*arrayref* defines the outermost data that the
method returns. What's inside that is controlled by attributes.

For example, want an array or arrays where the inner arrays contain
only the 2nd and 3rd fields? Easy:

my $emp = $dbh->selectall_arrayref("...", { Slice => [2,3] });

Please spread the word. Way too many people seem to not know about this!

Tim.

Re: Convenience function selectall_hasharrayref

--xjyYRNSh/RebjC6o
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2006-01-10 17:17:21 +0000, Tim Bunce wrote:
> On Tue, Jan 10, 2006 at 12:47:54PM +0100, Peter J. Holzer wrote:
> > Anyway, I rather frequently find that the "most natural" way to
> > represent a query result is an array of hashes: Each row is hashref, but
> > the the rows are in an array(ref) so that the order is preserved, and
> > the columns can be accessed by name.
[...]
> > What do you think?
>=20
> Anything wrong with the existing
>=20
> my $emp =3D $dbh->selectall_arrayref("...", { Slice =3D> {} });
>=20
> ?

Nothing, except that I didn't know about it.

Looks like it was too obvious for me what fetchall_arrayref does (it
returns an arrayref, duh!) so I never read what it really does
(sometimes it returns a hashref).

> Please spread the word. Way too many people seem to not know about this!

Now there are a few people less who don't know about it. Maybe an example
in the man-page would make it clearer. OTOH, the DBI manpage is already
very long for a manpage (pod2latex DBI.pm produces a 98 page document).

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--xjyYRNSh/RebjC6o
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iQDQAwUBQ8TRHVLjemazOuKpAQHS7gXSA2Pr5QmomoIwBDbMfKtcYuDIUy7G 346r
vStqSxRpS0xa0drFtT4ZCmCy75ChkyLiMO8GTWwmkz6IMFqBkml8tyRxm/Yx cSUg
I6kWWgzAVIiS59Mw3Da9cue16F3H0EwkzjFw1k/oUCcaEN5wzDuCazXMZcHY Ajtr
fS3XksHGvrUvt9FGL/6hs3ZmuKQoofDTYmspc4bmeD/CUg/4Y/1fphTlXdu3 c9u1
zoykOEEZTVUVosVs8d90XIwOLw==
=a2rd
-----END PGP SIGNATURE-----

--xjyYRNSh/RebjC6o--

Re: Convenience function selectall_hasharrayref

Tim Bunce wrote:

>On Tue, Jan 10, 2006 at 12:47:54PM +0100, Peter J. Holzer wrote:
>
>
>>or selectall_arrayhashref?
>>
>>Anyway, I rather frequently find that the "most natural" way to
>>represent a query result is an array of hashes: Each row is hashref, but
>>the the rows are in an array(ref) so that the order is preserved, and
>>the columns can be accessed by name.
>>
>>Proposed use:
>>
>>my $emp = $dbh->selectall_hasharrayref("select * from emp order by ename");
>>
>>for (@$emp) {
>> print "$_->{ename} ($_->{empno}): $_->{job}\n";
>>}
>>
>>or
>>
>>for ($first .. $last) {
>> print $emp->[$_]{ename}, "\n";
>>}
>>
>>or something like that.
>>
>>What do you think?
>>
>>
>
>Anything wrong with the existing
>
> my $emp = $dbh->selectall_arrayref("...", { Slice => {} });
>
>?
>
>Note that selectall_*arrayref* defines the outermost data that the
>method returns. What's inside that is controlled by attributes.
>
>For example, want an array or arrays where the inner arrays contain
>only the 2nd and 3rd fields? Easy:
>
> my $emp = $dbh->selectall_arrayref("...", { Slice => [2,3] });
>
>Please spread the word. Way too many people seem to not know about this!
>
>Tim.
>
>
>
>

Maybe if there would be an example like this in the POD people would
realize this? I'd volunteer to create such a doc-patch but where can I
get the latest source with all changes made since the last cpan release?


Tom

Re: Convenience function selectall_hasharrayref

On Wed, Jan 11, 2006 at 10:37:58AM +0100, Tom Schindl wrote:
> Tim Bunce wrote:
> >Anything wrong with the existing
> >
> > my $emp = $dbh->selectall_arrayref("...", { Slice => {} });
> >
> >?

> >Please spread the word. Way too many people seem to not know about this!
>
> Maybe if there would be an example like this in the POD people would
> realize this? I'd volunteer to create such a doc-patch

Thanks!

> but where can I
> get the latest source with all changes made since the last cpan release?

See http://search.cpan.org/~timb/DBI/DBI.pm#CONTRIBUTING

Tim.

Re: Convenience function selectall_hasharrayref

--zhtSGe8h3+lMyY1M
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2006-01-11 10:34:21 +0100, Peter J. Holzer wrote:
> On 2006-01-10 17:17:21 +0000, Tim Bunce wrote:
> > Anything wrong with the existing
> >=20
> > my $emp =3D $dbh->selectall_arrayref("...", { Slice =3D> {} });
> >=20
> > ?
>=20
> Nothing, except that I didn't know about it.
>=20
> Looks like it was too obvious for me what fetchall_arrayref does (it
> returns an arrayref, duh!) so I never read what it really does
> (sometimes it returns a hashref).

No, it doesn't. It returns an arrayref containing hashrefs. Obviously I
can't distinguish between fetchall_arrayref and fetchrow_arrayref (at
least not before finishing the first cup of coffee of the day).

hp


--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--zhtSGe8h3+lMyY1M
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iQDQAwUBQ8TTBlLjemazOuKpAQF6hgXUDJPdB9BZTUzpW1vLboZr+ZepUpGN xqHD
tZJ9Nvupx+y6bJ+X1BWp5kjJ0h2QTuW51pAXW3mRQw98NW/KFt+3xc9bjONN iWRy
xSMcmVVmAeoiQVy5v2lX6H5+kkH0FdqI0KdIrU9axBnIebWn/ZNQi09YHEFn eq1d
cDkN776bNgO69ewMX8rYNptpLpvHnIWFwthaYp74H11IN9Ql8Itvkk9E6Gz6 CzQD
WyO05uDj8Q1IKUKn/QDa061+fQ==
=QKo/
-----END PGP SIGNATURE-----

--zhtSGe8h3+lMyY1M--

Re: Convenience function selectall_hasharrayref

--------------010405030400020303010306
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

and here's the proposed patch hopefully my english is good enough.

Tom

--------------010405030400020303010306
Content-Type: text/x-patch;
name="DBI-doc.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="DBI-doc.patch"

Index: DBI.pm
============================================================ =======
--- DBI.pm (Revision 2381)
+++ DBI.pm (Arbeitskopie)
@@ -4014,7 +4014,17 @@
In which case the array is copied and each value decremented before
passing to C.

+The following example demonstrates the power of this method by fetching
+all rows as an array-ref and addressing the row's column as hash-keys:

+ my $emps = $dbh->selectall_arrayref("select * from emp order by ename", { Slice => {} } );
+ foreach my $emp ( @{ $emps } )
+ {
+ print "Employee: " . $emp->{ename} . "\n";
+ }
+
+When using an empty HashRef as Slice-Value all columns will be return in the Hash.
+
=item C

$hash_ref = $dbh->selectall_hashref($statement, $key_field);

--------------010405030400020303010306--

Re: Convenience function selectall_hasharrayref

Great. Thanks Tom.

Tim.

On Wed, Jan 11, 2006 at 01:10:43PM +0100, Tom Schindl wrote:
> and here's the proposed patch hopefully my english is good enough.
>
> Tom

> Index: DBI.pm
> ============================================================ =======
> --- DBI.pm (Revision 2381)
> +++ DBI.pm (Arbeitskopie)
> @@ -4014,7 +4014,17 @@
> In which case the array is copied and each value decremented before
> passing to C.
>
> +The following example demonstrates the power of this method by fetching
> +all rows as an array-ref and addressing the row's column as hash-keys:
>
> + my $emps = $dbh->selectall_arrayref("select * from emp order by ename", { Slice => {} } );
> + foreach my $emp ( @{ $emps } )
> + {
> + print "Employee: " . $emp->{ename} . "\n";
> + }
> +
> +When using an empty HashRef as Slice-Value all columns will be return in the Hash.
> +
> =item C
>
> $hash_ref = $dbh->selectall_hashref($statement, $key_field);