RE: Number of rows from SELECT (was Oracle error handling)

From: Srikanth (Srikanth.Madani_at_vodafone.com)
Date: 02/24/05 

Date: Thu, 24 Feb 2005 15:01:54 +0100
To: <dbi-users@perl.org>, "susan lam" <susanzlam@yahoo.com>

susan lam wrote on Donnerstag, 24. Februar 2005 14:09

> $sql = qq {select * from mytable};
> $sth = $dbh->prepare($sql);
> $sth->execute;
> $dbh->disconnect;

> Also, if mytable does exist, how can I obtain the row
> count returned from the above query and assign it to a
> variable?

>From the docs: <http://cpan.uwinnipeg.ca/htdocs/DBI/DBI.html>

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

One alternative method to get a row count for a SELECT is to execute a
``SELECT COUNT(*) FROM ...'' SQL statement with the same ``...'' as your
query and then fetch the row count from that.
 

Cheers,
Srikanth Madani

All vulgarity is due to the incapacity to resist a stimulus.
-Nietzsche/Twilight of the Idols

Relevant Pages

  • Re: Select/Join query help (dropdown box?)
    ... Your select query is fetching 9 columns but you're only listing two. ... Prev by Date: ...
    (comp.lang.php)
  • Detail section problem.
    ... My query is fetching ... multiple results but it allocates only the last result to the text fields. ...
    (microsoft.public.access.reports)
  • Re: DBI equivalent of Oraperl ora_titles function !
    ... I need it because I want to get the titles of a select query ... into an array without fetching the row values from the database. ...
    (perl.beginners)
  • Re: Affected Rows
    ... > One alternative method to get a row count for a SELECT is to ... > ``...'' as your query and then fetch the row count from that. ...
    (perl.dbi.users)
  • Re: Data type conversion
    ... "Bryan Linton" wrote ... ... How are you fetching the data: MS Query, ADO, SQL.REQUEST, etc? ...
    (microsoft.public.excel.programming)