Problem with SQLTimeStamp

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
zvasku
Posts: 73
Joined: Tue 19 Sep 2006 12:04

Problem with SQLTimeStamp

Post by zvasku » Tue 13 Nov 2007 19:29

Hello,

I have problem with DATE=TSQlTimeStamp fields. This simple code generates exception:

var X:Variant;

X:=VarSQLTimeStampCreate(Now);
Q.SQL.Text:='insert into testa(id,datum) values (1,:D)';
Q.ParamByName('D').Value:=X;
Q.ExecSQL();
Q.SQL.Text:='update testa set id=2 where DATUM=:D';
Q.ParamByName('D').Value:=X;
r:=Q.ExecSQL();
if r=0 then raise Exception.Create('Record not found');

D2005, D2006, driver 4.20.5 OIC and NET too

Is it a problem in driver or is it a general problem of sqltimestamps?

Thanks
Zdenek

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Wed 14 Nov 2007 14:14

The problem occurs because of inaccuracy on conversion between TDateTime and TSQLTimeStamp. This is a usual occurrence for DateTime and floating point numbers.

You can avoid the problem if you will compare only date part of values:

Code: Select all

update testa set id=2 where trunc(DATUM) = trunc(:D)
The following query also should work:

Code: Select all

update testa set id=2
where to_char(DATUM, 'dd.mm.yyyy hh24:mi:ss') = to_char(:D, 'dd.mm.yyyy hh24:mi:ss')
There also was no problems in our tests, if TDateTime value is assigned to the parameter:

Code: Select all

var
  D: TDateTime; 
...
  D := Now; 
  Q.ParamByName('D').Value := D; 

zvasku
Posts: 73
Joined: Tue 19 Sep 2006 12:04

Post by zvasku » Wed 14 Nov 2007 14:26

Thanks for you answer. I usually use TO_DATE/TO_CHAR conversion for such a problem. This problem essentialy comes from TClientDataset.Applyupdate where TSQLResolver generates where DATUM=? and then assign Value from Field which is TSQLTimeStamp. I remove field DATUM from where clause in such a case and all is ok now.

Zdenek

Post Reply