Problem with SQLTimeStamp

Problem with SQLTimeStamp

Postby 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
zvasku
 
Posts: 72
Joined: Tue 19 Sep 2006 12:04

Postby 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;
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09

Postby 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
zvasku
 
Posts: 72
Joined: Tue 19 Sep 2006 12:04


Return to dbExpress driver for Oracle