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

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

murphy_sean — Thu, 17 Nov 2016 06:16:23 GMT

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"

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

mburgess — Thu, 17 Nov 2016 06:19:18 GMT

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

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

mburgess — Thu, 17 Nov 2016 06:19:53 GMT

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

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

murphy_sean — Thu, 17 Nov 2016 06:30:11 GMT

Thank you very much. That worked great.

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

murphy_sean — Thu, 17 Nov 2016 07:04:32 GMT

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