Page 1 of 1
Prepare, Open and execute V3.1.0.16
Posted: Fri 16 Sep 2011 16:11
by calou
Hello
I have this code
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.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
Posted: Mon 19 Sep 2011 09:23
by AndreyZ
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?
Posted: Mon 19 Sep 2011 11:49
by calou
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
Posted: Mon 19 Sep 2011 13:39
by AndreyZ
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;
Posted: Mon 19 Sep 2011 14:30
by calou
Thanks. It is clear now
Regards