One Star

Generate BATCH_ID on increment with one for every job execution

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!
17 REPLIES
One Star

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

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,
Fifteen Stars

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

There is no screenshot?
Rilhia Solutions
One Star

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

Please find the attached screenshot
 
Thanks!
Fifteen Stars

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

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

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

Hi,
Attached is the screenshot for DB component.

 



Thanks!
Fifteen Stars

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

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

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

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!
  
Fifteen Stars

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

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.
Rilhia Solutions
One Star

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

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
Fifteen Stars

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

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".
Rilhia Solutions
One Star

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

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
Fifteen Stars

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

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). 
Rilhia Solutions
One Star

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

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
Fifteen Stars

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

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")) +"')"
Rilhia Solutions
One Star

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

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,
Fifteen Stars

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

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. 
Rilhia Solutions
One Star

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

Thank you Sir!