Re: Weird DBI behaviour

Your two snippets are not exactly equivalent. In the first, you call

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

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
[download]

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]);
[download]

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;
[download]

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  );
[download]
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

Comment on Re: Weird DBI behaviour Select or Download Code

Replies are listed 'Best First'.
Re^2: Weird DBI behaviour by Joost (Canon) on Aug 19, 2005 at 13:23 UTC
`# instead of my $array_ref = $select->fetchall_arrayref(); # try my $array_ref = $select->fetchall_arrayref;` [download] Those are exactly equivalent, not matter what the definition of that method is. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l]
Re^2: Weird DBI behaviour by jZed (Prior) on Aug 19, 2005 at 15:45 UTC
I'm really perplexed that people find these names so confusing. seleactall_arrayref selects all rows returned into an array reference. 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 );` [download]	[reply] [d/l]
Re^3: Weird DBI behaviour by punkish (Priest) on Aug 19, 2005 at 16:38 UTC
> 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. [download] 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	[reply] [d/l]
Re^4: Weird DBI behaviour by jZed (Prior) on Aug 19, 2005 at 16:47 UTC
# 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 methods are all named the same because they all do one thing: return results into a reference to an array. The arguements to the methods are different because they specify the nature or contents of that arrayref. Most methods behave diffently when passed different parameters. BTW, I'm not at all blaming you for being confused and I don't deny that it gets a bit mucky in there. I'm just pointing out that there is a more general logic to how DBI methods (and especially attributes to those methods) work.	[reply]