topic Re: Generate BATCH_ID on increment with one for every job execution in Talend Studio

Generate BATCH_ID on increment with one for every job execution

Anonymous — Sat, 16 Nov 2024 11:00:06 GMT

Hello,

I'm new to Talend studio and I have scenario where I need to generate the batch_ID by doing curr_val + 1 and update the value in the oracle table with the latest batch_id.In Talend I'm looking for a component where it looks for the current batch_id and then increment by one and then update the value in the relational table.

Thanks in advance!

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Thu, 08 Oct 2015 17:26:50 GMT

I'm using the Numeric.sequence("s1",1,1) in the tmap expression and the value is not getting updated in the database table
Attached is the screenshot for the tmap expression that I'm using.Please correct me if anything is wrong.
Can anyone please help me

Thanks,

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Thu, 08 Oct 2015 17:29:49 GMT

There is no screenshot?

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Fri, 09 Oct 2015 14:44:13 GMT

Please find the attached screenshot

Thanks!

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Fri, 09 Oct 2015 14:50:10 GMT

That looks OK. You will need to show us the DB component you are using to update the database and how it is configured.

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Fri, 09 Oct 2015 15:22:19 GMT

Hi,
Attached is the screenshot for DB component.

Thanks!

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Fri, 09 Oct 2015 16:28:59 GMT

It shows you are updating your db table. What is your lookup key? This should be set on the Advanced Settings tab

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Fri, 09 Oct 2015 16:59:50 GMT

Hi,
Just now I did configured the lookup key in the additional columns.
Can you please let me know if we need to SQL expression as well.Thanks!

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Fri, 09 Oct 2015 17:14:14 GMT

Are you wanting to update or insert here? I am not clear. The update you have done is wrong. You want to remove that. If you want to set an update key, you need to tick the "Use field options" tick box and select the update key from there.
But I am not convinced you want to be doing an update here. I think you want to be inserting.

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Fri, 09 Oct 2015 17:30:49 GMT

Hi,
I did removed the additional columns and choosed the optional fields in the below.I need to update currval to 2(right now it is one).Even I executed the job and the currval is not getting updated. Thanks for your help Sir!
Can you please correct me if anything is wrong!
Attached is the screenshot.

Database table

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Fri, 09 Oct 2015 20:27:43 GMT

Ah, I see. So you want to update the value that is currently there by 1? That is a different problem and doesn't require the Numeric.sequence in Talend. Numeric.sequence is a Java method that provides a sequence that is valid for the lifetime of the job. It is reset every time the job runs.
The best way to achieve this is probably to use a tOracleRow. Connect a tFlowToIterate to the tMap. Connect the tOracleRow to the tFlowToIterate using an Iterate link. Write an update statement in there like....

"update table set CURVAL = (select CURVAL+1 from table where NAME = '" + ((String)globalMap.get("row1.Name")) +"'"

The above query assumes that the row linking the tmap to the tIterateToFlow is called "row1" and the column is called "Name". The tIterateToFlow will store the values in the globalMap hashmap for every iteration. The update statement above updates the table using the a select to find the last value for the row. The row is selected using the current value of "Name".

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Fri, 09 Oct 2015 22:35:59 GMT

Hi,
Thanks for providing me good approach!
I did used the approach that you have provided by using the below query to update in the toraclerow and tIterateFlow and getting the errors.
Can you please let me know if I'm following correctly in the mapping that you have provided.
SQL used in tOraclerow
"update T_GEO_KEYS set CURRVAL = (select CURRVAL+1 from T_GEO_KEYS where NAME = 'DS_BATCH_GID" + ((String)globalMap.get("row1.Name")) +"'"
Error

toraclerow

tIterateFlow

tmap

Thanks,
Yugandhar

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Sat, 10 Oct 2015 00:10:32 GMT

You want to use a tFlowToIterate, not a tIterateToFlow. The flow should be tMap --->tFlowToIterate ---> tOracleRow.
The update should be....
"update T_GEO_KEYS set CURRVAL = (select CURRVAL+1 from T_GEO_KEYS where NAME = '" + ((String)globalMap.get("row1.Name")) +"'"
The row (line) that feeds the tFlowToIterate should be the name of the row before "Name" in the globalMap part of above. So if the row (line) is "row22", the above would be....
"update T_GEO_KEYS set CURRVAL = (select CURRVAL+1 from T_GEO_KEYS where NAME = '" + ((String)globalMap.get("row22.Name")) +"'"
Try and make your jobs flow left to right (like reading text).

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Mon, 12 Oct 2015 14:42:27 GMT

Hi,
Thank you once gain for providing me a good approach!
I did created the job flow as toracleinput -> tmap -> tflowtoiterate -> toraclerow and I'm getting the below error.
Update statement used in toraclerow
"update T_GEO_KEYS set CURRVAL = (select CURRVAL+1 from T_GEO_KEYS where NAME = '" + ((String)globalMap.get("row1.NAME")) +"'"

Error:
Starting job sequence_generator at 09:33 12/10/2015.
connecting to socket on port 3609
connected
ORA-00921: unexpected end of SQL command
disconnected
Job sequence_generator ended at 09:33 12/10/2015.
Can you please let me know if anything is wrong.
Thanks in advance Sir!
jobflow

toraclerow

toracle input

Thanks,
Yugandhar

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Mon, 12 Oct 2015 18:00:59 GMT

My mistake, I left a closing bracket out of the update I sent you. It should be something like this (but I would urge you to check the update in SQL Developer first. I am not able to check this code here, so am guesstimating)....
"update T_GEO_KEYS set CURRVAL = (select CURRVAL+1 from T_GEO_KEYS where NAME = '" + ((String)globalMap.get("row1.NAME")) +"')"

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Mon, 12 Oct 2015 21:24:16 GMT

Thank you so much!
The SQL is working fine and I do see the error as below
Starting job sequence_generator at 16:23 12/10/2015.
connecting to socket on port 4053
connected
ORA-01407: cannot update ("MDMSTGGEO"."T_GEO_KEYS"."CURRVAL") to NULL
disconnected
Job sequence_generator ended at 16:23 12/10/2015.
Thanks,

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Tue, 13 Oct 2015 00:01:44 GMT

That is probably because there is no data for the NAME. I've also realised that the update has no where clause. At the moment it will update ALL rows with the result of the nested query. You will need to add a where clause to ensure this doesn't happen.
The code I have given was really meant to give you an idea of how you could achieve this. You really need to go through the process manually using SQL Developer to perfect the sql statements. My guesses are not going to be perfect as I only have the brief information that I have derived from reading your posts. I recommend trying the process out manually, perfect the SQL, work on building it using Java and then start testing with Talend.

Re: Generate BATCH_ID on increment with one for every job execution

Anonymous — Tue, 13 Oct 2015 14:50:27 GMT

Thank you Sir!