[
https://issues.apache.org/jira/browse/EMPIREDB-283?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rainer Döbele resolved EMPIREDB-283.
------------------------------------
Resolution: Fixed
Hello everyone,
I have analysed and resolved the issue, that updates using prepared statements
were not working on SQL Server 2016+
As stated before, the problem occurred with the constraint on the
update_timestamp column (a.k.a. optimistic-locking) when updating records. For
reasons unknown to me, Microsoft has changed the behaviour in a way that when
adding a java.sql.Timestamp as a parameter to a prepared statement, all
comparisons with an existing DATETIME value fail. Using the SQL Server Profiler
tool, I found, that instead of milliseconds '2019-01-27 19:56:31.374' the
values was sent to the server with nanonseconds, even though they were all zero
(like e.g. '2019-01-27 19:56:31.374000000') and somehow those values were not
regarded as equal, although technically they are.
However, when using the datatype DATETIME2 instead of DATETIME, everything
works fine.
Hence the recommended solution is to use DATETIME2 instead of DATETIME for new
databases.
For existing databases I also recommend to convert all existing DATETIME
columns to DATETIME2 if possible.
However, in cases where this is not desired to possible, in order for existing
databases using DATETIME to work on SQL Server 2016+ I have added a property
“useDateTime2” to the driver class DBDatabaseDriverMSSQL. The default value for
this property is “true”.
Hence for existing databases, compatibility can be achieved like this:
{{((DBDatabaseDriverMSSQL)driver).setUseDateTime2(*false*);}}
When setting this property, all timestamp values will be added to the prepared
statement as strings with milliseconds only (instead of nanoseconds). As a
DATETIME column can only hold milliseconds anyway, there is no drawback to this.
Thanks to Gunnar for reporting this issue and thumbs down for Microsoft for
making such a vile change, that took me many hours to get behind.
Regards,
Rainer
> PreparedStatements not working with MS SQL-Server >= 2016
> ---------------------------------------------------------
>
> Key: EMPIREDB-283
> URL: https://issues.apache.org/jira/browse/EMPIREDB-283
> Project: Empire-DB
> Issue Type: Bug
> Components: Core
> Affects Versions: empire-db-2.4.4, empire-db-2.4.6, empire-db-2.4.7
> Environment: JAVA 8, WIN2K12R2, MS-SQL-SRV > 2012, JDBC-Driver 4.2,
> 6.0, 7.0
> Reporter: Gunnar Kappei
> Assignee: Rainer Döbele
> Priority: Major
>
> Since the company I'm working at upgraded their database-servers from MS-SQL
> 2012 to 2016, there seems to be an issue when using PreparedsStatements
> together with Empire-DB.
> When enabling PreparedStatements via
> DBDatabase#setPreparedStatementsEnabled(true), it's not possible to perfom
> SQL-UPDATEs.
> Debugging the sources I found out, that the problem is located nside the
> executeSql-method of the DBDatabase / DBDatabaseDriverMSSQL classes. The
> number of affected records is < 0. Therefore an exception is thrown. There is
> no issue with INSERT statements.
> When disabling PreparedStatements, the code is working fine again since it
> did for years now. Tried Empire-DB 2.4.4, 2.4.6, 2.4.7 and several versions
> of the official JDBC driver from MS. On several database-servers. Getting the
> same result every time.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
