sql-serverteiid

Cannot insert null in datetimeoffset column in SQL Server using Teiid


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!


Solution

  • 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.