At 11:23 PM 5/08/2004 +0000, you wrote:

>Hi Rob,
>
>This is great help, many thanks!
>
>Do you know if this is a feature or bug?

Neither. There isn't a generic way to get the exact record count of a union
query. If you were not referring to the RecordCount property yourself,
then it's likely that you were pulling these records into a DBGrid. DBGrid
uses RecordCount to configure the vertical scrollbar. Getting an accurate
recordcount isn't an efficient thing to do in Firebird and InterBase at all
(needs select count(*) from...) but there simply isn't any SQL to perform
select (*) from a UNION query. So, the native DBGrid with the VCL's
dataset "out-of-the-box" can't display a UNION query.

>Is IBObject going to fix this in the future?

IBO can't "fix" it, because there is no generic way on the server to count
the rows from an InterBase or Firebird UNION query. For example, there is
no valid SQL syntax currently like

select count (*) from
(select EMP_NO, FIRST_NAME, LAST_NAME
from employee
where job_country = 'USA'
UNION
select EMP_NO, FIRST_NAME, LAST_NAME
from employee
where job_country = 'England' )

So, IBO tries to work around it instead.

To get an exact record count under these conditions, IBO needs to count all
of the eligible rows *after* they have arrived in the buffer. For small
datasets, the workaround is to set the AutoFetchAll property true. (For
large datasets, the delay waiting would be unacceptable: find a way to
restrict the size of the dataset if possible).

If it's the DBGrid that's causing your problem, set the dataset's
RecordCountAccurate property to false. Then IBO counts the rows in the
buffer for RecordCount and DBGrid is happy.

If you really want to read an accurate recordcount and avoid the exception,
set the RecordCountAccurate property of the dataset to False *and*
AutoFetchAll to true.

Helen


Helen