whats the same function of FOUND_ROWS() in firebir

Question

dear all...i'm familiar with MySQL but not with Firebird.

i want to change my php page, from MySQL into Firebird query.But i got some difficulty during change command FOUND_ROWS(). is there someone who know whats the same function of FOUND_ROWS() in Firebird?

i have browsed in every sites but i have no answer. i'm stuck in this case.please help.

Answer 1

Similar to @Andrei K. answer.

Answer to your question: There's no equivalent in Firebird for FOUND_ROWS() MySQL function/statement.

Workaround: If you deadly want to know that number of rows, ask the engine to run a new query to compute the number of rows present for a special version of the first query. For simple queries, @Andrei K. answer is accurate, but for the general case, including queries with group by and having clauses use a query like this:

select count(*)
  from (your original query here) q1;

You have to exclude the first/skip and order by clauses if present in that original query. So, for a query looking this:

select first 10 skip 20 pd.productcode, extract(year from ph.purchasedate) year, sum(pd.quantity) year_quantity
  from purchase_details pd
       inner join purchase_header ph
          on ph.id = pd.purchase_id
 where ph.cancelled = 0
   and ph.salesman = 10
 group by pd.productcode, extract(year from ph.purchasedate)
having sum(pd.quantity) > 1000
 order by sum(pd.quantity) desc;

the found_rows equivalent query will be:

select count(*) 
  from (
        select pd.productcode, extract(year from ph.purchasedate) year, sum(pd.quantity) year_quantity
          from purchase_details pd
               inner join purchase_header ph
                  on ph.id = pd.purchase_id
         where ph.cancelled = 0
           and ph.salesman = 10
         group by pd.productcode, extract(year from ph.purchasedate)
        having sum(pd.quantity) > 1000
       ) q1

From my experience, this works for 99.9% of the queries.

Warning This approach is very inefficient, use at your own risk.

Answer 2

There is no way in Firebird to know how many rows would return query without running the query and fetching all the data or running query like this:

SELECT COUNT(*) FROM <your tables and conditions go here>

Answer 3

If you are using this FOUND_ROWS() to show something like showing Y rows out of X rows, see if your new version of the page can do without that information. The way with Firebird, reading the documentation appears to be, to run the query without the LIMIT first and then with the LIMIT. LIMIT being MySQL, not sure what is it called in Firebird. Then there is the SQL_CALC_ROWS you should translate from MySQL to Firebird.