Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Expert Contributor

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"

1 ACCEPTED SOLUTION

avatar
Master Guru

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

View solution in original post

4 REPLIES 4

avatar
Master Guru

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

avatar
Master Guru

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

avatar
Expert Contributor

Thank you very much. That worked great.

avatar
Expert Contributor

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