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

This is a little subjective. Some would say that your longer example is more readable. Are we playing Perl Golf? — pavium, Nov 01 '09 at 09:58
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. — szabgab, Nov 01 '09 at 11:51
@szabgab I don't think this should be a common case. What do you use the count for? — Sinan Ünür, Nov 01 '09 at 12:18

score 36 · Accepted Answer · answered Nov 01 '09 at 12:32

36

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

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

answered Nov 01 '09 at 12:32

Dave Sherohman

45,363
14
64
102

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);` – Gilles Quénot Aug 06 '13 at 13:26
1

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). – Dave Sherohman Aug 07 '13 at 07:11
1

Context is *not* determined by the right-hand side of an assignment (see the [Context Tutorial](http://www.perlmonks.org/?node_id=738558) on PerlMonks). – ThisSuitIsBlackNot Jan 09 '14 at 18:28
From the [DBI docs](http://search.cpan.org/~timb/DBI-1.630/DBI.pm#selectrow_array): "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." – ThisSuitIsBlackNot Jan 09 '14 at 18:28
4

@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". – Dave Sherohman Jan 10 '14 at 08:34

Tony Andrews · Answer 2 · 2009-11-02T10:14:16.100

3

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

edited Nov 02 '09 at 10:14

answered Nov 01 '09 at 11:24

Tony Andrews

129,880
21
220
259

3

Good guess for someone who doesn't know Perl. :) – friedo Nov 01 '09 at 14:55
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 Nov 01 '09 at 15:03

score 1 · Answer 3 · answered Nov 01 '09 at 14:13

1

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.

answered Nov 01 '09 at 14:13

Joe Casadonte

15,888
11
45
57

1

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. – Dave Sherohman Nov 02 '09 at 11:09
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". – Joe Casadonte Nov 02 '09 at 13:33

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

3 Answers3

Linked