CODEWITHSUNDEEP
perldbi
ErikR
ErikR

Reputation: 52039

Determine if $sth has rows without consuming it?

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.

Upvotes: 6

Views: 9457

Answers (4)

zgpmax
zgpmax

Reputation: 2847

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
}

Upvotes: 2

Dimanoid
Dimanoid

Reputation: 7279

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

Upvotes: 0

ruz
ruz

Reputation: 492

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.

Upvotes: 1

Ilion
Ilion

Reputation: 6872

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

Upvotes: 8

Related Questions