Need help to use database sequence in Talend Open studio

[Anonymous]

Hi,
I need to load a file to a database table.
Before loading the file to the table, I need to log the file information like file name, # of records to another database table (let me call it audit table), which contains a sequence generated id.
I need to be able to use this sequence generated id in my destination table while I load the records from the file.
Can you please suggest how to generate a sequence from Talend Open studio, insert file information mentioned above in the audit table, and store this sequence id in a variable/context to be re-used in further Data Integration processes.
Also, I need to maintain both the table loads in a transaction.
For example, if my destination table is not loaded for some reason, I need to rollback the file information row created in the audit table. How can I achieve this?
Your help will be much appreciated.
Thanks
Vikas

[Anonymous]

Hi,

Before loading the file to the table, I need to log the file information like file name, # of records to another database table (let me call it audit table), which contains a sequence generated id.
I need to be able to use this sequence generated id in my destination table while I load the records from the file.

Could you please elaborate your case with an example with input and expected output values?
Best regards
Sabrina

[Anonymous]

This is a common task that is not 100% straightforward to implement. But not to worry, its not really very hard once you understand how to design.
first, you want everything to be contained in a transaction-- to do this talend gives you a component that connects to the DB and starts a transaction(t<db>Connect), and then another to commit it(t<db>Commit), and another to rollback(t<db>Rollback).
assuming you are using oracle, your job would end up looking something like the screenshot below.
The key here is to configure all your database components to use the connection made by tOracleConnection_1. this will place all their queries inside a transaction. The Commit/Rollback at the end of the job completes this transaction.
The next step is to get the file info you need and save it in context.
Then log this info to your Audit table, generating a sequence.
Query the Audit table for the sequence you just created, save it in context.
Load the data table using the context var to populate the LOAD_ID.
Commit the transaction, or rollback if an error occurred.

[Anonymous]

Thanks JohnGarrettMartin,
I really liked the way you have broken the problem and explained in detailed steps.
However, I'm pretty new to Talend.
I'm unable to create a similar job.
Can you upload the job file of the picture that you uploaded.
That will help me understand the job details quicker.
Thanks
Vikas

[Anonymous]

Hi,
Based on previous threads, I have tried to create a context group with a few variables like file name, sequence id.
I need to load the values into the context group at runtime.
Can you please explain me how to different context variables in different subjobs. For example, variable file name has to be loaded in one subjob and the audit table sequence id has to be saved in the context variable in a different subjob.
I want to use these saved context values in my subsequent subjobs.
Your help will be much appreciated.
Thanks
Vikas

[Anonymous]

I am facing another strange issue here.
I have defined a context group and a context variable by the name of "id" - I will use this variable to store my max audit id.
In one of the subjobs, I get the max of audit id from the table and save it into this context variable using a tJava component.
In my next subjob, I want to be able to use this id. However, it is coming null.
Please tell me where I am going wrong and suggest a way to resolve it.
Thanks
Vikas