DBI Array of Hashes... simpler way?

Rodster001 has asked for the wisdom of the Perl Monks concerning the following question:

Hello!

I was wondering if there is a slightly simpler (fewer lines) to accomplish what I am doing here (maybe without the while loop and/or push).

    my $query = qq~
        SELECT *
        FROM my_table
    ~;
    my $sth = $dbh->prepare($query);
    my $rv = $sth->execute;
    
    my @res;
    
    while (my $r = $sth->fetchrow_hashref) {
        push(@res, $r);
    }
[download]

Thanks!

Comment on DBI Array of Hashes... simpler way? Download Code

Replies are listed 'Best First'.
Re: DBI Array of Hashes... simpler way? by kennethk (Abbot) on Aug 24, 2009 at 22:06 UTC
DBI gives you the fetchall_arrayref and fetchall_hashref methods which do what you are attempting, though the documentation is unclear to my reading and I haven't worked with them myself. On a side note, your code will likely fail because (from DBI): Note that the same array reference is returned for each fetch, so don't store the reference and then use it after a later fetch. Also, the elements of the array are also reused for each row, so take care if you want to take a reference to an element. See also "bind_columns".	[reply]
Re^2: DBI Array of Hashes... simpler way? by ruzam (Curate) on Aug 25, 2009 at 04:09 UTC
The key word is 'array reference'. I don't believe fetchrow_hashref is subject to this limitation (any more).	[reply]
Re: DBI Array of Hashes... simpler way? by derby (Abbot) on Aug 25, 2009 at 01:47 UTC
Actually I think you really want to use the Slice option to selectall_arrayref: `$dbh->selectall_arrrayref( $sql, { Slice => {} } );` [download] awkward looking, yes but effective. -derby	[reply] [d/l]
Re^2: DBI Array of Hashes... simpler way? by Rodster001 (Pilgrim) on Aug 25, 2009 at 17:09 UTC
Yup, that's it. So, just for everyone else... this is what the modified code would look like. Much more concise, which is exactly what I was looking for. Thanks! `my $query = qq~ SELECT * FROM my_table ~; my $res = $dbh->selectall_arrayref($query, { Slice => {} } );` [download]	[reply] [d/l]
Re^2: DBI Array of Hashes... simpler way? by Anonymous Monk on Jul 16, 2013 at 01:41 UTC
derby, thanks so much. exactly what I was looking for.	[reply]
Re: DBI Array of Hashes... simpler way? by runrig (Abbot) on Aug 24, 2009 at 22:15 UTC
Look at the selectall_arrayref or selectall_hashref method (update: derby has it right below...selectall_arrayref with the Slice parameter).	[reply]