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.

Anemology

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.

Manion

It may not work depending on the driver, for example with DBD::Oracle, $sth->rows returns the number of rows affected only for updates, deletes and inserts but -1 for selects (search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#rows) – Servetnick

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
}

Halona

Or just use $sth->fetchall_arrayref() directly as described in https://mcmap.net/q/888966/-how-can-i-get-a-row-count-in-dbi-without-running-two-separate-calls-to-process if the expected results would be of a reasonable size. – Squama

Answer 3

Instead of looking something in DBI you can use SQL for that. Just wrap SELECT into another SELECT with EXISTS condition:

SELECT 1 FROM DUAL WHERE EXISTS ( SELECT ... );

It's not portable SQL: FROM DUAL may look different on your RDBMS, your DB may not support EXISTS and subqueries. However, most modern DBs allow you to cook something like this.

It should be most effective way as optimizer can skip ignore order by, group by, limits on the sub query. Check execution plans.

SELECT COUNT(*) FROM ( SELECT ... )

Is also possible, but DB have to check whole data set to get you correct number of rows.

If you control SQL builder then you can drop order by, group by (only if there is no having part as well) and apply limit on top of that to select only one row.

Flooring

Answer 4

Maybe this is what you looking for.

"Active" (boolean, read-only)

The "Active" attribute is true if the handle object is "active". This is rarely used in applications. The exact meaning of active is somewhat vague at the moment. For a database handle it typically means that the handle is connected to a database ("$dbh->disconnect" sets "Active" off). For a statement handle it typically means that the handle is a "SELECT" that may have more data to fetch. (Fetching all the data or calling "$sth->finish" sets "Active" off.)

Blunder

No. As you quote in your answer, Active typically means that a statement handle may have more rows to fetch. So, even an executed statement that we know a priori will return no rows will yet (under typical drivers like DBD::mysql) report Active until the first (and final) fetch() is called. – Creamer