Re: DBI specification change
by blakem (Monsignor) on Jul 16, 2002 at 10:01 UTC
|
Hmm... Can't say that I like the newly documented behavior, but its always been a bit murky anyway. See this discussion where three of us so-called-saints all got it wrong. Perhaps in a future release, fetchrow_array should throw a warning when called in scalar context.
-Blake
| [reply] [Watch: Dir/Any] |
|
I agree that fetchrow_array in scalar context should throw a warning. Somthing like:
$scalar = $sth->fetchrow_array better written as ($scalar) = $sth->fet
+chrow_array
| [reply] [Watch: Dir/Any] [d/l] |
Re: DBI specification change
by hakkr (Chaplain) on Jul 16, 2002 at 10:24 UTC
|
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);
}
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;)
| [reply] [Watch: Dir/Any] [d/l] |
|
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) };
$|=$_="1g2i1u1l2i4e2n0k",map{print"\7",chop;select$,,$,,$,,$_/7}m{..}g
| [reply] [Watch: Dir/Any] [d/l] |
|
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] [Watch: Dir/Any] [d/l] |
|
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};
}
| [reply] [Watch: Dir/Any] [d/l] |
|
| [reply] [Watch: Dir/Any] [d/l] |
|
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};
}
}
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] [Watch: Dir/Any] [d/l] [select] |
Re: DBI specification change
by bronto (Priest) on Jul 16, 2002 at 14:14 UTC
|
Don't you think that,
since this change is going to break existing
code significantly, it should deserve a major change
in version numbering also?
Personally, when I upgrade from version n.m to version
n.m+1 I expect that nothing differs but bug fixes or minor
changes. And, a fortiori, that my programs that ran on n.m
will still run flawlessly on n.m+1. I think that many people
will fall in the trap of upgrading and finding their
(production?) code broken; it is not
fair1
Ciao! --bronto
Note: breaking other's
code is not fair, but upgrading without reading release
notes is unsafe: I agree!
# Another Perl edition of a song:
# The End, by The Beatles
END {
$you->take($love) eq $you->made($love) ;
}
| [reply] [Watch: Dir/Any] |
|
Bronto, did you see this paragraph? They just changed the spec to match the old behaviour, so if your code worked before (with a Driver.xst-based driver), it'll still work now...
FYI, this change was triggered by discovering that the fetchrow_array implementation in Driver.xst (used by most compiled drivers) didn't match the DBI specification. Rather than change the code to match, and risk breaking existing applications, I've changed the specification (that part was always of dubious value anyway).
--
Mike
| [reply] [Watch: Dir/Any] |
|
You got a point here.
My reply, anyway, was triggered by this paragraph of
gmax posting...
I am posting this warning because I have seen some SOPWs
where people were using this particular idiom.
...and triggered that consideration of mine, which is
general and not limited to DBI, that version
n.m+1 shouldn't break any code that works with n.m. In case
something could be broken, one should choose a new M>>m,
obviously IMHO. No attempt to blame to DBI people there
;-)
Ciao! --bronto
Update: Edited the HTML, misplaced tag
# Another Perl edition of a song:
# The End, by The Beatles
END {
$you->take($love) eq $you->made($love) ;
}
| [reply] [Watch: Dir/Any] |
Re: DBI specification change
by perrin (Chancellor) on Jul 16, 2002 at 14:53 UTC
|
Using fetchrow_array is a bad idea anyway. Much better to use fetchrow_arrayref (aliased as fetch) or the even better bind_columns. | [reply] [Watch: Dir/Any] |
|
| [reply] [Watch: Dir/Any] |
|
The performance of fetchrow_arrayref is much better than fetchrow_array, and bind_columns is even faster. This is discussed in Tim Bunce's presentation . Basically, the less copying you do the better it performs.
| [reply] [Watch: Dir/Any] |
Re: DBI specification change
by runrig (Abbot) on Jul 17, 2002 at 00:35 UTC
|
However, selecting two or more columns and then fetching only one is a plain waste of resources. If you need to fetch only the first column of a query, I recommend that you be specific and ask for that column only.
In some databases (I'm thinking of Informix in particular), fields in the ORDER BY clause must be in the SELECT clause, so maybe you want to select the customer with the highest balance, but don't really need the
balance itself, so you fetch the first field of the first row of this: select customer, balance
from customers
order by balance desc
| [reply] [Watch: Dir/Any] [d/l] |