Hi,

we have made an Delphi app that is used "in house" by many users. It's
rather complicated and thus contains relatively many bugs that we try to
solve in some order. It is still under development.

We were forced to launch it a bit prematurely so there were serious
problems. Sometimes we had to manualy change data in the database just
to keep the whole company running. Some percentage of these manual
changes was not carried correctly (we forgot about some dependencies
etc.) so we had to develop special tests to find the errors in data.

The situation has already improved dramatically, but we have found some
problems in the actual tests.

One of those tests is about having correct relationship between the
quantities on stock that are logged daily soon after midnight (eg. table
STOCK_CONTENTS_LOG) and logged changes in the stock quantities that
occur during the day because of incoming vendor supplies and outgoing
customer orders/invoices etc. (STOCK_ITEM_IN_OUT_LOG).
IOW, any process that increases or decreases the quantity on stock logs
this operation immediately under read committed transaction, the
timestamp is generated using cast ('now' as timestamp).
The STOCK_CONTENTS_LOG logging process runs as stored procedure under
repeatable read transaction and takes some time to finish (currently
about 20 seconds). We generate the times of process start and process
end using cast('now' as timestamp).

Eg.

STOCK_CONTENTS_LOG (alias SCL)
SCL_INS SCL_QTY
25.12.2003 00:00:25 32
26.12.2003 00:00:32 58


STOCK_ITEM_IN_OUT_LOG (alias SIL)
SIL_INS SIL_QTY
25.12.2003 08:10:25 -10
25.12.2003 09:02:56 -5
25.12.2003 19:00:10 2
26.12.2003 00:00:32 39


earlier SCL_QTY + (SUM(SIL_QTY) where SIL_INS > earlier SCL_INS and
SIL_INS <= later SCL_INS) = later SCL_QTY

32 -10 -5 +2 +39 = 58 ... OK

1. problem (small)
Both timestamps stored in the DB are the same. If the transaction
isolation is read committed, the timestamps differ. How can I get real
current timestamp in DB under repeatable read transaction ? I am afraid
I can't ...

2. problem (bigger)
The database generated timestamps don't contain subsecond part, which
causes confusion in this test when there is a change logged in
STOCK_ITEM_IN_OUT_LOG during the same second as STOCK_CONTENTS_LOG
logging was started, but just a little later.


In the foloowing example there are timestamps with full precision. If
the DB generated it, the test would run OK, without it the test reports
fake error, as the last record in STOCK_ITEM_IN_OUT_LOG shouldn't be
taken into account.

STOCK_CONTENTS_LOG (alias SCL)
SCL_INS SCL_QTY
25.12.2003 00:00:25.0500 32
26.12.2003 00:00:32.1000 58


STOCK_ITEM_IN_OUT_LOG (alias SIL)
SIL_INS SIL_QTY
25.12.2003 08:10:25.0000 -10
25.12.2003 09:02:56.0000 -5
25.12.2003 19:00:10.0000 2
26.12.2003 00:00:32.0200 39
26.12.2003 00:00:32.9999 -24

We can't fill the timestamp values on the client and we don't know about
any UDF (for Linux, FB CS) that correctly generates timestamps with full
precision. And we really think that it's kind of unbelievable that FB1.5
still can't generate timestamp with subsecond part :-((((

But perhaps someone has a solution for this.

Thanks a lot for reading this.

Best regards,
Tom

=================================

Tomas Michalik
ProCA, s. r. o.
V Luzich 818, Praha 4
Czech Republic

e-mail: michalik@...
tel: +420 234646446