Subject | Re: [IBO] query.invalidatesql and query.refresh |
---|---|
Author | Markus Ostenried |
Post date | 2003-07-10T07:48:45Z |
At 07:06 Thursday, 10.07.2003 +0000, you wrote:
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.
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.
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
>query.invalidatesql and query.refreshNo, Refresh ensures that the data in the query's buffer is refetched from
>am I right to say that the refresh method is useless without calling the
>invalidatesql first?
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.You must move these two lines to the OnPrepareSQL event of your query.
>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
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 3These stay in the ButtonClick event.
>query.refresh; // line 4
>well this code doesn't work it doesn't filter the record from 1/1/03Yes, that's the way it is designed.
>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?
>I try to investigate what happen to my sqlwhereitems, and it seemsEverything you write to the SQLWhereItems in OnPrepareSQL will be added to
>that the items in sqlwhereitems were erase when the qeury reach on
>the onprepare event.
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