On 10 Sep 2001, at 14:39, Paul Maine Jr. wrote:
> I am writing a Perl/DBI application. I need to initially issue a
> select statement and then determine if any records are returned. Could
> someone provide me with an example of how to determine if no records
> are returned from a select statement.
>
AFAIK the only way to determine the number of rows returned from a
SELECT-satement in a safe and driver-independent manner is to fetch
them all and count them. The "classic" approach would be
<perlcode>
...
get yourself a table name, a where clause, and a connection string
here
...
my $dbh = DBI->connect($your_cennection_string here, etc...
{RaiseError => 1}) or die DBI->errstr();
my $sql = "SELECT * FROM $table WHERE $where_clause";
my $sth = $dbh->prepare($sql);
$sth->execute;
my $number_of_rows = 0;
while (my @row = $sth->fetchrow_array) {
# do something or not
$number_of_rows++;
}
die "No rows returned!\n" if $number_of_rows == 0;
</perlcode>
A syntactically brute force approach can squeeze that into virtually
one line using one of the database handle's selectall methods;
<perlcode>
my $dbh = DBI->connect($your_cennection_string here, etc...
{RaiseError => 1}) or die DBI->errstr();
die "No records found!\n" if scalar @{$dbh-
>selectall_arrayref("Your_select_statment_here_verbatim")} == 0;
</perlcode>
I suggest that you choose something in between these variants,
depending on how many rows you expect to be returned and what you
want to do with them. As alternatives, i) your database driver may
provide a private method or attribute for your task, ii) you can use
the statement handle's rows()-method (which you should test first and
which is not recommended for SELECT-statements) or iii) you first
launch a "SELECT COUNT..." query and check the result, if your DBMS
supports this sql syntax (most do). All these alternatives are not
100% portable between DBMSs, the pure DBI approaches are.
HTH
Bodo
