[NIFI-5082] SQL processors do not handle Avro conversion of Oracle timestamps correctly

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.7.0
• Component/s: Extensions
• Assignee: Matt Burgess
• Reporter: Matt Burgess
• Created: 13/Apr/18 16:12
• Updated: 24/Apr/18 08:50
• Resolved: 24/Apr/18 08:50

Description

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.

Issue Links

• links to: GitHub Pull Request #2638