[
https://issues.apache.org/jira/browse/EMPIREDB-283?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16744466#comment-16744466
]
Rainer Döbele commented on EMPIREDB-283:
----------------------------------------
Hello Gunnar,
sorry for the delay, but I had to setup a new VM first.
I now have an SQL Server 2017 installed and I can ideed confirm the problem.
The error occurs because for optimistic locking, we're adding a constraint on
the declared timestamp column (UPDATE_TIMESTAMP in the basic example) and
SQL-Server simply does not match the timestamp with the value in the database.
Hence the constraint:
{{"WHERE EMPLOYEE_ID=? AND UPDATE_TIMESTAMP=?" }}(where the timestamp parameter
is a java.sql.Timestamp object)
always fails and the number of records updated is 0.
This is particularly strange, as the same timestamp value was provided from SQL
Server itself when the record was read.
I have also found out, that when you strip off the nanoseconds from the
timestamp, then it works again.
This can be done by creating you own driver class and overriding
addStatementParam as follows:
{{public class MyMSSQLServerDriver extends DBDatabaseDriverMSSQL
}}
{{{
@Override
protected void addStatementParam(PreparedStatement pstmt, int paramIndex,
Object value)
throws SQLException
{
if (value instanceof Timestamp)
{ // special handling for timestamp
Timestamp ts = (Timestamp)value;
ts.setNanos(0); // Clear nanos
pstmt.setObject(paramIndex, ts);
}
else
{
super.addStatementParam(pstmt, paramIndex, value);
}
}
}
}}
{color:#333333}I don't like this solution, as it completely eliminates
nanoseconds from the timestamp and hence will work on seconds only. But
currenly I have nothing else to offer.{color}
{color:#333333}I will continue investigating this and I let you know if I found
out more.{color}
{color:#333333}Regards,{color}
{color:#333333}Rainer{color}
{color:#333333}P.S. SQLServer 2014 does not have this problem. It was
introduced with 2016.{color}
> PreparedStatements not working with MS SQL-Server > 2012
> --------------------------------------------------------
>
> 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
> 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)
