Subject RE: [IBO] Re: Ambiguous field name between tables ...
Author Helen Borrie (TeamIBO)
At 10:25 AM 14-08-02 -0400, you wrote:
>I'm not familiar with the use of the colon in sql, but the line, WHERE CO.CD
>= :CD, bothers me. Should not :CD be, perhaps, :EU.CD?

Edson's statement was OK. He later reported that deleting and replacing
his IBOQuery solved it. His problem was no doubt due to an invalid TField
object being left as an artifact of a structure change. If that was the
case, he could have fixed it by just deleting and recreating the persistent
fields. (Native IBO doesn't have this problem, because it doesn't use
persistent fields..)
When you use


in a Delphi query, you are telling the API to prepare the query in
expectation of receiving a parameter, which is always a constant. You
can't ever pass a column identifier as a parameter.

IBO creates the Params[] array on the basis of input parameters included in
the SQL statement.

Parameterised queries are the absolute "meat and drink" of IBO. Ideally in
an IBO app you would avoid assigning an entire SQL statement at runtime
(except with totally ad hoc user-defined queries of course) because that
causes major cleanup to occur before the object gets totally renewed with
the new statement. A parameterised statement is a template for any number
of queries which will be fired off that statement object. The portion

SELECT <list of columns> from aTable [JOIN.....]
can be a stub containing only the part of the query that defines the output
set. In many cases, you will include the parameterised WHERE clause, and
you might have an ORDER BY or GROUP BY clause as well, e.g.
SELECT <list of columns> from aTable [JOIN.....]
WHERE somecol = :somecol
AND anothercol = :anothercol

At run time, when you pass actual values into the input parameters, using
Fields[], FieldByName or one of several other methods, IBO does a lot of
magical things in its parser in order to generate the exact SQL statement
that goes across to the server. If the structure of the output isn't going
to change from one usage of the query to another, the preparation sequence
occurs only the first time. All subsequent times, it simply swaps in the
new parameter values using the same resources it set up the first time.

In some cases, you *do* want to change the criteria for output set, to
limit it in a different way to what you did the previous time (different
columns wanted in the WHERE criteria, or ordering the set on a different
column - although not the "main stub" (the SELECT portion that defines the
output set).

This will require invalidation of the statement and a repreparation to set
up the changed criteria. For these cases, IBO has the OnPrepareSQL event,
in which your run-time code can define the new WHERE and Ordering items, by
assigning clauselets to the SQLWhereItems collection and the SQLOrder
property. IBO takes care of the invalidation for you. When you call
Prepare after that, IBO rebuilds the "template" statement, ready for you to
assign values to the new set of parameters.

This is less costly on resources than what the BDE, DBXpress or IBX do -
they have nothing like SQLWhereItems, so there is no option but to do a
total statement swapout/swapin, even if the "stub" of the query isn't going
to change.

Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at