Ask Your Question
0

MS JDBC DateTimeOffset support?

asked 2018-04-15 04:33:37 -0500

chris gravatar image

I'm trying to use the MS JDBC driver and the SQL Server Change Tracking Client to track changes to a MS SQL Server database and maintain Elasticsearch indexes. I've done this successfully with the JDBC Query Consumer by casting the DateTimeOffset fields to JDBC standard data types in the query. The SQL Server Change Tracking Client would be a better solution but StreamSets doesn't support DateTimeOffset data type.

There is a Jira ticket (SDC-4747) opened in 2016 and untouched. That tickets states that the jTDS driver can be used to work around this issue but jTDS hasn't been updated since 2013 and I don't believe it works with Java 8 now required for StreamSets.

The error returned is: com.streamsets.pipeline.api.StageException: JDBC_37 - Unsupported type -155 for column ModifiedDtTm. (a DateTimeOffset data type)

Any suggestions?

Thanks

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
0

answered 2018-07-11 05:38:51 -0500

marko gravatar image

As a workaround for now if you want to avoid casting, under the advanced tab of the JDBC consumer, look for "On Unknown Type" and set it to convert to string. After this you can use a field type converter and convert the string to a date format of your choice.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-04-15 04:33:37 -0500

Seen: 50 times

Last updated: Apr 15