topic Re: [resolved] Update field values based on Update or insert status in Talend Studio

[resolved] Update field values based on Update or insert status

Anonymous — Wed, 13 May 2015 09:37:19 GMT

Hi,
This "probably" is not a Talend DI issue but I am not able to figure out how to do it so I am asking here.
I have to copy data from 1 database table to another. In the destination table there are 2 fields created_date and updated_date.
The value in these fields is to be populated, as the name suggests, when the record is created and updated respectively.
So when loading a record from the source into the destination I need to check (based on the destination's primary key) if the record already exists in the destination or not. If it does not then I need to set the value of the created_date field. If the record exists (id is already present) then I need to set the value of the updated_date field and keep the original value of the created_date field.
Now my problem is how and where do I make this check and set the appropriate field's value?

Re: [resolved] Update field values based on Update or insert status

Anonymous — Mon, 18 May 2015 08:16:45 GMT

Hi,
What's your target DB? Are you looking for Action on data option " Update or Insert "?
Best regards
Sabrina

Re: [resolved] Update field values based on Update or insert status

Anonymous — Mon, 18 May 2015 12:41:41 GMT

Hi,
My target is a Mysql database table.
But what I want to know is by using the "Action on data" option of "Update or insert" how can I ensure that I set the created_date value field if the record is going to be "inserted" and set updated_date value if the record is going to be "updated"?

Re: [resolved] Update field values based on Update or insert status

Anonymous — Wed, 20 May 2015 10:55:38 GMT

Do you think that writing a database trigger for this update is an easier option than getting it done through Talend?

Re: [resolved] Update field values based on Update or insert status

Anonymous — Tue, 26 May 2015 06:32:25 GMT

Does nobody have a solution for my problem? Or an opinion whether using database triggers is a better option?

Re: [resolved] Update field values based on Update or insert status

Anonymous — Tue, 26 May 2015 10:47:54 GMT

hi all,
use a tMap to inner join on your ID.
Use a flow output to update items and another one to create items.
your update flow is the output flow where id is found (join).
add inner join rejet option for your creates items flow.
Use create action for create flow, and update action on table for the other one.
Manage update thanks advanced setting and 'use field options'.
have a look at talend help for some scenario :
https://help.talend.com/search/all?query=tMysqlOutput&content-lang=en

hope it helps
regards
laurent

Re: [resolved] Update field values based on Update or insert status

Anonymous — Tue, 26 May 2015 12:46:13 GMT

Hi Laurent,
I was able to understand your explanation till the inner join part but then I was lost
Do you have some screenshots to help me understand?
I have designed an alternative approach to this where I do the following:

put the source and destination table as tMysqlInput components
link them to a tMap component with the source as the main link and destination table as lookup
make a left outer join between these tables in the tMap based on the key field
declare a boolean variable "insert_record" and set it's value as (destination_table.key_field == null)
In the output row, to the same destination table as a tMysqlOutput component, I set the value of created_date with the following expression Var.insert_record ? new Date() : destination_table.created_date
Similarly I set the value of updated_date field with the expression !Var.insert_record ? new Date() : null

See the above screenshot where I have done the above described mapping.
Do you think this is a good idea?
Also please share your idea with some screenshots (if possible) so that I can try the same.

Re: [resolved] Update field values based on Update or insert status

Anonymous — Tue, 26 May 2015 13:14:13 GMT

could be something like :

the tip is to use "the catch inner join rejet" for the create action.

regards
laurent

Re: [resolved] Update field values based on Update or insert status

Anonymous — Tue, 26 May 2015 15:28:30 GMT

But what I want to know is by using the "Action on data" option of "Update or insert" how can I ensure that I set the created_date value field if the record is going to be "inserted" and set updated_date value if the record is going to be "updated"?

Hi,
Do you try to just play with "Field options" of the tMySQLOutput :
Your tMySQLOutput is design as "Insert or Update" so it will automatically insert or update your output table.
In "Fields Options" :
- For your field created_date, check insertable and uncheck updatable ==> So, this field will be populated for an insert and it will not be updated for an update
- For your field updated_date, uncheck insertable and check updatable ==> So, this field will not be populated for an insert and will be updated for an update

Re: [resolved] Update field values based on Update or insert status

Anonymous — Wed, 27 May 2015 08:46:23 GMT

for information "Insert or Update" is not a optimize solution (haven't got benchmark result by my side but it 's slower).
regards
laurent

Re: [resolved] Update field values based on Update or insert status

Anonymous — Thu, 28 May 2015 11:01:11 GMT

Thanks Laurent.
Your approach seems to work for me.

Re: [resolved] Update field values based on Update or insert status

Anonymous — Fri, 29 May 2015 12:52:04 GMT

thanks to put topic as 'resolved'
regards
laurent

Re: [resolved] Update field values based on Update or insert status

Anonymous — Thu, 18 Jun 2015 14:45:08 GMT

Hi,
I have got one more question related to this. Is there a way to know how many records have been inserted or updated?
A log or a message that can be printed on the console after the job execution is completed.

Re: [resolved] Update field values based on Update or insert status

Anonymous — Thu, 18 Jun 2015 15:21:03 GMT

Yes, you have some variables linked to your tMysqlOutput component :

((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_INSERTED"))
((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_UPDATED"))
((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_DELETED"))
((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_REJECTED"))

Re: [resolved] Update field values based on Update or insert status

Anonymous — Fri, 19 Jun 2015 05:29:06 GMT

Thanks tangiobs!