You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by "Eilebrecht, Karl (Key-Work)" <ka...@key-work.de> on 2010/06/21 16:00:31 UTC
AW: SQL-Server datetime issue - workaround
Hi,
I figured out that this is indeed a 2008-JDBC-driver-"feature".
The quick workaround is to use the older 2005-JDBC-driver.
Medium-term we will change the related columns to datetime2(3) which
means exactly the millisecond-precision we like.
This simulates what Microsoft does before storing datetime:
public static Timestamp roundTimestampLikeMicrosoft(Timestamp source) {
//http://msdn.microsoft.com/de-de/library/ms187819.aspx
if (source == null) {
return null;
}
long time = source.getTime();
long remainder = time % 10;
if (remainder == 0 || remainder == 3 || remainder == 7) {
return source; //won't change
}
if (remainder < 2 || remainder > 8) {
remainder = 0;
}
else if (remainder > 1 && remainder < 5) {
remainder = 3;
}
else if (remainder > 4 && remainder < 9) {
remainder = 7;
}
return new Timestamp((time / 10) + remainder);
}
For clarification: The problem with the new driver is NOT that the database stores at low precision (no difference to the old one, no bug). The problem arises when comparing (for a select). There seems to be an "optimization" in the new driver that leads to the described symptoms.
Can't even say if this is a bug or a shot in the foot caused by wrong usage ...
Regards.
Karl
Karl Eilebrecht
Key-Work Consulting GmbH | Kriegsstr. 100 | 76133 Karlsruhe | Germany | www.key-work.de
Fon: +49-721-78203-277 | E-Mail: karl.eilebrecht@key-work.de | Fax: +49-721-78203-10
Key-Work Consulting GmbH Karlsruhe, HRB 108695, HRG Mannheim
Geschäftsführer: Andreas Stappert, Tobin Wotring
-----Ursprüngliche Nachricht-----
Von: Eilebrecht, Karl (Key-Work)
Gesendet: Montag, 21. Juni 2010 07:44
An: dev@ofbiz.apache.org
Betreff: SQL-Server datetime issue
Hi,
this is only for people using Microsoft SQL-Server, no need to read this if you're using another database:
We just stumbled across a problem while migrating to SQL Server 2008 R2 64 bit, newest JDBC-driver.
It seems that the datetime datatype (used for timestamps) never was suitable for being part of a primary key due to
rounding issues.
http://msdn.microsoft.com/en-us/library/ms187819.aspx
However it is used (i.e. some relations and some of our own tables) and we actually never faced any problems with that - up to now.
With any older versions all worked fine. But under certain circumstances with the newest server/driver combination we can
now reproduce an error when calling createOrStore twice with the same PK values right after another. The second statement
results in a primary key violation. Similar problem when executing findByPrimaryKey(entityPkJustStored) - not found.
Reason seems to be that the value itself is stored at a slightly lower precision that the comparison (second call)
is processed.
We're still investigating and are currently discussing two options:
(1) use datetime2 (higher precision)
(2) remove any "rounding-prone" datatypes like datetime etc. from primary keys (replace with integer types)
Regards.
Karl
Karl Eilebrecht
Key-Work Consulting GmbH | Kriegsstr. 100 | 76133 Karlsruhe | Germany | www.key-work.de<http://www.key-work.de>
Fon: +49-721-78203-277 | E-Mail: karl.eilebrecht@key-work.de<ma...@key-work.de> | Fax: +49-721-78203-10
Key-Work Consulting GmbH, Karlsruhe, HRB 108695, HRG Mannheim
Gesch?ftsf?hrer: Andreas Stappert, Tobin Wotring