Determine if $sth has rows without consuming it?

Question

With DBI is there a way to determine if an executed statement handle for a SELECT statement will return any rows without fetching from it?

I.e. something like:

use DBI;
...
my $sth = $dbh->prepare("SELECT ...");
$sth->execute;

if (..$sth will return at least one row...) {
  ...
} else { 
  ...
}

and I want to perform the test $sth will return at least one row without performing any fetch method on $sth.

Note - I don't need the exact count of rows (i.e. $sth->rows), I just need to know if $sth->rows will be > 0.

Answer 1

$sth->rows is still your best option. As you said, just check if it's more than 0.

if ($sth->rows > 0){
  # magic here!
}

EXCEPT! the DBI documentation says this is not reliable for select statments until all rows have been fetched. There is not a way to determine how many rows will be fetched in advance. If you need to know this the advice is to first do a

select count(*) from <table>

See this section of the DBI documentation.

Answer 2

I don't think there's any reliable way of testing whether any rows can be fetched other than fetching them. Of course once you've fetched the first row, it's not then available to be fetched, and you have to hold it "in hand". But this isn't too hard to deal with.

I would generally think to use idioms like one of the following two:

...
$sth->execute;

my @rows;

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

if ($row) {
    do {
        # do something with $row
        ...

        $row = $sth->fetchrow_hashref();
    } while ($row);
} else {
    # No rows
}

or

...
$sth->execute;

my @rows;

while (my $row = $sth->fetchrow_hashref()) { push @rows, $row }

if (@rows) {
    # Some rows, now in @rows
} else { 
    # No rows
}