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

Using results from a Database select statement in multiple Jobs

Anonymous — Sat, 16 Nov 2024 13:19:05 GMT

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

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

Anonymous — Wed, 11 Aug 2010 22:06:41 GMT

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?

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

Anonymous — Thu, 12 Aug 2010 01:11:11 GMT

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.

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

Anonymous — Thu, 12 Aug 2010 08:49:55 GMT

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.

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

Anonymous — Thu, 12 Aug 2010 20:22:12 GMT

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?

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

Anonymous — Fri, 13 Aug 2010 06:14:08 GMT

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

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

Anonymous — Fri, 13 Aug 2010 18:36:53 GMT

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

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

Anonymous — Mon, 16 Aug 2010 06:54:14 GMT

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

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

Anonymous — Tue, 17 Aug 2010 19:26:05 GMT

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.

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

Anonymous — Wed, 18 Aug 2010 06:46:44 GMT

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

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

Anonymous — Wed, 18 Aug 2010 18:43:35 GMT

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.

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

Anonymous — Thu, 19 Aug 2010 06:06:31 GMT

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

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

Anonymous — Thu, 19 Aug 2010 18:21:28 GMT

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?

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

Anonymous — Fri, 20 Aug 2010 09:15:40 GMT

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

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

Anonymous — Fri, 20 Aug 2010 18:56:29 GMT

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.

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

Anonymous — Sat, 21 Aug 2010 08:38:21 GMT

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

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

Anonymous — Sat, 21 Aug 2010 19:36:15 GMT

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

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

Anonymous — Sun, 22 Aug 2010 05:05:15 GMT

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)

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

Anonymous — Mon, 23 Aug 2010 00:13:37 GMT

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.

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

Anonymous — Mon, 23 Aug 2010 05:13:20 GMT

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.