topic How can I insert a blank instead of a null into a database field? in Talend Studio

How can I insert a blank instead of a null into a database field?

MarkM1 — Tue, 12 Nov 2019 22:42:28 GMT

I am reading data from a MS SQLServer table using tDBInput, routing it through a tMap component, writing certain parts of it to a DB2 for i table using tDBOutput, and finally outputting it to an Excel Spreadsheet using a tFileOutputExcel component. The flow is linear.

Everything works great except the output to DB2. All the records I expect are in the Excel spreadsheet, but none are in the DB2 table. The problem seems to be that it is trying to write a null into a field that is not null capable. I am trying to write a blank into that field because I have nothing to map, but it is not working. TOS is insisting on putting a null in there. This is one of 4 similar flows, and all work but this one. The only difference is the blank value.

So, how do I write a constant blank into a DB field that is not null capable?

Re: How can I insert a blank instead of a null into a database field?

manodwhb — Wed, 13 Nov 2019 06:11:50 GMT

@mmurphy , You can try the below way in tmap or tjavarow with below condition for sting filed.

row1.col1==null?"":row1.col1

Re: How can I insert a blank instead of a null into a database field?

Raghuram_Puram — Wed, 13 Nov 2019 08:08:10 GMT

Hi,

If entire column needs to be blank, then in your tMap output section, in the expression builder of that column mapping, insert double quotes(" ") instead of leaving it blank.

If an input column has to be mapped, try handling null for that mapping using conditional statement.

**input_row.col_name==null ? " " : input_row.col_name**

Good luck!