How do I check if more than one record has been returned from DBI query in Perl?

Question

Hi,

I searched for this on the site already but couldn't locate anything that answered my question. Which brings me to the details:

I am querying database using a simple select query, realistically (given the scenario) it should never return more than about 5 to 6 records. I just want to check if more than one has been returned. And if more than one has been return act upon that (in an else statement) if only one has been returned then I progress with the script.

my $sql = qq { SELECT col1, col2, col3
                 FROM table
            WHERE col1 = 'foo'
          };

my $sth = $dbc->prepare_cached($sql) or die "Could not prepare statement: " . $dbc->errstr;

$sth->execute() or die "Could not execute statement: " . $sth->errstr;

# Not sure how to efficiently check for more than one row returned and still progress if true... This is my problem! I'm thinking a nested if to see if any rows were returned, and then progress with the check of one or more rows? But how can this me checked (like a count function?)
if (my $ref = $sth->fetchrow_hashref()) {

 # One row was returned...

} else {
 # More than one row was returned... Uh oh!
}

If you guys know of a thread that I couldn't locate that answers this question simply redirect my and I'll nullify this thread!

Regards, BorisTheBulletDodger!

Answer 1

Use one of the fetchall_* or selectall_* methods (see the DBI documentation), since you'll only have 5-6 at most. Then determine how many rows you got after all.

Answer 2

The only way to authoritatively determine whether more than one row has been found by a SELECT is to try to retrieve more than one row. DBI does provide the ->rows property, but it is only guaranteed to work for non-SELECT statements.

The answers to How can I check if a database query will return results? may also prove useful, even though it's not quite the same question.

Answer 3

my $sth = $dbh->prepare();
$sth->execute();

my $row = $sth->fetchrow_hashref();
my $second = $sth->fetchrow_hashref();

if ($second) {
  print "2+ rows\n";
} elsif ($row) {
  print "1 row\n";
} else {
  print "no rows\n";
}

Answer 4

my $sth = $dbh->prepare("SELECT col1, col2, col3 FROM tablename");
$sth->execute();
my $rows = $sth->fetchall_arrayref({});
if (@$rows == 0) {
    die "no rows returned";
} elsif (@$rows > 1) {
    die "too many rows returned";
}
my $row = $rows->[0];
print "col1 is $row->{col1}\n";