Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

DBI specification change

by gmax (Abbot)
on Jul 16, 2002 at 08:22 UTC ( [id://182023]=perlmeditation: print w/replies, xml ) Need Help??

DBI 1.29 is out. There is a change in the specification. (And possibly a discrepancy in previous releases, read on.)
fetchrow_array in scalar context is not guaranteed to return the first column, as previously stated.

I am posting this warning because I have seen some SOPWs where people were using this particular idiom.

I quote from the release notes:
Changes in DBI 1.29, 15th July 2002

NOTE: This release changes the specified behaviour for the fetchrow_array method when called in a scalar context: The DBI spec used to say that it would return the FIRST field. Which field it returns (i.e., the first or the last) is now undefined. This does not affect statements that only select one column, which is usually the case when fetchrow_array is called in a scalar context.
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).
Rather than using fetchrow_array in scalar context, consider calling it by assigning to a one-element list:
my $sth=$dbh->prepare("SELECT surname,name from people"); $sth->execute(); while (my ($surname) = $sth->fetchrow_array()) { # do something with $surname }
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.

One more warning, from the same source. Future versions of the DBI may not support Perl 5.5 any longer. If you have database applications, consider upgrading to Perl 5.6.1.
 _  _ _  _  
(_|| | |(_|><
 _|   

Replies are listed 'Best First'.
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

      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
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;)
      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

      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^

      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}; }
      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.

      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

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) ;
    }

      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

        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) ;
        }

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.
        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.
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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://182023]
Approved by dws
Front-paged by hsmyers
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others pondering the Monastery: (6)
As of 2024-03-19 09:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found