The DBI documentation states in ->execute for SELECT statements:

The execute method does not return the number of rows that will be returned by the query

So you will have to run a second query to fetch the number of returned rows, or fetch all rows to get their number.

Hm, pity.
So, it's just MySQL feature, I guess.

As the documentation mentions (rows of DBI; look for rows under statement handle methods if that link doesn't work) it's dependent on the underlying database driver to begin with, not to mention what your query actually does. The DBD documentation may mention the behavior, too.

Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement.

The cake is a lie.
The cake is a lie.
The cake is a lie.

This is why my favorite DBI method is selectall_arrayref with the attribute Slice set

  my $rows= $dbh->selectall_arrayref( "SELECT * FROM table", { Slice =
+> {} } );
[download]

Sure it looks wonky and pretentious but this approach gives my an array reference of hash references where the hash key is the column name and the values are well, the data values. Now you can just use perl to get the size:

  printf "Executed Statement - returned (%d) rows\n", scalar( @$rows )
+;
[download]

As a developer the concept of prepare, execute, and fetch is alluring and I can see its' value in certain situations but pragmatically, wrapping those steps into one is the common case with re-use of statement handle being the uncommon case (and for the future comments, I said uncommon - not rare). I am aware of the downsides to this approach such as the result set being too large and needing to be fetched in smaller batch sizes but I find a lot of the downsides to be outlier cases that are rarely hit (at least for my domain).

If using DBIx::Class, is an option, it makes this kind of usage simple (at the cost of doing two queries) e.g.

my $cd_rs = $schema->resultset('CD')->search({ year => 2005 });

printf "returned (%s) rows\n", $cd_rs->count;

while($cd = $cd_rs->next) {
  print $cd->title;
}
[download]

If the SQLite is new enough, you could use a window function:

select 
    *
  , count(*) over () as rowcount
from t ;
[download]

(But of course, now you have an extra column which is not always what you want. YMMV, etc)

my $results_ref = $dbh->selectall_arrayref(....);
if (@$results_ref) {
  # ... do stuff
}

# or just get $count, (using `scalar` for clarity but it's already in 
+scalar
# context so works without it here)
my $count = scalar @$results_ref;
[download]