The problem seems to be that the Oracle Driver does not implement the
rows function.
Quote from 'perldoc DBI':
""rows""
$rv = $sth->rows;
Returns the number of rows affected by the last row
affecting command, or -1 if the number of rows is not
known or not available.
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 you might try:
$tbh->execute();
while (my @row = $tbh->fetchrow()) {
...
}
fetchrow returns a false value (the empty list) when the table is
exhausted.
hope this helps,
cr
On Tue, 15 May 2001 17:18:36 +0000, Ang Sei Heng said:
> 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;
> }
>
> my $i = 0;
>
> while( $i < $tbh->rows ){
> my @dbres = $tbh->fetchrow_array;
>
> my $strbuf = join ' -- ', @dbres;
>
> print $strbuf;
>
> $i++;
> }
>
> $tbh->finish;
> $dbh->disconnect;
>
> -- ## End Perl Code ## --
>
> The program executed and return $tbh->rows as '0' when there
> more than 10000 records in the table 'street'.
>
> However, when I did the following:
>
> -- ## Begin Perl Code ## --
>
> my $sqlcmd = "select * from street";
>
> my $tbh = $dbh->prepare($sqlcmd);
>
> $tbh->execute;
>
> while( 1 ){
>
> my @dbres = $tbh->fetchrow_array;
>
> if( !defined(@dbres) ) {
> print "END OF RECORDS\n";
> last;
> }
>
> my $strbuf = join ' -- ', @dbres;
>
> print $strbuf;
>
> $i++;
> }
>
> $tbh->finish;
> $dbh->disconnect;
>
> -- ## End Perl Code ## --
>
> The program return all the entries in the table.
>
> Very strange...?? Anyone has any idea what coz'
> my perl script to have such problem. Or... this
> is actually the 'correct' way to get the table
> records from Oracle??
>
> Any perl guru/expert can help me on this? If I want to
> find out how many rows return, how should I go
> about and do it??
>
> Thanks in advance,
>
> Regards,
> Sei Heng
>
> --
> Cybersource Pte Ltd
> 745 Toa Payoh Lorong 5 #03-02
> HBM Building Singapore 319455
> Tel: (65) 3580575
>
> >> I rather be free in Hell then a slave in Heaven <<
>
