Determine if string $ sth has strings without consuming them?

Question

With DBI there a way to determine if a processed handle statement for a SELECT statement will return any rows without extracting from it?

those. sort of:

 use DBI; ... my $sth = $dbh->prepare("SELECT ..."); $sth->execute; if (..$sth will return at least one row...) { ... } else { ... } 

and I want to run the test $sth will return at least one row without executing the fetch method on $sth .

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

Answer 1

$ sth-> rows is still your best bet. As you said, just check if it is greater than 0.

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

BESIDES! the DBI documentation says it is not reliable for individual records until all rows have been extracted. It is not possible to determine how many rows will be selected in advance. If you need to know this, we recommend that you first do

 select count(*) from <table> 

See this section of the DBI documentation .

Answer 2

I don't think there is any reliable way to check if any rows can be retrieved, in addition to being retrieved. Of course, once you select the first line, it will not be available for download, and you should hold it in your hand. But this is not difficult to handle.

I would usually think of using idioms as 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 } 

Answer 3

Instead of looking for something in the DBI, you can use SQL to do this. Just wrap SELECT in another SELECT clause with an EXISTS clause:

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

This is not portable SQL: FROM DUAL may look different on your RDBMS, your database may not support EXISTS and subqueries. However, most modern databases allow you to cook something like this.

This should be the most efficient way, because the optimizer may skip ignoring the order by groups, restrictions on an additional request. Check execution plans.

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

It is also possible, but the DB must check the entire data set to get the correct number of rows.

If you control the SQL constructor, you can reverse the order, group by (only if you don’t have a part), and apply a limit on top to select only one row.

Answer 4

Perhaps this is what you are looking for.

Active (logical, read-only)

The "Active" attribute is true if the handle object is "active". This is rarely used in applications. The exact meaning of the activity is currently somewhat vague. For a database descriptor, usually means that the descriptor is connected to the database ("$ dbh-> disconnect" sets "Active"). For a descriptor, an instruction usually means that the descriptor is “SELECT”, which can have more data to retrieve. (Retrieving all data or calling $ sth-> finish disables Active.)