One Star

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
22 REPLIES

Re: Using results from a Database select statement in multiple Jobs

hi
i assume
"transaction_ID" => is the value returned by the DB
"global_tran_id" => name of your talend variable
you call your jobs from the main/parent job using tRunJob and click the tick-box 'transmit whole context' (do not double click on tRunJob - click once and then click on TAB component)
in this way *ALL* variable from your parent job will be sent to your child jobs.
is this what your are asking?
One Star

Re: Using results from a Database select statement in multiple Jobs

Actually the transaction_id is what I use to find the global_tran_id.
Basically I write a select statement the following way.
select * from (table1) where transaction_id = '12345'
From here I get the results which include a global_tran_id this number which is associated with
the transaction_id has multiple records inside multiple tables. I want to be able to use
the global number to find all the records inside all of the different tables.
I want to then use the global_tran_id to find all of the other records in different tables.
So what I want to do is place the global_tran_id inside a variable and use that number inside another
select statement. In a different job.
Example: select* from (table2) where global_tran_id = (global_tran_id result from first select statement).
Please keep in mind. I will have another job with the second select statement. I need to be able to have multiple jobs
so that I could have up to twenty different jobs with twenty different select statements all based off that global_tran_id.
I want to be able to populate all the jobs with the same global_tran_id and run the select statements. I understand that I can run multiple jobs. It is totally a matter of learning how to pull that global_tran_id into all the different jobs and place them inside a new select statements.
One Star

Re: Using results from a Database select statement in multiple Jobs

Hi
put our output from select to TjavaRow. Pass the global_tran_id in the output to a context variable. Later you can use this context variable for other queries.
One Star

Re: Using results from a Database select statement in multiple Jobs

I keep getting the following error output row cannot be resolved.
The way I have it set up is by running the select inside an oracle connection.
From there I do a main connection to the TJavaRow.
I have set up in the context variable option the key and value.
Using the TJavaRow I have selected generate code however, it comes up with the following
//Code generated according to input schema and output schema
output_row.key = input_row.key;
output_row.value = input_row.value;
Any ideas?
One Star

Re: Using results from a Database select statement in multiple Jobs

Hi
Please do sync columns and press Generate code. For your use where the u can
add extra line context.global_tran_id = input_row.global_tran_id (this case will work only when u have only one row as output)
later u can pass the context variable another job for your process.
Hope this one helps
One Star

Re: Using results from a Database select statement in multiple Jobs

Still getting the error.
Here is the exact message.
Exception in thread "main" java.lang.Error: Unresolved compilation problems:
output_row cannot be resolved
output_row cannot be resolved
row1.global_tran_id cannot be resolved or is not a field
One Star

Re: Using results from a Database select statement in multiple Jobs

Hi
I have attached a screen shot for you help. there can only be input_row.(values) and output_row.(values) in tavaRow apart from routine and context values.
If you click the code part of the screen then you can see the errors.
Hope this one helps you
One Star

Re: Using results from a Database select statement in multiple Jobs

This is still not placing the information into the variable.
Also since the data that comes originally from the table schema is in a column which is a bigDecimal the
context variable will not work with a big decimal variable. It keeps stating the following
Exception in thread "main" java.lang.NumberFormatException
at java.math.BigDecimal.<init>(Unknown Source)
at java.math.BigDecimal.<init>(Unknown Source)
at lsie_test_project.global_transaction_id_0_1.Global_Transaction_ID.runJobInTOS(Global_Transaction_ID.java:1204)
at lsie_test_project.global_transaction_id_0_1.Global_Transaction_ID.main(Global_Transaction_ID.java:1124)
The context variable that I set up on the screen is a context.value and the type is just like the schema a BigDecimal. Even if I have editted the schema so that it is there the only time I get any answer is when it is a string variable and it will not place the information into the variable.
Still would like to be able to place the BigDecimal result from a select statement.
The schema reads: Transaction_id which is a string
Global_tran_id which is a BigDecimal
In the context variable I set up the variable as a BigDecimal
The tjavarow information is setup the following way.
//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;
And above is my what I have done...I am still having problems getting the results from one select statement into the actual variable.
Once I have this then I will need to create a new select in a different job which I want to use this global value in other table schema's
Any help would be greatly appreciated.
One Star

Re: Using results from a Database select statement in multiple Jobs

Hi
Kindly give default value as 0 for the context variable, then it will work properly
One Star

Re: Using results from a Database select statement in multiple Jobs

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

Re: Using results from a Database select statement in multiple Jobs

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
One Star

Re: Using results from a Database select statement in multiple Jobs

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?
One Star

Re: Using results from a Database select statement in multiple Jobs

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
One Star

Re: Using results from a Database select statement in multiple Jobs

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

Re: Using results from a Database select statement in multiple Jobs

Hi
The statement i have given is to check your console with context variable.
have you checked transit all context in tRunjob
One Star

Re: Using results from a Database select statement in multiple Jobs

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
One Star

Re: Using results from a Database select statement in multiple Jobs

hi
can u upload the screen shot of your job
how many rows are the select statement in parent job returning. (I am expecting it to be one)
One Star

Re: Using results from a Database select statement in multiple Jobs

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

Re: Using results from a Database select statement in multiple Jobs

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

Re: Using results from a Database select statement in multiple Jobs

I had been using a tOracleInput.
with the following sql statement: SELECT * FROM table1 WHERE TRANSACTION_iD = '192890'
Ist the tJavaRow the code I have:
//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;
System.out.println(context.GLOBAL_TRAN_ID);
Followed by the tLogRow
Is there a different way with the tOracleOutput?
I am not familiar with a way where I can attach select statments to tOracleOutput?
One other point. The problem first is getting the first job to print the context.GLOBAL_TRAN_ID variable.value in the
proper place before moving on to the second and third job.
Right now I am not even seeing the global value in the first job.
Thanks
One Star

Re: Using results from a Database select statement in multiple Jobs

hi
I think the context value is printing properly
as you can see after the connected , the context value is printed (52473), so there should not be any problem there.
connecting to socket on port 3558
connected
52473
.--------------+--------------.
| tLogRow_1 |
|=-------------+-------------neutral
|TRANSACTION_ID|GLOBAL_TRAN_ID|
|=-------------+-------------neutral
|192890 |52473 |
'--------------+--------------'
disconnected
Job Global_Transaction_ID ended at 16:05 22/08/2010.

hope its correct
One Star

Re: Using results from a Database select statement in multiple Jobs

It is printing it to the results screen after you run the job however, it is not printing it inside the context variable.
When I look at the screen it shows
Name Value
GLOBAL_TRAN_ID 0
So although it is showing the information in the results window it is not moving the information
into the actual variable.
I need this information in the actual context variable so that I can use it in other jobs.
Eventually I will be taking it to a level where I will us this value in 10 or 20 jobs. But above all else
I need to see it in the first so before moving on to the next job. Hope this makes
some sense.
Here is something else I have tried. I switched out my tlog_row with a tContextLoad
I set the operation up to print out all the operation and error if a variable in context but not loaded.
This is the response I got.
Starting job Global_Trans_ID_01 at 13:26 25/08/2010.
connecting to socket on port 3535
connected
tContextLoad_1 set key "192890" with value "52473"
Error: Parameter "GLOBAL_TRAN_ID" has not been set by tContextLoad_1
disconnected
Job Global_Trans_ID_01 ended at 13:26 25/08/2010.
So the 52473 number is not being loaded into the context variable.
Any ideas?
Largent