topic Re: dynamically updating the context variable in Talend Studio

dynamically updating the context variable

Anonymous — Tue, 24 Dec 2013 12:59:08 GMT

Hi,
We are planning to implement the Incremental Extract, our source is SqlServer and Target is Oracle.
In our source DB we have column called timestamp, using this column i have to perform a incremental load.
Each time when the job runs i have to capture the maximum value of the timestamp and i will be using that particular value during my next run dynamically. So i have created a context as Last_Run_Timestamp which should dynamically capture the max value of the Timestamp.
In order to do this,
In the tMSsqlInput component wrote a query as "select max(timestamp) from <tablename>", after that i have made this as input to tjavarow where i wrote a code as
context.Last_Run_Timestamp=row2.Timestamp
but the context is not getting updated...!!!
Thanks
Arul

Re: dynamically updating the context variable

Anonymous — Tue, 24 Dec 2013 22:46:14 GMT

Could you show us the whole job design?
Did the tJavaRow receive a row (shown in the metrics of the flow to tJavaRow) ?
The code works only if at least one row will be delivered.

Re: dynamically updating the context variable

Anonymous — Thu, 26 Dec 2013 08:21:40 GMT

Hi,
Yeah, it is getting 1row as input. In order to verify that i have tried placing a tlogrow component where am getting the one value(i.e) max(timestamp) value.
While defining the context, in the place of default i have entered a timestamp value, if i keep the value as null means am gettin a error in Where Clause of the query which i wrote in the main job.
Code:
Select eventid, viodate,status from <tablename> where timestamp>=context.Last_Run_Date.
Inputs would be very useful.
Thanks
Arul

Re: dynamically updating the context variable

Anonymous — Fri, 27 Dec 2013 07:41:28 GMT

Hi,
The main job will populate some columns from source to target in which am using the condition as
where timestamp>=context.Last_Run_Timestamp.
In the Subjob i have to find the Max(Timestamp) from the source and have to update them to the context variable, so that i will make use of that in the next run to do incremental extract.
The 2nd pic has the value as NULL which comes out from the t_javarow.
But in the place of t_javarow if i place tlogrow means am getting correct output.
The code i wrote in the t_javarow:
context.Last_Run_Timestamp=row2.Timestamp;
I have no clue y am getting the output from t_javarow as NULL.
can u guys help me to resolve this..

Thanks
Arul

Re: dynamically updating the context variable

Anonymous — Mon, 30 Dec 2013 13:48:12 GMT

Any one there..??

Re: dynamically updating the context variable

Anonymous — Mon, 30 Dec 2013 14:12:40 GMT

Please take care the schema of your tMASqlInput_2 component fits exactly to the result field of the query. The matching between the result set fields and the schema columns will be done by the position not by the name!
And please note, most people from Talend and most users here on vacation currently. This could cause some delays of answers.

Re: dynamically updating the context variable

Anonymous — Mon, 30 Dec 2013 14:28:02 GMT

Hi,
Any one there..!!
Will explain my requirement more clearly. My source table has a audit column TimeStamp, In order to perform a incremental load, i need to find the Max(Timestamp) in Each run(Subjob) and will pass this value onto the main job, and the value will be used in the where Clause
code:
where timestamp>=context.Last_Run_Timestamp
in which the context.Last_Run_Timestamp will hold the max(Timestamp) value which is calculated in the Subjob.
tMSSQLinput--tMSSqlOutput
|
subjob ok
|
tMSSQlinput--tmap--tJava_Row
In the subjob tMSSqlInput i wrote a code
select max(Timestamp) from table Name -- tmap -- tJava_Row
In tJava_Row i wrote the code as
code:
context.Last_Run_Timestamp=out1.Timestamp
System.out.println(context.Last_Run_Timestamp)
While i tried Printing the Context.Last_Run_Timestamp am getting the Correct value, but it not reflecting in the
First tMSSQlinput where clause..!!
Help me out to resolve this issue..!!
I have also attached the Screen Shot.

Re: dynamically updating the context variable

Anonymous — Mon, 06 Jan 2014 10:30:05 GMT

updates on this..?
Still am facing the same problem..

Re: dynamically updating the context variable

Anonymous — Mon, 06 Jan 2014 11:10:45 GMT

Hi
To access the input data flow on tJavaRow, the format is as below:

context.Last_Run_Timestamp=input_row.Timestamp;

I would suggest you to read a KB article: the difference between tJava, tJavaRow and tJavaFlex
Shong

Re: dynamically updating the context variable

Anonymous — Mon, 06 Jan 2014 11:15:51 GMT

hi,
use load context component for that.
1.read your max value from table
2.store in globalMap with "flow to iterate component"
3. create key,value flow with "fixed flow input"
4. load key,value with context load (context parameter must exist in your context field)
tip : use print operation of contextLoad to validate parameters load to your application

Starting job truc at 12:07 06/01/2014.

 connecting to socket on port 3999
 connected
tContextLoad_1 set key "max" with value "M308RA4029"
 context max :M308RA4029
 disconnected
Job truc ended at 12:07 06/01/2014.

regards
laurent

Re: dynamically updating the context variable

Anonymous — Mon, 06 Jan 2014 12:38:27 GMT

Hi,
Thanks for the reply..
Am able to get the Max(Timestamp) value in my Subjob. Next step would be i have to store it a Context Variable.
As suggested i have written a code in tjava_row as
Last_Row_Timestamp=input_row.Timestamp.
When i print the context variable value am getting correct output. But, when i use that context variable in Main job where clause as
where Timestamp> "+context.Last_Run_Timestamp+" am getting the following error.
The requirement would be get the max(Timestamp) value in the Subjob, store that in a Context Variable(Last_Run_Timestamp)
Use that Context Variable in the Main Job(Where Clause) during the Next Run, to get the record greater than the previous run Date.

connected
Exception in component tMSSqlInput_3
java.sql.SQLException: Incorrect syntax near '>'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1304)
at dw_dev.copy_of_test_timestamp_child_0_1.Copy_of_Test_Timestamp_Child.tMSSqlInput_3Process(Copy_of_Test_Timestamp_Child.java:1393)
at dw_dev.copy_of_test_timestamp_child_0_1.Copy_of_Test_Timestamp_Child.runJobInTOS(Copy_of_Test_Timestamp_Child.java:1777)
at dw_dev.copy_of_test_timestamp_child_0_1.Copy_of_Test_Timestamp_Child.main(Copy_of_Test_Timestamp_Child.java:1639)
disconnected
Job Copy_of_Test_Timestamp_Child ended at 07:26 06/01/2014.

Re: dynamically updating the context variable

Anonymous — Mon, 06 Jan 2014 13:34:20 GMT

so if I understand your need you have to pass value to a "parent job".
for that use bufferOutput component and in the Main job retrieve schema and value with copy child job schema.
hope it helps
regards
laurent

Re: dynamically updating the context variable

Anonymous — Mon, 06 Jan 2014 13:45:43 GMT

Hi,
I went through threads with tBufferoutput, am little confused.
Can u please provide a screen shot of how to implement this. That would be really helpful.
Thanks

Re: dynamically updating the context variable

Anonymous — Mon, 06 Jan 2014 14:25:57 GMT

here some explanation :
main job call child job that read the max value of something and return result to main job.
in my test the context param "MAX" default value = NULL .
Child job get the value from sql query and return this value via buffer output to main job.
Then Max Value of main job is now = to the value passed by child job ...

Starting job main at 15:23 06/01/2014.

 connecting to socket on port 3609
 connected
2013-12-16
 disconnected
Job main ended at 15:23 06/01/2014.

hope it helps
regards

Re: dynamically updating the context variable

Anonymous — Thu, 31 Mar 2016 12:10:10 GMT

Hi,
I want to iterate a workflow where I want to select a particular column from a csv file depending upon value of context variable, say i. So at first iteration, i =1 so i want to select column named t1. At the end of first iteration, my i becomes 2 (using tjava component here). Now I want to select column named t2. But when I use tjavarow for getting the value of column from column in csv, it doesn't allow me to write context.col_name = input_row."t"+i. (ultimately I want input_row.t2). How can I do this job in Talend?
Thanks and regards,
Himani