I'm trying to paginate some result sets in the Firebird RDBMS using DBD::InterBase, and having some trouble. I would like to be able to use the $sth->rows method to get the rows returned by a select before I fetch all of the rows (there could be quite a lot of rows, and I only want to fetch the rows that fit my pagination factor). Firebird/Ibase does not support that; it always returns -1 for $sth->rows until you have fetched the rows. So ... I have a stand alone select that will count(*) the rows first, and store that so I can display a *useful* message to the end user.
Now, when I paginate through the 2nd, 3rd, and so on pages I have code that will create the First, Skip clause correctly. but I can't seem to get a count(*) on those returned rows at the same time. This SQL returns nothing, for example: SELECT FIRST 10 SKIP 10 COUNT(*) from foo_table Should I give up on code re-use and just put some if() blocks in my code and execute the paginated queries without using a separate SELECT count(*) query, and then just manually count the rows after I fetch them all ? Been a long time MySQL user, and admittedly Firebird has been frustrating initially. What should be simple stuff seems very difficult and convoluted to get done in Firebird. (Hopefully what is more difficult to get done in MySQL will be easier in Firebird ... ) Are there any folks out there that use Firebird and the DBD::InterBase driver that can comment on getting counts from paginated Firebird tables and views ? Is there an easier way to do all of this ? Or does everyone just fetch all the rows and work around this limitation with select count(*) hacks when needed ? I found this doc which looked like it would be helpful at first ... http://delphi.weblogs.com/stories/storyReader$387#MakingReasonableRequests But then the author says: "In my opinion there are few if any legitimate uses for TDataset.RecordCount, and only slightly more for the SQL aggregate function COUNT." Any ideas ? -- + Jon Larsen: Chief Technology Officer, Richweb Inc. + Richweb.com: Providing Internet-Based Business Solutions since 1995 + GnuPG Public Key: http://richweb.com/jlarsen.gpg + Business: (804) 359.2220 x 101; Mobile: (804) 307.6939
