How can I fetch a single count value from a database with DBI?

Question

The following code seems to be just too much, for getting a single count value. Is there a better, recommended way to fetch a single COUNT value using plain DBI?

sub get_count {
   my $sth = $dbh->prepare("SELECT COUNT(*) FROM table WHERE...");
   $sth->execute( @params );
   my $($count) = $sth->fetchrow_array;
   $sth->finish;

   return $count;
}

This is shorter, but I still have two statements.

sub get_count_2 {
   my $ar = $dbh->selectall_arrayref("SELECT ...", undef, @params)
   return $ar->[0][0];
}

Solution 1

Easy enough to do in one line with no extra variables:

$count = $dbh->selectrow_array('SELECT count(*) FROM table WHERE...', undef, @params);

Solution 2

I don't know Perl, but if it's syntax is logical I would think this would work based on your 2nd example:

sub get_count {
   return $dbh->selectall_arrayref("SELECT ...", undef, @params)->[0][0];
}

Solution 3

I probably wouldn't do this myself, but you could always make it a new top-level function of the DBH object you're using:

WARNING: untested code follows!

sub DBD::SQLite::db::count
{
   my($dbh, $table, $where) = @_;

   my($stmt) = "SELECT COUNT(*) FROM $table";
   $stmt .= " WHERE $where" if $where;

   my($count) = $dbh->selectrow_array($stmt);

   return $count;

}

and then call it like this:

my($cnt) = $dbh->count('Employee', 'year_hired < 2000');

Besides polluting a namespace that's not yours, you'd also have to write this for every DB driver you use, though I'm sure your could work something up that allows you to construct and eval some code to auto-configure this for a given DBH object.

Comments

[pavium]

This is a little subjective. Some would say that your longer example is more readable. Are we playing Perl Golf?

[brian d foy]

Does it matter how many statements you have?

[szabgab]

I have many such calls in my code so I have a sub that gets the SQL statement and the @params and returns the count. If I had a built-in statement for that in DBI then I don't need the extra sub. I think it is a common use case and I was wondering if there was such a statement and I missed it or if there is no such statement in DBI.

[silbana]

@szabgab I don't think this should be a common case. What do you use the count for?

[friedo]

Good guess for someone who doesn't know Perl. :)

[szabgab]

nice, though it should be $dbh->selectall_arrayref("SELECT ...", undef, @params)->[0][0]; as the call returns a matrix and not a vector. (I had the same mistake in my original example but I fixed it since your comment)

[szabgab]

That's what I was looking for. Thanks!

[Lawrence Hutton]

Aside from polluting someone else's namespace and needing to rewrite it for every DBD you use, the solution as presented requires you to interpolate values into the SQL string instead of losing placeholders ('year_hired < ?'), so you lose access to the best possible protection against SQL injection attacks.

[Joe Casadonte]

Good point; the main thrust of this, though, was in response to the OP's comment "If I had a built-in statement for that in DBI".

[Gilles Quenot]

The doc said that you should be in list context, personally I use (my $count) = $dbh->selectrow_array('SELECT count(*) FROM table WHERE...', undef, @params);

[Lawrence Hutton]

Context is determined by the right-hand side of an assignment, so it's run in list context either way. I just take advantage of the detail that the scalar value of a list is the list's final element, while you're explicitly grabbing the first element of the list (which has the same result, since the list in this case will always contain exactly one item).

[ThisSuitIsBlackNot]

Context is not determined by the right-hand side of an assignment (see the Context Tutorial on PerlMonks).

[ThisSuitIsBlackNot]

From the DBI docs: "If called in a scalar context for a statement handle that has more than one column, it is undefined whether the driver will return the value of the first column or the last. So don't do that. Also, in a scalar context, an undef is returned if there are no more rows or if an error occurred. That undef can't be distinguished from an undef returned because the first field value was NULL. For these reasons you should exercise some caution if you use selectrow_array in a scalar context, or just don't do that."

[Lawrence Hutton]

@ThisSuitIsBlackNot - Looking back on it five months later, I have no idea what I was thinking when I said context is determined by the RHS... Regarding the quote from the DBI docs, those are good general warnings, but this is a specific case where the query will always return exactly one row containing exactly one column whose value will never be NULL, so none of the concerns listed there apply. I'd say that qualifies as "exercis[ing] some caution".