in reply to DBI specification change

I think DBI is great but there is one thing I would like to see added. I love being able to fetch a row into a hash that is automatically keyed by field name (fetchrow_hashref) but I also love being able to fetch all the rows with one call (fetchall_arrayref). How about a fetchall_arrayofhashref to save me using the code below everywhere.
 
my @rows;
while (my $result=$sth->fetchrow_hashref) {
    push (@rows, $result);
}

[download]
No biggie and I'm sure there are many more pressing issues I just reckon it would be cool. May require a more catchy name than fetchall_arrayofhashref;)

Replies are listed 'Best First'.
Re: Re: DBI specification change
by giulienk (Curate) on Jul 16, 2002 at 11:33 UTC
    I guess the method fetchall_arrayofhashref isn't provided for performance issues (mapping the keys to every hash at once). Anyway, knowing the columns names (as you should), you can mimic the behavior of the method you want with something like this: 
    my @fields = qw(name surname);
my $select = sprintf 'SELECT %s FROM clients', join(',', @fields);
my @arr_of_hashes = map {my %hash; @hash{@fields} = @$_; \%hash}
                               @{ $dbh->selectall_arrayref($select) };

    [download]


    $|=$_="1g2i1u1l2i4e2n0k",map{print"\7",chop;select$,,$,,$,,$_/7}m{..}g

[reply]
[d/l]
Re: Re: DBI specification change
by gav^ (Curate) on Jul 16, 2002 at 12:45 UTC
    You can use selectall_arrayref to do this: my $rows = $dbh->selectall_arrayref($sql, {Slice=>{}}); This works in ver 1.20+ I think

    Hope this helps...

    gav^

[reply]
[d/l]
Re: Re: DBI specification change
by mp (Deacon) on Jul 16, 2002 at 16:38 UTC
    The DBI pod on version 1.20, that I've been using says (under fetchrow_hashref):

    "Currently, a new hash reference is returned for each row. This will change in the future to return the same hash ref each time, so don't rely on the current behaviour."

    I've been interpreting this to mean that it is not safe to use the returned hash directly and have been copying the hash like this: 

    my @rows;
while(my $result = $sth->fetchrow_hashref) {
  push @rows, {%$result};
}

    [download]
[reply]
[d/l]
Re^2: DBI specification change
by Aristotle (Chancellor) on Jul 17, 2002 at 18:38 UTC
    gav^ is close, but there's a method that's closer yet; in fact it does exactly what you're asking for: fetchall_arrayref - no, that's not a typo, it will return a list of hashes, provided you pass it a hashref as argument. my $row_aoh = $sth->fetchall_arrayref({}); Note that you if you populate the anonymous hash, the keys are taken to select the desired columns from your query. See the DBI POD.

    Makeshifts last the longest.

[reply]
[d/l]
Re: Re: DBI specification change
by aufrank (Pilgrim) on Jul 18, 2002 at 16:06 UTC
    this actually already exists and is very useful. the method you're looking for is fetchall_arrayref( {} )

    from the docs:

    "When passed a hash reference, fetchall_arrayref uses /fetchrow_hashref to fetch each row as a hash reference. If the parameter hash is empty then fetchrow_hashref is simply called in a tight loop and the keys in the hashes have whatever name lettercase is returned by default from fetchrow_hashref."
    and
    "To fetch all fields of every row as a hash ref:
    $tbl_ary_ref = $sth->fetchall_arrayref({});"

    going through each row then looks something like: 

    foreach my $hashref_row (@$tbl_ary_ref) {
    foreach my $field (sort keys %$hashref_row) {
        my $value = $hashref_row->{$field};
    }
}

    [download]

    there's a good chance my use of references is off in the 3rd line, but at least I tried :)

    hope that's what you were looking for!
    --au

[reply]
[d/l]
[select]

In Section Meditations