Hi,

I tried this query:

with MyQuery do
  begin
    ParamByName('FSearch').asString := '%' + edSearch.text + '%';
    if not active then
      open
    else
      refresh;
  end;

instead of this:

with MyQuery do
  begin
    close;
    ParamByName('FSearch').asString := '%' + edSearch.text + '%';
    open;
  end;

Only the 2nd one works if executed more than once. Is this the expected
behaviour?

Thanks Uli

Uli


If edSearch.text changes each time then I'd say yes. My understanding is that queries can be refreshed ie reapply the same WHERE clause but essentially you're asking for a different query.

Roy Lambert [Team Elevate]

Uli,

Also, for better performance, if you are repeatedly executing the same query
with different parameter values you should explicitly prepare the statement
before the first "open" call and explicitly unprepare it when it is no longer
needed. That will avoid the query to be repeatedly prepared and unprepared each
time it is opened and closed.

--
Fernando Dias
[Team Elevate]

Fernando,

> Also, for better performance, if you are repeatedly executing the same
> query with different parameter values you should explicitly prepare the
> statement before the first "open"

I am doing so with all queries that are frequently used. Same in this case.
Nevertheless the refresh does not work.

Uli

Roy,

> If edSearch.text changes each time then I'd say yes. My understanding is that queries can be refreshed ie reapply the same WHERE clause but essentially you're asking for a different query.

Sure edSearch.text changes each time.

Uli

Uli,

> Nevertheless the refresh does not work.

Well, it works - it just doesn't do what you was expecting it to do
The query isn't executed again with new parameter values when you call refresh;
only the rows that changed in the underlying table(s) are updated in the result
dataset after a call to Refresh.

--
Fernando Dias
[Team Elevate]

Fernando,

> only the rows that changed in the underlying table(s) are updated in the
> result dataset after a call to Refresh.

Sorry, but I don't understand what you mean. The query produces a result
dataset. When changing a parameter and executing the query again, the
result dataset has changed.

The question now is whether or not a refresh is sufficient to show the
changed result dataset or a close/open action has to be done.

I remember the same problem with stored procedures some time ago. There
it was a bug.

Uli

Uli,

<< The question now is whether or not a refresh is sufficient to show the
changed result dataset or a close/open action has to be done. >>

You have to do a close/re-open.  Changing the parameter value and calling
Refresh does nothing but refresh the result set using the older parameter
values.  Parameter values are not re-assigned without re-executing the
query, which is what Open does, but Refresh does not.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> You have to do a close/re-open.  Changing the parameter value and calling
> Refresh does nothing but refresh the result set using the older parameter
> values.  Parameter values are not re-assigned without re-executing the
> query, which is what Open does, but Refresh does not.

Ok, clear answer. Thank you.

Uli

Tim,
> You have to do a close/re-open.

Do I remember correctly that this is different with stored procedures?

Uli