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?

Solution 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();

Solution 2

The DBD::mysql driver has a the rows() method that can return the count of the results:

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

This is database-driver specific, so it might not work in other drivers, or it might work differently in other drivers.

Solution 3

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

Solution 4

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]) {
....
}

Solution 5

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

View more solutions

Comments

[Robert P]

This really has more to do with the library you're using ... you might be able to update your question to be more specific.

[Paul Tomblin]

@Robert, it has nothing to do with the library he's using. All ODBC drivers act the same way.

[innaM]

I hope this is just example code. But you should be using bind values.

[runrig]

DBI has the rows() method, but rows() does not return a meaningful result for SELECT's in many DBD's until all rows are fetched.

[brian d foy]

We're talking about a particular DBD here. :)

[runrig]

Ah, I see it now...hidden in the tags for the node...it should be in the title or text if that's an important part of the question :-)

[Lee Goddard]

"...you almost never need to know that in advance anyhow" -- for the very common case of result pagination, you will need to know in advance how many results there are.

[Bulrush]

And I like to see how many records my program has read/processed, out of a total count of records selected. If my select grabbed 10,000 records, and it's processing 100 records per minute, that will take a while.