On Tue, 15 May 2001, Ang Sei Heng wrote:
> Hello Everyone,
>
> Recently I did some database connection to Oracle via DBD::Oracle.
>
> I manage to conncec to server, the follow statement does not
> work:
>
> -- ## Begin Perl Code ## --
>
> my $sqlcmd = "select * from street";
>
> my $tbh = $dbh->prepare($sqlcmd);
>
> $tbh->execute;
>
> if( $tbh->rows == 0 ){
> # Nothing return from table street...
> print "No data return\n";
> $tbh->finish;
> $dbh->disconnect;
> exit;
> }
>
...
A snippet from perldoc DBI:
Generally you can only rely on a row count after a
non-select execute (for some specific operations like
update and delete) or after fetching all the rows of a
select statement.
For select statements it is generally not possible to
know how many rows will be returned except by fetching
them all. Some drivers will return the number of rows
the application has fetched so far but others may
return -1 until all rows have been fetched. So, use
of the rows method, or $DBI::rows, with select
statements is not recommended.
So basically, rows isn't too useful for an SQL select.
If you want to check for zero rows to select different logic, eg.
displaying "No rows found" vs starting a HTML table, you need to rearrange
your logic a bit, maybe something like:
my $sth = $dbh->prepare($yourselect) or die;
$sth->execute() or die;
my @row;
if (@row = $sth->fetchrow_array) {
print "<table>\n";
while (@rows) {
print "<tr>",map { "<td>",encode_entities($_),"</td>" } @row,"</tr>\n";
@row = $sth->fetchrow_array;
}
print "</table>\n";
}
else {
print "<p>No rows found</p>\n";
}
If you need to display the number of rows found before the data, you will
probably need to either do 2 queries, one to get the count and one to get
the data, or copy the data to a temporary table.
--
Tony
