Weird DBI behaviour

[toadi]

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

[Joost]

They both look correct and equivalent (though the second code is a bit more readable).

How do you know they aren't sorted correctly? It looks to me that the "some_other_number" columns might not contain what you think it does. Try printing out the value of "some_other_number" column too.

[toadi]

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.

[Joost]

Can you provide an example with working code and a (small) database dump, and the exact query you run on the mysql client?

I'm pretty sure something else is going on; for example, are you sure the bind value for number is what you think it is?

[davidrw]

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

[toadi]

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.

[punkish]

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

..$select->fetchrow_array

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.

[Joost]

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

Those are exactly equivalent, not matter what the definition of that method is.

[jZed]

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

);

[punkish]

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