Support Questions

Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Data Analytics (CDA) group hub.

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

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

Super 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

Super 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

Super Guru

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

Contributor

Thank you very much. That worked great.

Contributor

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

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.