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.
Based on a quick look here, it seems that after you run
$statement->execute($arg)
you can access the row count via
$statement->rows
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.
In order to find out how many rows are in a result set you have exactly two options:
select count(*)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 :-)
It's a bit late, but if anyone uses ORACLE, here comes THE sweat solution:
SELECT
q.*,
ROWNUM DB_ROWNUM,
(SELECT max(ROWNUM) FROM ($sql)) DB_COUNT
FROM
($sql) q
$sql is your query, of course. Oracles optimizer is intelligent enough not to execute everything twice.
Now every fetched row holds the current row number (useful for paging grid row numbering) in DB_ROWNUM and the complete number of rows in DB_COUNT. You still have to fetch at least one row (so it isn't exactly the answer to the question above ;)), but the sweat use comes next:
It's also a very easy way to do start and limit in Oracle and still get the complete number of rows:
SELECT * FROM (
SELECT /*+ FIRST_ROWS($limit) */
q.*,
ROWNUM DB_ROWNUM,
(SELECT max(ROWNUM) FROM ($sql)) DB_COUNT
FROM
($sql) q
WHERE
ROWNUM <= $limit
)
WHERE
DB_ROWNUM > $start
With this, you can actually fetch only row 51 to 100 for the second page in your grid, but still have the real row number (starting from 1) and the complete count (without start and limit) in every fetched row.
CPAN says:
[...] or after fetching all the rows of a SELECT statement.
So doing something like this will probably work:
$sth->execute or die $sth->errstr;
say (scalar keys %{$sth->fetchall_hashref('id')}) . ' row(s).';
try this SQL solution, combine your SQL for the data with a count Statement.
select null, null, null, count(*) from tablex union select foo, bar, foobar, null from tablex
the first statement will have the count, and should be the first row (if not you can order by to get around it) then you can cycle through the rest of the recordset at your leisure.
I have dynamically generated a SQL and executing it. For me select count(*) does not seem to be an option because I have to re-generate the query again. Following approach looked clean for me. But you have to issue s $h->execute() once again to retrieve row data.
$h->execute() or die "ERROR: Couldn't execute SQL statement";
$rowcount_ref = $h->fetchall_arrayref(0);
$rowcount = scalar (@{$rowcount_ref});
-- Shaakunthala
If you want to know how many rows are there before walking through all of them, a MySQL-specific solution could be FOUND_ROWS().
In your first query, add SQL_CALC_FOUND_ROWS right after SELECT. Then do SELECT FOUND_ROWS();, and you have access to the row count right away. Now you can decide whether you want to walk through all the rows, or the best way to do it.
Note that having LIMIT in the query will give you the total number query would have returned without LIMIT.
As others already said, you really need to get the rows to find out if there are any. If you need to know before starting a loop on every row, and if the expected results are not huge, you can get all results into an array, and then check that.
I recently used something like that:
foreach my $table ( qw(ACTOR DIRECTOR PRODUCER WRITER) ) {
my $sth = $dbi->prepare(qq{SELECT * FROM $table WHERE DESCRIPTION != TRIM(DESCRIPTION)})
or die $dbi->errstr;
$sth->execute or die $sth->errstr;
my @rows = @{ $sth->fetchall_arrayref() };
next unless @rows;
foreach my $row (@rows) {
print join(", ", map {qq("$_")} @{$row}), "\n";
}
}
I'd just let the database do the counting instead. If all you want is the number of rows. Find all the rows for january 2018.
$year='2018';
my $qry = "SELECT COUNT(`my_key`) FROM `mtable` WHERE `my_date` LIKE '$year-01-%';";
my ($count) = $dbc->selectrow_array($qry);
I think this can be achieved pretty easily using references. The code blow below shows how it is not needed to go for re-execution of the query to fetch the rows after a count.
my $sth = $dbh->prepare("SELECT NAME, LOCATION
FROM Employees");
$sth->execute() or die $DBI::errstr;
$nrows = $sth->fetchall_arrayref() ;
print "Number of rows found :" . @$nrows . "\n";
#while (my @row = $sth->fetchrow_array()) {
foreach my $row(@{$nrows}) {
my ($name, $location ) = @$row;
print " Name = $name, Location = $location\n";
}
I hope this satisfied the question made here.
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.
