Subject Re: [IBO] query.invalidatesql and query.refresh
Author Markus Ostenried
At 07:06 Thursday, 10.07.2003 +0000, you wrote:
>query.invalidatesql and query.refresh
>am I right to say that the refresh method is useless without calling the
>invalidatesql first?

No, Refresh ensures that the data in the query's buffer is refetched from
the server. InvalidateSQL tells the query that it has to re-prepare its SQL
the next time it wants to fetch data from the server. With a call to
Refresh after InvalidateSQL you make sure that the changed SQL property
(see below) will be used immediately.

>and Iam confuse on the sqlwhereitems property.
>I have a code like this for the onclick of a button
>
>query.sqlwhereitems.clear; // line 1
>query.sqlwhereitems.add(' date >= ''1/1/03'''); // line 2

You must move these two lines to the OnPrepareSQL event of your query.
Because after a call to InvalidateSQL the query will be re-prepared the
OnPrepareSQL will be called. There you can adjust the SQLWhereItems as you
want.

>query.invalidatesql; // line 3
>query.refresh; // line 4

These stay in the ButtonClick event.

>well this code doesn't work it doesn't filter the record from 1/1/03
>and beyond. but if the put the line 1 and line 2 on the onprepare
>event of the query it works. doesn't this means that sqlwhere,
>sqlorder, and etc and only be manipulated in onprepare event?

Yes, that's the way it is designed.

>I try to investigate what happen to my sqlwhereitems, and it seems
>that the items in sqlwhereitems were erase when the qeury reach on
>the onprepare event.

Everything you write to the SQLWhereItems in OnPrepareSQL will be added to
the query's where clause. It's empty each time so you don't have to change
anything if you want to leave the SQL as you set it originally.

So what you should have is something like this:

procedure TForm1.QueryPrepareSQL( .. );
begin
query.sqlwhereitems.clear; // line 1
query.sqlwhereitems.add(' date >= ''1/1/03'''); // line 2
end;

procedure TForm1.ButtonClick( ... );
begin
query.invalidatesql; // line 3
query.refresh; // line 4
end;

But if all you want to do is changing the date value of your query you
should better use a parameter instead because assigning different parameter
values is much faster than changing the SQL property and then re-preparing
the query. To use a parameter you need a SQL statement like this:

SELECT YourField FROM YourTable
WHERE YourDate >= :DateParam

Then ButtonClick should look like this:

procedure TForm1.ButtonClick( ... );
begin
query.ParamByName( 'DateParam' ).AsString := '1/1/03';
query.Refresh; // *1*
end;

*1*) only needed if query.RefreshOnParamChange is set to false.

Only use the OnPrepareSQL event to change the SQL if you can't achieve what
you want with parameters.

HTH,
Markus