0

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Manoj Majumdar
  • 505
  • 1
  • 4
  • 23
  • 1
    `NULL` has nothing to do with it; the error is quite specific: *"Implicit conversion from data type varbinary to datetimeoffset is not allowed. Use the CONVERT function to run this query."* You're trying to insert a `varbinary` into a `datetimeoffset`; that's the problem. – Thom A Apr 30 '20 at 08:33
  • Actually I am only performing PreparedStatment.setNull(), and I am not inserting any value other than NULL there. My paramterMap of PreparedStatment shows {1=null} after calling setNull(); – Manoj Majumdar Apr 30 '20 at 09:30
  • The schema detail by teiid, reads datetimeoffset as object(34), might this be causing an issue? – Manoj Majumdar May 05 '20 at 10:59

1 Answers1

1

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.

Steven Hawkins
  • 538
  • 1
  • 4
  • 7
  • I altered the schema for datetimeoffset by taking it as a timestamp and then deployed the vdb, and it is working. Thanks for the suggestion – Manoj Majumdar May 26 '20 at 19:44