Using results from a Database select statement in multiple Jobs
I have set up Talend with an Oracle database.
I will then run a select against a table it comes back with the key "transaction_ID" and then value "global_tran_id"
I want to take what is inside the value key number and place it into a variable so that I can use it in another job.
However, it doesn't seem to want to do this.
How can create this so that I can use it in multiple select statements.
What I am referring to is that once I have the global tran number I want to use that number in several selects which are in
different jobs. How can I do that?
Thanks
Killoran
Okay...I have done that and it does work. However, I am unable to use the context variable in the next job. This job uses the context variable in a different select statement.
The statement I use is the following
"SELECT * FROM table1 where global_tran_id = 'context.GLOBAL_TRAN_ID'"
Exception in component tOracleInput_1
java.sql.SQLException: ORA-01722: invalid number
To me this means that when I create the new statement the context variable is not being read from the Oracle table.
Hi First of all the sql statement given by you is wrong it should be "SELECT * FROM table1 where global_tran_id = '"+context.GLOBAL_TRAN_ID+"'" Secondly while call the tRunjob component , click the checkbox transit whole context in the subjob there should be a context variable with same name i.e. context.GLOBAL_TRAN_ID here also the default value should be set to 0
I set it up the way you suggested however, it looks like the variable is not being set from first job.
Here is the java code that I used.
//Code generated according to input schema and output schema
output_row.TRANSACTION_ID = input_row.TRANSACTION_ID;
output_row.GLOBAL_TRAN_ID = input_row.GLOBAL_TRAN_ID;
context.GLOBAL_TRAN_ID = input_row.GLOBAL_TRAN_ID;
When I go to the second job and try it the system runs but since variable information is not set correctly I do not get any information on that global_tran_id.
Any ideas?
Hi
Try to put a System.out.println(context.GLOBAL_TRAN_ID); in tJavaRow and check weather the value is being printed correctly.
Please remove the statement System.out.println after the testing
I tried it with your statement, the results were I still get the information in the log_row results. So the numeric value
which I want is in the results screen however, the value is not be populated to the context variable.
I really need to be able to move this value into other tables so that they can be used throughout.
The best way to describe it is I used all of the code which you gave me.
From there I ran the job. All of the transaction information is in the results area.
However, the context variable remains 0 as a value.
So while the information is present in the results area the variable isn't being updated.
Thanks
I can't show everything on my screen due to corporate security. However, this what I can show. There is one row and two columns per transaction_ID In the context variable screen area you see GLOBAL_TRAN_ID as the name and the value is 0 Below is the result Starting job Global_Transaction_ID at 16:05 22/08/2010. connecting to socket on port 3558 connected 52473 .--------------+--------------. | tLogRow_1 | |=-------------+-------------=| |TRANSACTION_ID|GLOBAL_TRAN_ID| |=-------------+-------------=| |192890 |52473 | '--------------+--------------' disconnected Job Global_Transaction_ID ended at 16:05 22/08/2010.
hi I have tried the same type of job in my system, its working properly I used the components as below tOracleoutput---------------->tJavaRow(in this i assigned my input_row variable to context variable i.e. global_id)--->tRunJob(transit whole context checked) in the subjob i created a context variable same as the variable name in my parent job SubJob tJavaRow (println (context variable)) The below method is working properly, printing my context variable in parent job with values in subjob.
