I am using Teiid to insert null value in a datetimeoffset
column, but the PreparedStatement.setNull(index, Types.TIMESTAMP)
is not inserting null. The error which I am getting is this:
Caused by: java.sql.SQLException: Remote com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetimeoffset is not allowed. Use the CONVERT function to run this query.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:471)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537) at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:315) ... 27 common frames omitted
Here are my datasource details for SQL Server 2012 version:
<driver name="sqlserver" module="com.microsoft.sqlserver">
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
</driver>
and the driver version is: mssql-jdbc-8.2.0.jre8
I have tried inserting null without Teiid (by making a sql connection using Microsoft jdbc driver) and it is working. The table only contains one column with datetimeoffset
as a datatype.
Please suggest any ideas as to what I can try apart from this. Thanks in advance!
This was captured as a Teiid issue: https://issues.redhat.com/browse/TEIID-5949
A workaround for the object type mapping is suggested, but it probably should be addressed by updating the import logic.