[ 
https://issues.apache.org/jira/browse/NIFI-5082?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16438025#comment-16438025
 ] 

Matt Burgess commented on NIFI-5082:
------------------------------------

We should probably also support TIMESTAMP WITH TIME ZONE since it is a valid 
SQL type (with value 2014). Unfortunately [Oracle has its own 
extensions|https://docs.oracle.com/cd/B13789_01/java.101/b10979/basic.htm#g1028145]
 and does not return 2014 for TIMESTAMP WITH TIME ZONE, it returns -101. In the 
default processing (where we currently just throw an IllegalArgumentException), 
we may want to check to see if the actual object is a Timestamp and perform the 
processing as such. However I am leery of doing this as it seems brittle and 
likely not cross-platform.

> SQL processors do not handle Avro conversion of Oracle timestamps correctly
> ---------------------------------------------------------------------------
>
>                 Key: NIFI-5082
>                 URL: https://issues.apache.org/jira/browse/NIFI-5082
>             Project: Apache NiFi
>          Issue Type: Bug
>            Reporter: Matt Burgess
>            Priority: Major
>
> In JdbcCommon (used by such processors as ExecuteSQL and QueryDatabaseTable), 
> if a ResultSet column is not a CLOB or BLOB, its value is retrieved using 
> getObject(), then further processing is done based on the SQL type and/or the 
> Java class of the value.
> However, in Oracle when getObject() is called on a Timestamp column, it 
> returns an Oracle-specific TIMESTAMP class which does not inherit from 
> java.sql.Timestamp or java.sql.Date. Thus the processing "falls through" and 
> its value is attempted to be inserted as a string, which violates the Avro 
> schema (which correctly recognized it as a long of timestamp logical type).
> At least for Oracle, the right way to process a Timestamp column is to call 
> getTimestamp() rather than getObject(), the former returns a 
> java.sql.Timestamp object which would correctly be processed by the current 
> code. I would hope that all drivers would support this but we would want to 
> test on (at least) MySQL, Oracle, and PostgreSQL.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Reply via email to