[jira] [Resolved] (EMPIREDB-283) PreparedStatements not working with MS SQL-Server >= 2016

["Rainer Döbele (JIRA)" <em...@incubator.apache.org>]

     [ 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)