How do I know how many rows a Perl DBI query returns?

Question

I'm trying to basically do a search through the database with Perl to tell if there is an item with a certain ID,. This search can return no rows, but it can also return one.

I have the following code:

my $th = $dbh->prepare(qq{SELECT bi_exim_id FROM bounce_info WHERE bi_exim_id = '$exid'});
$th->execute();
if ($th->fetch()->[0] != $exid) {
        ...

Basically, this tries to see if the ID was returned and if it's not, continue with the script. But it is throwing a Null array reference error on the $th->fetch()->[0] thing. How can i just simply check to see if it returned rows or now?

Answer 1

my $th = $dbh->prepare(qq{SELECT bi_exim_id FROM bounce_info WHERE bi_exim_id = '$exid'});
$th->execute();
my $found = 0;
while ($th->fetch()) 
{
   $found = 1;
}

Your query won't return anything if the row doesn't exist, so you can't de-reference the fetch.

Update: you might want to re-write that as

my $found = th->fetch();

Answer 2

Change select to always return something? This should work in Sybase, dunno about other DBs.

my $th = $dbh->prepare(qq{SELECT count(*) FROM bounce_info WHERE bi_exim_id = '$exid'});
$th->execute();
if ($th->fetch()->[0]) {
....
}

Answer 3

The DBD::mysql driver has a the rows() method.

$sth = $dbh->prepare( ... );
$sth->execute;
$rows = $sth->rows;

Answer 4

In general, I'm not sure why people are so afraid of exceptions. You catch them and move on.

my $sth = prepare ...
$sth->execute;
my $result = eval { $sth->fetchrow_arrayref->[1] };

if($result){ say "OH HAI. YOU HAVE A RESULT." }
else       { say "0 row(s) returned."         }

In this case, though, Paul's answer is best.

Also, $sth->rows doesn't usually work until you have fetched every row. If you want to know how many rows match, then you have to actually ask the database engine the question you want to know the answer to; namely select count(1) from foo where bar='baz'.

Answer 5

The only reliable way to find out how many rows are returned by a SELECT query is to fetch them all and count them. As stated in the DBI documentation:

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.

For SELECT statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. So use of the rows method or $DBI::rows with SELECT statements is not recommended.

However, when you get down to it, you almost never need to know that in advance anyhow. Just loop on while ($sth->fetch) to process each row or, for the special case of a query that will only return zero or one rows,

if ($sth->fetch) {
  # do stuff for one row returned
} else {
  # do stuff for no rows returned
}

Answer 6

Why don't you just "select count(*) ..."??

my ($got_id) = $dbh->selectrow_array("SELECT count(*) from FROM bounce_info WHERE bi_exim_id = '$exid'");

Or to thwart Little Bobby Tables:

my $q_exid = $dbh->quote($exid);
my ($got_id) = $dbh->selectrow_array("SELECT count(*) from FROM bounce_info WHERE bi_exim_id = $q_exid");

Or if you're going to execute this a lot:

my $sth = $dbh->prepare("SELECT count(*) from FROM bounce_info WHERE bi_exim_id = ?");
....save $sth (or use prepare_cached()) and then later
my ($got_id) = $dbh->selectrow_array($sth, undef, $exid);