Generate BATCH_ID on increment with one for every job execution

[Anonymous]

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!

[Anonymous]

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".

[Anonymous]

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

[Anonymous]

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). 

[Anonymous]

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

[Anonymous]

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")) +"')"

[Anonymous]

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,

[Anonymous]

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. 

[Anonymous]

Thank you Sir!