Nifi cannot handle DB2 BLOB data; sends 'Wrong result column type for requested conversion.'

[#1]

When using an SQL query with a "BLOB" content the following error is thrown:

ExecuteSQL Unable to execute SQL select query SELECT BLOBTBL.BLOB_CONTENTS FROM BLOB_DECOMP BLOBTBL fetch first 10 rows only with UR due to org.apache.nifi.processor.exception.ProcessException: com.ibm.db2.jcc.am.jo: [jcc][t4][1092][11638][3.57.110] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815; routing to failure: org.apache.nifi.processor.exception.ProcessException: com.ibm.db2.jcc.am.jo: [jcc][t4][1092][11638][3.57.110] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815

Tried the work around of using a 'cast' such as SELECT case(BLOBTBL.BLOB_CONTENTS as varchar(2000)) FROM BLOB_DECOMP BLOBTBL fetch first 10 rows only with UR

But then the following is thrown:

"failed to process session due to org.apache.avro.SchemaParseException: Illegal initial character: 1: org.apache.avro.SchemaParseException: Illegal initial character: 1"

[#2 · Accepted solution]

DB2 might be returning a different JDBC type for BLOB than what the processor is expecting, such that it tries to convert it to a String or something else rather than a byte array.

For your workaround, try a column alias for the case() function, so you can set the name of that column to something Avro will like, such as "blob_contents":

SELECT case(BLOBTBL.BLOB_CONTENTS as varchar(2000)) blob_contents FROM BLOB_DECOMP BLOBTBL fetch first 10 rows only with UR

[#3]

Might need an "AS blob_contents", can't remember

[#4]

Thank you very much. That worked great.

[#5]

Just a quick clarification for others following this post the "SELECT" statement should use 'cast' and not 'case' as I have it above.