How to fetch the size of the resultset from a DBI query?

Loc_Nar has asked for the wisdom of the Perl Monks concerning the following question:

How would I fetch the size (number of records) of the resultset of a query?

my $dbh = DBI->connect("dbi:mysql:db", "user", "pass",
              { AutoCommit => 1, RaiseError => 1})
                or die &error_out;
my $qry = $dbh->prepare("SELECT location FROM people")
                or die &error_out;  
$qry->execute or die &error_out;
[download]

I want to know the number of locations returned.

$qry->give_me_the_size_of_the_resultset; ?  :)
[download]

Loc_Nar

Comment on How to fetch the size of the resultset from a DBI query? Select or Download Code

Replies are listed 'Best First'.
Re: How to fetch the size of the resultset from a DBI query? by runrig (Abbot) on Sep 12, 2001 at 23:17 UTC
`$sth->rows` is one way, but it is inconsistently supported in the various DBD's. Some will return a meaningful value before any fetches, some won't, and some don't support it at all, I think. The most reliable way is to count the records yourself as you're fetching them, or do a 'select count()...' before you do the main select (if you absolutely must* know the count before you do the fetching, that is).	[reply] [d/l]
Re: How to fetch the size of the resultset from a DBI query? by thpfft (Chaplain) on Sep 12, 2001 at 23:25 UTC
in sql: `"SELECT COUNT() FROM people"` doing the same in perl would be horribly inefficient, unless you're going to do something else with the results afterwards. here's a minimal version anyway, in case for some reason select count isn't possible: `my $records = $dbh->selectcol_arrayref('SELECT location FROM people'); my $peoplecount = scalar(@$records);` [download] update:* curses... redundant again.	[reply] [d/l] [select]
Re: Re: How to fetch the size of the resultset from a DBI query? by seesik (Initiate) on Sep 12, 2001 at 23:37 UTC
be extremely careful with this approach, as it actually caches your result set in memory. if you're querying against 400 GB of data, you might not want that entire resultset stored locally ;) revision uno: hmm, that's a `selectcol_arrayref` which only caches the first column's values IIRC (which still might be relatively large), not `selectall_arrayref` which caches the entire resultset	[reply] [d/l] [select]
Re: RTFM (Was: How to fetch the size of the resultset from a DBI query?) by htoug (Deacon) on Sep 13, 2001 at 10:45 UTC
I do wish people would RTFM. perldoc DBI explicitly states: `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. 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.	[reply]
Re: How to fetch the size of the resultset from a DBI query? by Loc_Nar (Novice) on Sep 13, 2001 at 20:59 UTC
I had, in my brief research through some DBI documentation, noticed that the `$sth->rows;` action was deemed unreliable for select operations. I think that a `SELECT COUNT(*)` query prior to my `SELECT` query will be the most intelligible path to take. Thanks all for your help.... Loc_Nar	[reply] [d/l] [select]