Prepare, Open and execute V3.1.0.16

calou · Post by **calou** » Fri 16 Sep 2011 16:11

Hello

I have this code

IBCQry.SQL:=select count(*) from TABLE where test= :toto
IBCQry.Prepare;
while not eof(f)do
begin
IBCQry.parambyname('toto'):=var;
IBCQry.Open;
ShowMessage(IntToStr(IBCQry.Fields[0].Asinteger);
end;
IBCQry.Unprepare;

If i call IBCQry.Open the results are sometimes false.
Il i call IBCQry.Execute the results are always good.

Is it a bug of my version or is it normal?

Thanks for help

Regards

AndreyZ · Post by **AndreyZ** » Mon 19 Sep 2011 09:23

Hello,

The Execute method calls the Open method if provided SQL statement is a query (as in your case), so it mustn't be any difference between results of these methods. Please specify what you mean by false results. Could you provide an example of such behaviour?

calou · Post by **calou** » Mon 19 Sep 2011 11:49

Here are the log with the execute method :

Code: Select all

D:\devs\Valorem_BeVent\GESMAT\Soft\GESMAT.exe
  19/09/2011 13:39:18 0.000 SQL Prepare: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='128' 
:DT1(Unknown)= 
:DT2(Unknown)= Complete
  19/09/2011 13:39:22 0.000 SQL Execute: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='128' 
:DT1(TIMESTAMP)=18/09/2011 
:DT2(TIMESTAMP)=18/09/2011 23:50:00 Complete
  19/09/2011 13:39:29 0.000 SQL Execute: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='128' 
:DT1(TIMESTAMP)=17/09/2011 
:DT2(TIMESTAMP)=17/09/2011 23:50:00 Complete
  19/09/2011 13:39:41 0.000 SQL Execute: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='128' 
:DT1(TIMESTAMP)=16/09/2011 
:DT2(TIMESTAMP)=16/09/2011 23:50:00 Complete
  19/09/2011 13:39:42 0.000 SQL Execute: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='128' 
:DT1(TIMESTAMP)=15/09/2011 
:DT2(TIMESTAMP)=15/09/2011 23:50:00 Complete
  19/09/2011 13:39:43 0.000 SQL Execute: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='128' 
:DT1(TIMESTAMP)=14/09/2011 
:DT2(TIMESTAMP)=14/09/2011 23:50:00 Complete
  19/09/2011 13:39:50 0.000 SQL Execute: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='128' 
:DT1(TIMESTAMP)=13/09/2011 
:DT2(TIMESTAMP)=13/09/2011 23:50:00 Complete
  19/09/2011 13:39:50 0.000 SQL Execute: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='128' 
:DT1(TIMESTAMP)=12/09/2011 
:DT2(TIMESTAMP)=12/09/2011 23:50:00 Complete
  19/09/2011 13:39:51 0.000 SQL Execute: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='128' 
:DT1(TIMESTAMP)=11/09/2011 
:DT2(TIMESTAMP)=11/09/2011 23:50:00 Complete
  19/09/2011 13:39:52 0.000 SQL Execute: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='128' 
:DT1(TIMESTAMP)=10/09/2011 
:DT2(TIMESTAMP)=10/09/2011 23:50:00 Complete

The log with open

Code: Select all

  19/09/2011 13:41:33 0.000 SQL Execute: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='102' 
:DT1(TIMESTAMP)=18/09/2011 
:DT2(TIMESTAMP)=18/09/2011 23:50:00 Complete
  19/09/2011 13:41:43 0.000 SQL Unprepare: Select count(*) from DATA_NRG where SITE_ID=:SITE and DATE_TIME between :DT1 and :DT2
:SITE(VARCHAR[3])='102' 
:DT1(TIMESTAMP)=01/05/2011 
:DT2(TIMESTAMP)=01/05/2011 23:50:00 Complete

The sql is executed only one time with the open method and correctly with the execute method

Thanks

AndreyZ · Post by **AndreyZ** » Mon 19 Sep 2011 13:39

The point is that the Execute method closes a dataset if it was active before execution. The Open method does not do this. To obtain the same result with the Open method, you should close TIBCQuery manually before next execution. Here is an example:

Code: Select all

IBCQry.SQL:='select count(*) from TABLE where test= :toto';
IBCQry.Prepare; 
while not eof(f)do 
begin 
IBCQry.parambyname('toto'):=var;
IBCQry.Close;
IBCQry.Open; 
ShowMessage(IntToStr(IBCQry.Fields[0].Asinteger); 
...
end; 
IBCQry.Unprepare;

calou · Post by **calou** » Mon 19 Sep 2011 14:30

Thanks. It is clear now

Regards