Skip insert of null when column is missing from input.

One Star

Skip insert of null when column is missing from input.

In code generated by TOS, I see this pattern which sets a "null" value on insert when the value is missing in the source.
if (productOrder.order_currency == null) {
pstmt_tPostgresqlOutput_1.setNull(13,java.sql.Types.VARCHAR);
} else {
pstmt_tPostgresqlOutput_1.setString(13, productOrder.order_currency);
}
This results, when 'productOrder.order_currency' is null, in a SQL INSERT like so:
INSERT INTO "mytable" (..., ORDER_QUANTITY, ORDER_CURRENCY, ...) VALUES (..., 10, null, ...)
I would prefer to have it generate an insert statement like so, when order_currency is null:
INSERT INTO "mytable" (..., ORDER_QUANTITY, ...) VALUES (..., 10, ...)
And, when 'productOrder.order_currency' is not null, then it should include the column.
In other words, I would like to have the mapping skip columns that are missing from the input, instead of passing a null value (to leverage default values already present in column constraints on the target database table).
Please advise, thanks.
Ed
Community Manager

Re: Skip insert of null when column is missing from input.

Hi
If there is only one column or less columns you don't want to insert a null value if it is null, you can filter the rows which the column value is null and insert these rows on another DB output component. for example:
tFileInputDelimited--main--tMap---out1--tMysqlOutput_1
--out2--tFillterColumn--main--tMysqlOutput_2
Add a filter condition for out1:
row1.columnName!=null
filter condition for out2:
row1.columnName==null
on tFilterColumn, remove the column which you don't want to be inserted.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: Skip insert of null when column is missing from input.

hi
I have the similar requirement, but in my case i have more than 10 columns in source, and many columns allow null values. Now, i need to filter out column that have NULL values while loading into output file.
Please let me know what options i can use in Talend Big Data.
regards
Chandra
One Star

Re: Skip insert of null when column is missing from input.

Hi  i want log error file 
suppose while insert/update a record, some records are failed to insert/update in want to log those failed records list into file
id name phone city
1  shivi 888 kanpur  -- > sucessfully updated in db 
2  Atul  999 bangor  --> failed to update in db 
3  shiv  9973  atlantic - > sucessfully updated in db 
i want in log file " 2 Atul 999 bangor" record 
i have done small demo of tlogcatcher but it does not log failed record
can any one tell me how to do it?
thanks
Shivi

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog