topic Insert or update with ID from context (that can be null) in Talend Studio

Insert or update with ID from context (that can be null)

Anonymous — Mon, 19 Nov 2018 15:27:00 GMT

Hello,

I would like to insert or update datas in a database depending on the value of the ID available in my context. (see screenshots below)

2 cases :

My ID (context.asset_id) is not null so it exists in the output table => update => working well
My ID (context.asset_id) is null => insert (using auto_increment on the ID which has been enabled) => throw an exception

At the moment, in order to make it works, I'm using two different jobs (one for updates and one for inserts) and a tFilterRow which switch null id's to insert job and not null id's to update job. The problem is both jobs are quite identical so that's not really clean ...

How could I make it work as I wish?

Thanks

Re: Insert or update with ID from context (that can be null)

Anonymous — Mon, 19 Nov 2018 20:58:14 GMT

Update and Insert are two distinct operations: why do you want to combine the jobs? It sounds like more trouble than it's worth.

How is the job handling null values for the ID field now?

Re: Insert or update with ID from context (that can be null)

Anonymous — Tue, 20 Nov 2018 08:15:15 GMT

I would like to combine these two jobs because they are identical and it doubles the cost of maintenance if a modification need to be done...

Null values are not handled at all, let me explain you how it works.

I have a table with two ID's columns (ID from old db, ID from new db). I read each row of this table and 2 cases are possible:

if ID from new db is not null then I update datas by using the ones from old db.
if ID from new db is null then I insert datas from old db to new db.

I use a tFilterRow to switch on the value of the ID (Not null => update job, null => insert job).

The only difference between these 2 jobs is in the tMap before insert or update datas (tDBOutput). If I am inside inserting job then the field ID is left empty, if I am inside updating job then the field ID is filled.

Re: Insert or update with ID from context (that can be null)

Anonymous — Tue, 20 Nov 2018 13:13:58 GMT

I'm assuming the new records have an auto-increment ID field, so that you just need to handle the NullPointer exception, and not generate a new unique ID, correct? If so, you could test the ID field in tMap to see if it's empty, and if it is, assign a placeholder value to avoid the exception. You might be able to use Java's isNull() function; something like (in the tMap expression for the ID field):

row1.ID.isNull() ? "123" : row1.ID

The "123" can be any value, since you're just going to discard it. If isNull() doesn't work (sometimes it doesn't), then try comparing to the empty string:

row1.ID.equals("") ? "123" :row1.ID

If ID is a number, then:

row1.ID == "" ? 123 : row1.ID

Hope this helps.

Re: Insert or update with ID from context (that can be null)

Anonymous — Tue, 20 Nov 2018 14:11:15 GMT

Yes, as you assume, I have an auto-incrementing ID inside my new table but what do you mean by using any value which will be discarded ? If I fill the ID field with any value, it will try to insert a row with this ID, or even try to update a row with this ID (supposing I have defined the action "insert or update" on the table).