Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

Accepted Solutions
Highlighted

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

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
Highlighted

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

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

Highlighted

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

Super Guru

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

Highlighted

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

Contributor

Thank you very much. That worked great.

Highlighted

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

Contributor

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

Don't have an account?
Coming from Hortonworks? Activate your account here