[NIFI-6061] PutDatabaseRecord does not properly handle BLOB/CLOB fields

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.11.4
• Fix Version/s: 1.14.0
• Component/s: Extensions
• Assignee: ZhangCheng
• Reporter: Matt Burgess
• Created: 20/Feb/19 22:30
• Updated: 12/Oct/23 09:59
• Resolved: 04/May/21 20:02

Description

BLOB/CLOB fields in NiFi's Record API are returned from the record as Object[Byte], but when PutDatabaseRecord tries to insert Object[] via setObject(), the following error occurs:

2019-02-20 15:11:16,216 WARN [Timer-Driven Process Thread-10] o.a.n.p.standard.PutDatabaseRecord PutDatabaseRecord[id=0c84b9de-0169-1000-0164-3fbad7a17664] Failed to process StandardFlowFileRecord[uuid=d739f432-0871-41bb-a0c9-d6ceeac68a6d,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1550690599998-1, container=default, section=1], offset=1728, length=251],offset=0,name=d739f432-0871-41bb-a0c9-d6ceeac68a6d,size=251] due to org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of [Ljava.lang.Object;. Use setObject() with an explicit Types value to specify the type to use.:

Somewhere in the value conversion/representation, PutDatabaseRecord would likely need to create a java.sql.Blob object and transfer the bytes into it. One issue I see is that the record field type has been converted to Array[Byte], so the information that the field is a BLOB is lost by that point. If this requires DB-specific code, we'd likely need to add a Database Adapter property and delegate out to the various DB adapters.

Issue Links

• is related to: NIFI-12217 In PutDatabaseRecord processor, when you try to insert a CLOB and a SQLException gets catched, the exception message gets lost
• links to: GitHub Pull Request #4551
• links to: GitHub Pull Request #5049