SQLite: how to get a number of rows from SELECT query

[igoryonya]

I used to do something like this on MySQL:

$sth = $dbh->prepare("SELECT * FROM table");

if($sth->execute()){
    printf "Executed Statement - returned (%s) rows\n", $sth->rows;
}

and it returned a number of rows, found by SELECT, but, no matter, what SELECT variation I use with SQLite, even when it returns the actual row data with $sth->fetchrow_hashref(), it always returns 0 (zero).
Is there a way to determine the number of rows, found by select, if not with $sth->rows?

[Corion]

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.

[igoryonya]

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

[Fletch]

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.

[derby]

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

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

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 )
+;

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

[Arunbear]

SQLite doesn't seem to have an equivalent of MySQL's FOUND_ROWS feature (though even that is now deprecated in MySQL).

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

[erix]

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

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

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

[perlfan]

scalar + selectall_array (or selectall_arrayref might be your friends here). If you need a memory "lite" option and can afford an second SQL call, there's always the SQL COUNT(*) dealio.
But if say you have all rows in $results_ref (an array ref); it'd be like:

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;

[Anonymous Monk]

If all that you need is the count, then something like this should work much more efficiently: select count(*) as count from ...