Using Params with recycled queries

[Mark Williams 14]

On intial setup I set up queries with SQL.Text and prepare them ready for use and reuse. Some of these queries use params.

 

On first usage the queries work as expected. When reused they simply are not fired at all.

 

As an example:

with DAC.FDQueryDocCats do
  begin
  	ParamByName('CASEID').AsInteger:=FProject.CurrCaseID;
  	execute;
  end;	

Works as expected on first use.

 

When it is run a second time using the debugger I can see that the correct CASEID value is being passed in and that the query appears to execute, however, it doesn't. The query dataset doesn't update. I have enabled tracing and on checking the trace it is clear that the query does not run at all.

 

I have tried "EmptyDatSet", "ClearDetails", but same effect.

 

If however, I do this:

with DAC.FDQueryDocCats do
  begin
	SQL.Text:=SQL.Text;
  	ParamByName('CASEID').AsInteger:=FProject.CurrCaseID;
  	execute;
  end;

all works well, however, I lose any advantage in having prepared my query in the first instance (although I have no idea just how great that advantage is).

 

It would seem setting the SQL text clears data or resets flags that I am missing. I have professional delphi not enterprise so can't see the FireDac code to see what it is doing so I can copy.

[Uwe Raabe 2057]

If the query is a SELECT statement or something that also returns a record set you should use Open instead of Execute. Therefore you should add a Close before changing any parameters.

[Mark Williams 14]

1 hour ago, Uwe Raabe said:

If the query is a SELECT statement or something that also returns a record set you should use Open instead of Execute. Therefore you should add a Close before changing any parameters.

Sorry. It should have said open not execute. I call the appropriate command in another script which handles various errors and post details to a database. Didn't want to confuse by including all that code, but managed to confuse by not doing so!

 

1 hour ago, Uwe Raabe said:

Therefore you should add a Close before changing any parameters.

I was sure I had already tried Close and caused an AV. However, it works! Thanks.