dbExpress does not read the milliseconds part of TimeStamp fields

Question

Recently some Delphi / InterBase applications display

"Record not found or changed by another user"

They use the default TSQLQuery/TClientDataSet (or TSimpleDataSet) components of dbExpress.

We found that in the database table, a field was stored with milliseconds by a non-dbExpress application.

It looks like dbExpress did not read the milliseconds from DB, and use that truncated value in the 'where' condition of the update table SQL statement, so there was no matching record to be updated.

Update: we are using TSQLTimeStamp fields in the Delphi application. No OnBeforePost handlers are modifying the field value.

Update 2

As can be seen in IBExternals.pas PCTimeStructure does not have a milliseconds part. So 'by design' no support of milliseconds for InterBase in Delphi (2009).

Does anybody know if ms support has been added in a later version of Delphi?

Sham

See also: forums.embarcadero.com/thread.jspa?threadID=109177&tstart=0 – Sham

Answer 1

I guess it's the isc_decode_timestamp Interbase/Firebird client API which doesn't support milliseconds. Here is a related Firebird tracker issue.

Update: also see this answer.

Myongmyopia

IBExpert displays the millisecond part of the data correctly so I think this is fixed in the InterBase client library – Sham

Ok now I see it in IBExternals.pas - PCTimeStructure does not have a milliseconds part. So it is 'by design' no support of milliseconds for InterBase in Delphi (2009) :P – Sham

IBExpert simply doesn't use the API. I think they're using FIBPlus which uses PISC_QUAD(FXSQLVAR^.sqldata)^ to retrieve timestamps. – Myongmyopia

the answer is 'almost' correct - actually the InterBase/Firebird client API supports timestamps, but Delphi uses the API function call which does not include the milliseconds... – Sham

@Sham The Firebird DBX driver uses the particular API which does not support timestamp milliseconds, that's the problem. I didn't say anything about IB/FB API in general. – Myongmyopia

Answer 2

Don't know dbexpress well enough to say one way or another, but it may wel be the database that is doing the trimming. I do know that for example SQL server does drop milliseconds that are passed to it. You could try TIMESTAMP columns instead of DATETIME (not sure about exact names). Perhaps TIMESTAMP columns do have a millisecond resolution in the database you are using.

Myogenic

The database values are correct (with milliseconds). I have updated all records to set the milliseconds to zero, now the application works. Until there is a patch, we have to modify the other app which (correctly) writes timestamp values with milliseconds. – Sham

Answer 3

var
  tmpDTS: string;
  tmpDT: TDateTiume;

DateTimeToString(tmpDTS,'yyyy"."mm"."dd" "hh"."nn"."ss" "zzz',tmpDT);

With this format I insert a record in a Firebird table (via TSQLQuery) and Flamerobin shows the exact value.

Instead, if I insert the value by means of a TDateTime parameter, milliseconds are truncated. DBGrid shows values truncated.

Delphi XE, Db Express, driver Firebird, Firebird 2.5.1 .

Intolerance

Yes I understand that executing the DSQL directly bypasses the truncation. This is a workaround. – Sham