How can I get a row count in DBI without running two separate calls to process?

Question

I'm running DBI in Perl and can't figure out how, when I run a prepared statement, I can figure out if the returned row count is 0.

I realize I can set a counter inside my while loop where I fetch my rows, but I was hoping there was a less ugly way to do it.

Answer 1

Based on a quick look here, it seems that after you run

$statement->execute($arg)

you can access the row count via

$statement->rows

Answer 2

Rows definitely does vary depending on the database/driver version it seems. I would definitely look to a piece of logic there that dealt with unexpected results.

Answer 3

The "caveat" in the documentation (linked to in a comment on another answer) is important, and provides the real, correct answer:

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.

Answer 4

In order to find out how many rows are in a result set you have exactly two options:

1. select count(*)
2. Iterate over the result set and count the rows.

You can introduce some magic via a stored procedure that returns an array or something more fancy, but ultimately one of those two things will need to happen.

So, there is no fancypants way to get that result. You just have to count them :-)