If there are no rows to return, "fetchall_arrayref" returns a reference to an empty array.

if ( scalar @$result > 0) {
... process rows ...
   print $result[0]->{'b'}."\n";
}
else {
... no rows returned logic ...
}
[download]

and don't forget to set RaiseError or check $sth->err afterwards because if fetchrow_arrayref fails it can still return rows. See http://search.cpan.org/~timb/DBI-1.607/DBI.pm#fetchall_arrayref

Small correction:

print $result->[0]->{'b'}."\n";

Small correction

Actually, $result->[0]{b} will work just fine as well. You don't need to dereference explicitly after the first one, nor do bareword keys need to be quoted.

Concision, concision!

• another intruder with the mooring in the heart of the Perl

you don't even need arrows or periods :)

#!/usr/bin/perl --
use strict;
use warnings;

my $result = [ { b => 'small erection' } ];
print $result->[0]->{'b'}."\n";
print $result->[0]{b}."\n";
print "$$result[0]{b}\n";
print "$result->[0]{b}\n";
__END__
small erection
small erection
small erection
small erection
[download]

From what I've read and from my recent experience with DBI, you'll have an easier time if you bind your columns. You can do something like this:

   my $b;
   my $sql            = qq{SELECT b FROM blah WHERE user = ?};
   my $sth            = $dbh->prepare("$sql");
      $sth           -> execute("bahbah");
      $sth           -> bind_col(1,\$b);
   while( $sth->fetchall_arrayref ) {
      print "$b\n"    if $_;
   }
[download]

Update: Indeed, runrig, that was bad. I munged the following two methods (perhaps neither of which fully applies since the OP is fetching hashrefs.)

   my $b;
   my $sql   = qq{SELECT b FROM blah WHERE user = ?};
   my $sth   = $dbh->prepare("$sql");
      $sth  -> execute('bahbah');
      $sth  -> bind_col(1,\$b);
   while( $sth->fetch ) {
      print "$b\n";
   }
   ################
   # OR:
   ################
   my $sql   = qq{SELECT b FROM blah WHERE user = ?};
   my $sth   = $dbh->prepare("$sql");
      $sth  -> execute('bahbah');
   my $rows  = [];    # cache for batches of rows
   while(my $row = shift(@$rows) 
                || shift(@{$rows=$sth->fetchall_arrayref([0],10_000)||
+[]})) {
      print "$row->[0]\n";
   }
[download]

Incidentally, while both produce the same lists, do you know why the latter also gives "ERROR no statement executing (perhaps you need to call execute first)"?

First, the fetchall_* methods fetch all of the rows, so you usually don't see fetchall in the conditional of while loops. Second, $_ isn't set anywhere in your code, so I don't know why you're testing it (the while() does not set it).

First, the fetchall_* methods fetch all of the rows

Not always true. The fetchall_arrayref takes a $max_rows optional argument which limits the number of rows fetched in one go to $max_rows. The example shown was in fact using $max_rows. e.g., from the DBI docs:

If $max_rows is defined and greater than or equal to zero then it is used to limit the number of rows fetched before returning. fetchall_arrayref() can then be called again to fetch more rows. This is especially useful when you need the better performance of fetchall_arrayref() but don't have enough memory to fetch and return all the rows in one go.

Here's an example (assumes RaiseError is enabled):

my $rows = []; # cache for batches of rows
while( my $row = ( shift(@$rows) || # get row from cache, or reload ca
+che:
                            shift(@{$rows=$sth->fetchall_arrayref(unde
+f,10_000)||[]}) )
         ) {
           ...
         }
[download]

BTW and slight OT, I gave up on "fetching" a couple of years ago after I read gmax's wonderful DBI Recipes. No prepping or executing needed.

my $stmt = qq/SELECT * FROM mytable WHERE id = ?/;
my $result = $dbh->selectall_arrayref($stmt, {Slice => {}}, $subscribe
+r_id);
[download]

You end up with an AoH, which is perfect for me as I'm usually populating a loop in HTML::Template