topic Re: Skip insert of null when column is missing from input. in Talend Studio

Skip insert of null when column is missing from input.

Anonymous — Tue, 01 Jan 2013 14:12:06 GMT

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

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

Anonymous — Sat, 05 Jan 2013 10:40:33 GMT

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

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

cmadaka — Mon, 15 Jun 2015 04:33:56 GMT

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

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

Anonymous — Thu, 06 Oct 2016 05:27:28 GMT

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