Parsing Results from fetchall_hashref()

I'm trying to debug a problem in a Perl script that retrieves records
from an Oracle database and writes those records to a flat file. I'm
retrieving the records using fetchall_hashref() and have found that at
least one record isn't being written correctly so I'm trying to figure
out why.

I can access the value of each member just fine using $mediaref-
>{$media_id}->{'ADID'} and I can access the size of the entire record
set as keys ( %$mediaref ). What I need to know, though, is how many
elements exist in $mediaref->{$media_id}, but I can't figure out
whether that's possible or how to do it. I've tried all kinds of
variations of keys ( %$mediaref->{$mediaid} ) with no luck at all.

Is it possible? Am I correctly guessing at how the system stores
fields (i.e. as a nested hash)?

Any insight would be very much appreciated.

Thanks.

Rob Wilkerson

Re: Parsing Results from fetchall_hashref()

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

On 2007-10-26 19:39:38 -0000, Rob Wilkerson wrote:
> I'm trying to debug a problem in a Perl script that retrieves records
> from an Oracle database and writes those records to a flat file. I'm
> retrieving the records using fetchall_hashref() and have found that at
> least one record isn't being written correctly so I'm trying to figure
> out why.
>=20
> I can access the value of each member just fine using $mediaref-
> >{$media_id}->{'ADID'} and I can access the size of the entire record
> set as keys ( %$mediaref ). What I need to know, though, is how many
> elements exist in $mediaref->{$media_id}, but I can't figure out
> whether that's possible or how to do it. I've tried all kinds of
> variations of keys ( %$mediaref->{$mediaid} ) with no luck at all.

scalar keys %{ $mediaref->{$mediaid} }

But I'm quite sure that will return the same number for all rows in the
query. Maybe you are more interested in the number of fields which are
not null:

scalar grep {
defined $mediaref->{$mediaid}{$_}
} keys %{ $mediaref->{$mediaid} }

> Is it possible?

Yes.

> Am I correctly guessing at how the system stores fields (i.e. as a nested
> hash)?

Yes. No need to guess, though. perldoc DBI states that quite clearly:

The "fetchall_hashref" method can be used to fetch all the data to be
returned from a prepared and executed statement handle. It returns a
reference to a hash containing a key for each distinct value of the
$key_field column that was fetched. For each key the corresponding
value is a reference to a hash containing all the selected columns a=
nd
their values, as returned by fetchrow_hashref().

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

--PHCdUe6m4AxPMzOu
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFHJKrRMdFfQa64PCwRArIVAJ9N7vWKImP8MujbVY+GSd2zQSU9fwCf XMRI
jgtCNVaR9TtdOC2chE0qpF8=
=yepq
-----END PGP SIGNATURE-----

--PHCdUe6m4AxPMzOu--

Re: Parsing Results from fetchall_hashref()

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

On 2007-10-28 16:29:21 +0100, Peter J. Holzer wrote:
> On 2007-10-26 19:39:38 -0000, Rob Wilkerson wrote:
> > I can access the value of each member just fine using $mediaref-
> > >{$media_id}->{'ADID'} and I can access the size of the entire record
> > set as keys ( %$mediaref ). What I need to know, though, is how many
> > elements exist in $mediaref->{$media_id}, but I can't figure out
> > whether that's possible or how to do it. I've tried all kinds of
> > variations of keys ( %$mediaref->{$mediaid} ) with no luck at all.
>=20
> scalar keys %{ $mediaref->{$mediaid} }
>=20
> But I'm quite sure that will return the same number for all rows in the
> query. Maybe you are more interested in the number of fields which are
> not null:
>=20
> scalar grep {
> defined $mediaref->{$mediaid}{$_}
> } keys %{ $mediaref->{$mediaid} }

Or, a bit simpler:

scalar grep { defined $_ } values %{ $mediaref->{$mediaid} }

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

--+SfteS7bOf3dGlBC
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFHJLDuMdFfQa64PCwRAqGSAJ9xzNXy16rq43nXmsd7Do0euhqBywCg nbPB
1BaPaMWl8EEiTkLpLij5sQo=
=hsd5
-----END PGP SIGNATURE-----

--+SfteS7bOf3dGlBC--