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
3 REPLIES
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