Determine if $sth has rows without consuming it?

perl dbi
ErikR picture ErikR · Jan 19, 2012 · Viewed 8.7k times · Source

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

Ilion picture Ilion · Jan 19, 2012

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

Related questions

How do I know how many rows a Perl DBI query returns?

I'm trying to basically do a search through the database with Perl to tell if there is an item with a certain ID. This search can return no rows, but it can also return one. I have the following code: …

Read More
How do I connect with Perl to SQL Server?

I have a user id, password, database name and datasource details. I want to connect with Perl to a MSSQL server. I just used the following snippet, but I am getting an error. #!/usr/bin/perl -w use strict; use …

Read More
Fetch all records using Perl DBI

I have a Perl script that uses a DBI connection. I open and read the SQL script file using a sub routine. I am printing only one record, where I should have two more (three records total). How do I …

Read More