topic Re: Null Values in BLOBs in Talend Studio

Null Values in BLOBs

drosemeyer — Wed, 18 Jul 2012 13:30:01 GMT

Hi,
my jobs moves BLOB columns from ad Derby DB to a DB2 DB.
Source and target column are defined as Object in tMap.
If a BLOB value isn the source table is null, java.sql.Type is set to other, which leades to the exception:
Exception in component tDB2Output_6
com.ibm.db2.jcc.a.SqlException: Jdbc type 1111 not yet supported.
at com.ibm.db2.jcc.a.n.a(n.java:1003)
at com.ibm.db2.jcc.a.n.a(n.java:892)
at com.ibm.db2.jcc.a.n.a(n.java:873)
at com.ibm.db2.jcc.a.sf.b(sf.java:568)
at com.ibm.db2.jcc.a.sf.setNull(sf.java:558)
at
Is there a way to set the java.sql.Type to BLOB or another way to insert the null value into the target column?
Regards Dirk

Re: Null Values in BLOBs

Anonymous — Thu, 19 Jul 2012 02:59:16 GMT

Hi Dirk
It seems that you are trying to insert a NULL data in a column that is configured in the data model to NOT NULL.
Pleae recheck the schema.
Regards,
Pedro

Re: Null Values in BLOBs

drosemeyer — Thu, 19 Jul 2012 10:23:30 GMT

Hi Pedro,
no, the column is not mandatory.
In this case I would get an Sqlcode -407 from DB2.
The insert is not execcuted.
I guess the problem in in code generation:

if (save_binaryfile.C_VALUE == null) {
pstmt_tDB2Output_6.setNull(14,
java.sql.Types.OTHER);
} else {
pstmt_tDB2Output_6.setObject(14,
save_binaryfile.C_VALUE);
}
if (save_binaryfile.C_MIMETYPE == null) {
pstmt_tDB2Output_6.setNull(15,
java.sql.Types.VARCHAR);
} else {
pstmt_tDB2Output_6.setString(15,
save_binaryfile.C_MIMETYPE);
C_VALUE is the BLOB column.
In case of NULL java.sqlTypes.OTHER is used, which leads to the error.
For C_MIMETYPE java.sql.Types.VARCHAR is used, what is correct.
Regards Dirk

Re: Null Values in BLOBs

Anonymous — Thu, 19 Jul 2012 10:38:10 GMT

Hi Dirk
Usually for BLOB column, the data type of Talend is byte[].
Please try with it.
Wait for your feedback.
Regards,
Pedro

Re: Null Values in BLOBs

drosemeyer — Thu, 19 Jul 2012 12:51:52 GMT

Hi Pedro,
Object is the default type hich is set in tMap from the the schema object in derby and in DB2 as well.
I tried byte[] but it did not work. because java.sqlTypes.ARRAY is used.
if (save_binaryfile.C_VALUE == null) {
pstmt_tDB2Output_6.setNull(14,
java.sql.Types.ARRAY);
} else {
pstmt_tDB2Output_6.setBytes(14,
save_binaryfile.C_VALUE);
}

Exception in component tDB2Output_6
com.ibm.db2.jcc.a.SqlException: Jdbc type 2003 not yet supported.
at com.ibm.db2.jcc.a.n.a(n.java:1003)
at com.ibm.db2.jcc.a.n.a(n.java:892)
at com.ibm.db2.jcc.a.n.a(n.java:873)
at com.ibm.db2.jcc.a.sf.b(sf.java:568)
at com.ibm.db2.jcc.a.sf.setNull(sf.java:558)
at

Regards Dirk

Re: Null Values in BLOBs

Anonymous — Fri, 20 Jul 2012 03:08:23 GMT

Hi Dirk
Please report it on BugTracker if byte[] doesn't work.
Regards,
Pedro