Search code examples
javasqldatabasetalend

Talend - Insert 'NULL' into a Database Column which type is 'NUMBER'

I want to read values (integers) from a CSV file, and insert them into a Database Table. However, for a certain column of the table, if the values received from the CSV file don't belong to an interval, the value to write in the Database should be NULL.

For example in my Java App, I do : insert.setNull(3, java.sql.Types.INTEGER); in that cases.

But how can I do this in Talend, if the column type is 'NUMBER' ?

Solution

  • Just use a tMap component between yout tFileInput and database output instances to carry this simple transformation. Inside theis component, on the bottom right pane (the output schema) select the checkbox to let your field be nullable. Be careful, since fields coming from CSV are not nullable by default.

    enter image description here

    Finally, on the upper right pane (output transformation), on the target field (the one you forced to be nullable) write a ternary expression. For example:

    (assuming your incoming connection is called input_row)

    (input_row.field >= 0 && input_row.field <= 3) ? <value to write if inside interval> : null

    What I want to say is that if the field is nullable on both Talend side and on the target DB table, the boring java null is your friend.