At 08:02 AM 26/07/2006, you wrote:

>Hi all,
>
>BDS2006
>FB2
>IBO 4.3.A
>
>I'm creating IBOQuerys, and I'm finding if the index selected in the
>plan doesn't have all of its key fields represented in the query's
>field set, it throws its nana.

IBO doesn't care about indexes. The problem probably is down to its
being unable to determine the KeyLinks.
-- How are you setting these?
-- Are you trying to make RequestLive true on a query of this type?

>I'm connecting to a Firebird 2.0 database.

That will cause various problems with IBO 4.3A, which is more than 3
years old and predates not just Fb 2.0 but also Fb 1.5.

>I've got a whole heap of queries of this type:
>
>SELECT EXTRACT (WEEKDAY FROM SAILDATE) AS SAILDOWORD,
>DOW (SAILDATE) AS SAILDOWDESC,
>COUNT (SAILDATE) AS SAILINGS,
>CAST (SUM (PAX) AS INTEGER)AS PAX,
>FROM SAILINGDATA
>WHERE SAILDATE BETWEEN :D1 AND :D2
>AND DEPARTUREPOINT = :P
>
>GROUP BY 1, 2
>ORDER BY 1
>
>
>The PLAN looks like
>PLAN SORT (SORT ((SAILINGDATA INDEX (PK_SAILINGDATA))))
>This index comprises two fields: SAILDATE and BRSFRSCB.

Indexes in Firebird are not like indexes in Paradox. Firebird is
choosing this index because SAILDATE is exposed on the left side and
your search path includes a condition that can use that index. It's
serendipity that the optimizer has decided to use it for the sort and
it's actually not useful for the sort. (If you had an appropriate
expression index, you might get a useful choice for the sort...)

>If I try and compile as is, I get a complaint that field SAILDATE is
>not found in the query. If I add two calculated fields called SAILDATE
>and BRSFRSCB, and ignore those fields, my app will compile and the
>query will run normally.

This is the IBO parser objecting, not the database engine. On the
face of it, IBO should not object to SAILDATE, since it is a named
parameter in the WHERE clause. But this could well be due to the
ancientness of the TDataset descendants you are trying to use. Those
classes have acquired a lot more smarts from the TIB_Dataset since
IBO 4.3A. I guess if adding the dummy fields gets the desired effect,
it's as good as anything.

>Similarly, if I have a query of the type
>SELECT COUNT(BKMFBNUM)AS NOBOOKINGS FROM BM,
>where BKMFBNUM is an index, I get the same complaint. If I change my
>statement to read AS BKMFBNUM the complaining stops.

What do you mean by "where BKMFBNUM is an index"? It's not valid SQL
to put a count on an index. Did you really mean "where BKMFBNUM is
an indexed column"? If the latter, having an index on a column makes
no difference to COUNT(), although it might make a difference on the
server side for sorting (GROUP BY and ORDER BY). IBO simply doesn't
know or care whether you have it indexed or not.

>Now, I'm probly doing something wrong. How does one avoid these errors?
>
>Also, my setup is along the lines of
>IBOQuery -> dataSetProvider -> clientDataSet -> dataSource
>
>... and I think I saw someone in another thread frown at this sort of
>setup. Is this bad? Why? I'm enjoying the convenience of some of the
>clientDataSet features, but I need to make this work properly.

In the past we've seen people using ClientDataSets "for the
convenience of some of the ... features" when in fact the IBO
datasets already implement those features. Problems seem to occur
when the CDS asks to do something that the IBODataset implements
differently to how CDS expects its providers to behave. So it's a
bit of a Pandora's box when you're using CDS for purposes other than
to provide a remote client data layer for a data access layer that's
operating on the server, i.e. an n-tier app.

It seems to be an issue when people are migrating from IBX to IBO,
because the IBX dataset implementation needs the extra CDS layer in
order to implement bi-directional scrolling. I also recall
situations where the CDS methods fooled around with the keyfields and
made (otherwise good) IBO KeyLinks appear to be invalid....

Helen