At 12:11 PM 07-11-02 +0000, you wrote:

>Hi again:
>
>Thanks for your answer (very fast).
>
>The table on which I want to use SearchinLinks is defined in Interbase
>with columns not using "NOT NULL", but there are no records with a NULL
>value on it. If I make a little program modeled as the SearchingLinks
>sample that comes with IBO I don't get the expected results. When I
>reorder a column that hasn't got "NOT NULL" on its definition all data in
>the dataset "disappears". I use the following code in the OnPrepareSQL
>(all columns are of type text):
>
>procedure TfrmRelacionar.query1PrepareSQL(Sender: TIB_Statement);
>const
> START = 'STARTING WITH';
> CONT = 'CONTAINING';
> WHERECLAUSE ='UPPER(%s) %s :%s';
>begin
> with query1 do begin
> if RadioGroup1.ItemIndex = 0 then
> SQLWhereItems.Add(Format(WHERECLAUSE, [OrderingLink, START,
> OrderingLink]))
> else
> SQLWhereItems.Add(Format(WHERECLAUSE, [OrderingLink, CONT,
> OrderingLink]));
> end; { with query1 do...}
>
>But if the OnPrepareSQL is not used and I reorder the columns (even those
>with NULLs) everything works fine.
>
>What could be the problem in this case?. I think this a problem in IBO.

When you change the SQL, you actually cause the dataset to recreate
itself. All datasets are the encapsulation of a SELECT statement and, by
changing the statement, you are effectively causing a new dataset object to
be created. IBO mitigates the impact of this with SQLWhereItems but it's
still a fairly radical renewal that takes place.

In order to have the OnPrepareSQL event fire, you have to make sure to
create a condition where the dataset actually knows it needs to call
Prepare. So, in some preceding user event (perhaps your radiobutton click)
you need to do this:

...
if query1.Prepared then
query1.Unprepare;
...
Once the query is unprepared, I think the OrderingLink property becomes
inactive until the Prepare code fires and reactivates it - and this will
not occur until *after* the OnPrepareSQL event. Probably you should at
least test to see whether OrderingLink contains a non-empty string; but it
would probably be safer to access the OrderingField
(OrderingField.FieldName) property, since OrderingField is the actual
TIB_Column object reference.

Also, once you do get this event firing, your current code is going to
build up into a longer and long string of increasingly impossible search
criteria. You need to call SQLWhereItems.Clear before adding the altered
criteria.

Helen