Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

Weird DBI behaviour

by toadi (Chaplain)
on Aug 19, 2005 at 12:02 UTC ( #485088=perlquestion: print w/replies, xml ) Need Help??
toadi has asked for the wisdom of the Perl Monks concerning the following question:

Writing this not to find a solution to a problem. But try to understand why something behaves that way.

I had to debug some code written by some else because sometimes it did't do what it needed todo.

We need to fetch paths to files from mysql but ordered by a certain field. The guy wrote following code:

my $select = $dbh->prepare("select path from application_table where +number = ? ORDER BY some_other_number"); $select->execute($number); my $array_ref = $select->fetchall_arrayref(); foreach my $row (@$array_ref) { foreach (@$row) { print "path: $_\n"; } }

for me this looked weird, but the coding style of other people always look weird. But the trouble whas that in some cases the paths printed weren't sorted anymore by some_other_number row in mysql.

I solved it like this:

my $select = $dbh->prepare("select path from application_table where +number = ? ORDER BY some_other_number"); $select->execute($number); my $array_ref = $select->fetchall_arrayref(); while(my($path) = $select->fetchrow_array) { print "PATH: $path\n"; }

Now it got sorted even in those rare cases it wasen't sorted. But I couldn't give a reasonable explanation why in some cases it wasen't sorted in the first example??? Can someone enlighten me???

My opinions may have changed,
but not the fact that I am right

Replies are listed 'Best First'.
Re: Weird DBI behaviour
by Joost (Canon) on Aug 19, 2005 at 12:10 UTC

      Well I'm actually sure it comes out the right way. If i do the query on the mysql database it comes out in the right order. But perl doesn't print them in the right order in the first example.

      My opinions may have changed,
      but not the fact that I am right

Re: Weird DBI behaviour
by davidrw (Prior) on Aug 19, 2005 at 12:16 UTC
    That is extremely weird, as reading through both examples they should be doing the exact same thing. Can you give examples of the ordering from each (maybe something weird in the data being sorted??)? I will agree that sample 1 is a little weird -- that nested foreach loop is completely useless since @$row only ever has one element in it.

    Anyways, i didn't answer your primary question, but i'm curious as to the answer and will watch this thread.. I can also add though that you can use one of DBI's shortcut methods, too:
    my $paths = $dbh->selectcol_arrayref(""select path from application_ta +ble where number = ? ORDER BY some_other_number", {}, $number); foreach my $path ( @$paths ){ print "PATH: $path\n"; }
      Example i order on next values:
      032/001 032/002 032/003 032/004 032/005
      If I do the query on the database itself the ordering is ok. But if print them with the first piece of perl code I have given it isn't sorted anymore.

      My opinions may have changed,
      but not the fact that I am right

Re: Weird DBI behaviour
by punkish (Priest) on Aug 19, 2005 at 12:43 UTC
    Your two snippets are not exactly equivalent. In the first, you call

    my $array_ref = $select->fetchall_arrayref();

    and actually use it. In the second, you call the above, but don't do anything with it as you subsequently call

    You could fetch only the one-and-only column in the first snippent itself by doing the following --

    my $array_ref = $select->fetchall_arrayref([0]);

    All that said, there is no reason I can think of why the ordering should be different.

    Update: Try the following in the first snippet

    # instead of my $array_ref = $select->fetchall_arrayref(); # try my $array_ref = $select->fetchall_arrayref;

    From the DBI docs

    fetchall_arrayref $tbl_ary_ref = $sth->fetchall_arrayref; $tbl_ary_ref = $sth->fetchall_arrayref( $slice ); $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );

    The fetchall_arrayref method can be used to fetch all the data to be returned from a prepared and executed statement handle. It returns a reference to an array that contains one reference per row.

    If there are no rows to return, fetchall_arrayref returns a reference to an empty array. If an error occurs, fetchall_arrayref returns the data fetched thus far, which may be none. You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the data is complete or was truncated due to an error.

    If $slice is an array reference, fetchall_arrayref uses "fetchrow_arrayref" to fetch each row as an array ref. If the $slice array is not empty then it is used as a slice to select individual columns by perl array index number (starting at 0, unlike column and parameter numbers which start at 1).

    With no parameters, or if $slice is undefined, fetchall_arrayref acts as if passed an empty array ref.

    I sometimes wish DBI were simpler in the naming conventions of its methods. I have always resisted using Class::DBI (I believe if a difficult task requires an abstraction to make it easier, then the task should be made easier in the first place), but I guess it is time to give in and start learning C::DBI and its ilk that attempt to make DBI easier to use.

    when small people start casting long shadows, it is time to go to bed
      I'm really perplexed that people find these names so confusing. seleactall_arrayref *selects* *all* rows returned into an *array* *ref*erence. selectall_hashref *selects* *all* rows returned into a *hash* *ref*. The confusion comes because there are two types of arrayrefs in Perl - a reference to an Array of Array refs (AoA) and a reference to an Array of Hash references (AoH). DBI disambiguates them by allowing you to supply a slice as an argument to the arrayref and therefore specifying that you want an AoH instead of an AoA. In either case you are getting back a reference to an array and therefore want selectall_arrayref.

      If my verbiage doesn't clarify, perhaps these succinct examples will help.

      update You might want to check out the latest DBI since the docs you cite have been rewritten and are perhaps clearer (if not, submit a doc patch). One thing that the newer docs make clear is that the attributes hash in select_all_arrayref($stmt, \%attr, @vals) is a very powerful feature and that supplying a slice is only one of the things you can do with it. If you supply a max value in the attributes hash, for example, your result set will be limited to that number of rows (a perl version of LIMIT for those databases that don't support it.

      The basic idea is that the name of the method "selectall_arrayref" specifies what kind of a strucure the results will be returned in and the attributes hash specifies the characteristics of that structure. Yes, I do see how that can be a bit confusing, but it's also a very powerful concept. Upcoming versions of DBI will use these attribute hashes to methods even more extensively allowing you to specify both the return structure, attributes of that structure, and attributes of the method of fetching.

      update 2 Thanks for bringing this up, it's forced me to think :-). Here's a generic description of a DBI method call that applies to selectall_arrayref and most other DBI methods:
      $handle->what_perl_structure_to_fetch_results_into( $what_results_to_fetch, \%characteristics_of_the_fetch_and_of_the_fetch_into, @values_to_further_specify_what_to_fetch );
        > I'm really perplexed that people find these names so confusing

        $tbl_ary_ref = $sth->fetchall_arrayref; $tbl_ary_ref = $sth->fetchall_arrayref($slice); $tbl_ary_ref = $sth->fetchall_arrayref($slice, $max_rows); $tbl_ary_ref = $sth->fetchall_arrayref({foo=>1, BAR=>1}); $tbl_ary_ref = $sth->fetchall_arrayref({}); # They all are named the same but do things # differently. Which is strange, because Perl # makes a big deal (rightfully so, in my view) # that different things should look different. # # The last two are particularly perplexing (to me). # they don't even have the word 'hash' yet fetch # every row as a hash ref! # # That said, I use the last one all the time # because I can pass the returned data structure # directly to my beloved [cpan://HTML::Template] object.

        Otoh, it could be just me. After a few years at Perl, I still can't tell the various punctuation variables apart (other than $_ and @_). It is funny, last week a friend went for an interview for a Perl job -- they had a quiz for him, and asked if what $& stood for. I would have failed the quiz immediately! Yet, I can program to save my life.


        when small people start casting long shadows, it is time to go to bed

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://485088]
Approved by Nevtlathiel
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (7)
As of 2017-02-25 02:25 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (364 votes). Check out past polls.