We have spent the last few weeks testing the upgrade to IBO from BDE
with mixed results. The project is extremely large with around 500
screens and around 100 reports.

The main problem we have encountered is the insistance of IBO to
prepare everything. With the BDE, we have created many queries with
new generated fields to allow a user-operated tick box in a grid.
By generated fields I mean: "Select Name, ' ' FieldOne, ' ' FieldTwo
from aTable" - the last two fields are generated.

This gives a multi-select capability. Provided we made the query
CachedUpdates = true, and dropped an UpdateSQL on the form, it was
quite happy to allow us to edit the record (ie; allow the user to
tick
the box on any line), no matter what the code was in the UpdateSQL,
or
even if there was some.

We never updated the Query so it never changed the database and was
only used as a selection tool. Perhaps there may have been better
ways, but this worked and is used extensively. In the conversion we
found that IBO distregarded the UpdateSQL (something of a lack of
understanding on our side no doubt) so we moved any UpdateSQL code
into the IBOQuery's own EditSQL etc., creating update code where
necessary.

We now find that IBO, specifically TIBOQuery, will always attempt to
prepare any query to the database. Is there a way that we can tell a
query not to look at the database after the fetch?

This is particularly puzzling as we now find a query that has two
generated fields where one is used as a filter causes an error. For
example, the SQL:

Select Name, ' ' FieldOne, ' ' FieldTwo from aTable

and the Filter: FieldOne <> 'T'

causes an error where FieldOne is not found. In this case we are
controlling FieldOne programatically depending on other user
selections elsewhere.

Also. A fellow programmer asked a question on this forum that was
never answered. This relates to the query being re-written
incorrectly
and sent back to the database resulting in an error whenever we asked
for a RecordCount.

The question is repeated below. Again, note the generated field
'Selected':

The IBO Monitor/Trace tool reports the following SQL on a TOBIQuery.

-----------
select pl.Packline, Var.Variety Variety, Gm.Ref GrMeth,
Maturityindex.Ref Maturity,
DateReceived, Pl.PacklineID, 'T' selected,
(select count(distinct rdt.pagenumber) from rejectdetail rdt where
rdt.packlineid = pl.packlineid) pagn
from packline pl
join GrowingMethod Gm on Gm.GrowingmethodID = Pl.GrowingmethodID
join Variety Var on Var.VarietyID = Pl.VarietyID
join MaturityIndex Mat on Mat.MaturityIndexID = Pl.MaturityIndexID
where (select count(distinct rdtl.pagenumber) from rejectdetail rdtl
where rdtl.packlineid = pl.packlineid) <> 0
and Pl.PackhouseID = ? /* PackhouseID */
and Pl.GrowerID = ? /* GrowerID */
ORDER BY packline ASC
-----------

Asking for RecordCount causes an error. For example, place button on
form with the following code:

ShowMessage('number of records: ' + IntToStr(QryLines.recordCount) );

This will produce an error reporting the following in the IBO
Monitor/trace tool.

-----------
PREPARE STATEMENT
TR_HANDLE = 28398556
STMT_HANDLE = 28397488

SELECT COUNT( DISTINCT rdt.pagenumber) from rejectdetail rdt where
rdt.packlineid = pl.packlineid) pagn )
from packline pl
join GrowingMethod Gm on Gm.GrowingmethodID = Pl.GrowingmethodID
join Variety Var on Var.VarietyID = Pl.VarietyID
join MaturityIndex Mat on Mat.MaturityIndexID = Pl.MaturityIndexID
where (select count(distinct rdtl.pagenumber) from rejectdetail rdtl
where rdtl.packlineid = pl.packlineid) <> 0
and Pl.PackhouseID = ? /* PackhouseID */
and Pl.GrowerID = ? /* GrowerID */
FIELDS = [ Version 1 SQLd 1 SQLn 1
< SQLType: 496 SQLLen: 4 > = -1 ]

ERRCODE = 335544569
-----------

It seems to attempt to re-write the query incorrectly.

It seems that IBO removes the first open bracket of the field level
sub-select, then adds a closing bracket to the end of the line, after
the alias "pagn" and attempts to prepare the remainder of the now
broken query.

It doesn't like the closing bracket on the first line of the query.
Note that this closing bracket was added by IBO and was not part of
the original query.

This can be re-created at any time on this query and worked without
fail using the BDE. The query is cached and contains update queries
that are never processed as the cache is never updated, but used for
a selection tool only.

Can you please confirm either a problem at our end in what we are
doing, or if this is a problem at your end.

Many thanks

Steve Peacocke
steve@...