Set context variable from database

Four Stars

Set context variable from database

I want to pull a max value from a database and use it as the value of a context variable in a job.  I've tried things with tJDBCInput and tContextLoad, and threw in some tMap too, but haven't been successful.  This seems like a reasonable request that Talend should be able to do, but I can't find any documentation for how to make it happen- just documentation from a delimited file.  Thanks for any help you can offer!

 

Context Variable: context.Process_Max_Section_Date

 

JDBCInput Query:

"select concat( 'Process_Max_Section_Date=' , max(max_section_date) )
from repository.public.processing_control_sections
where completion_date is null
and ready_to_process_date is not null
and section = 'SALES'
;"


Accepted Solutions
Seven Stars

Re: Set context variable from database

Hi,
You can do one thing -
1) In JDBCInput use below query -
select max(max_section_date) AS MaxDate
from repository.public.processing_control_sections
where completion_date is null
and ready_to_process_date is not null
and section = 'SALES'
2) Put MaxDate in the schema of tJDBDInput.
3) After that use tJavaRow and use below code -
context.MaxDate = input_row.MaxDate
Don't forget to add this context variable in the context section.

Best Regards,
Abhishek

All Replies
Seven Stars

Re: Set context variable from database

Hi,
You can do one thing -
1) In JDBCInput use below query -
select max(max_section_date) AS MaxDate
from repository.public.processing_control_sections
where completion_date is null
and ready_to_process_date is not null
and section = 'SALES'
2) Put MaxDate in the schema of tJDBDInput.
3) After that use tJavaRow and use below code -
context.MaxDate = input_row.MaxDate
Don't forget to add this context variable in the context section.

Best Regards,
Abhishek
Employee

Re: Set context variable from database

Hi Claire,

 

You could achieve this by using tJavaRow. I have a job for you to refer. Iam using MySQL. 

job_design.jpg

 

 

I have created a context with name 'context_from_DB' , type=date.

in My tDBInput i have written a query : "SELECT   max(moment) FROM world.stats_table" 

In tJavaRow = 

System.out.println(row2.moment); Here moment is my column name in tDBInput_1.
context.context_from_DB=row2.moment;

 

and then in my filter i am using this context to filter the records as shown below.

filter.jpg

 

so I am able to select max value from db and use it in a component.

 

Hope this helps.

 

Thanks,

RekhaSree

Four Stars

Re: Set context variable from database

Thank you Abhishek and RekhaSree,

I'm SO CLOSE, but still getting an error. At the JDBCRow it is returning a syntax error at position 0 for the string that is returned from JDBCInput.  Do you have any ideas of what may be off?  That error makes me think that component isn't expecting any input.

 

In the below you'll see date fields that are strings, which is intentional.  It's data coming in from a vendor, and has a variety of formats.  

 

Variable1.PNGVariable2.PNG

tJDBCInput_1: "select max(max_section_date) as MaxDate

from repository.public.PROCESSING_CONTROL_SECTIONS
where completion_date is null
and ready_to_process_date is not null
and section = '"+context.Process_SectionName+ "'
;"

Variable3.PNG

tJDBCRow_66:

context.Process_Max_Section_Date = row1.MaxDate;

 

Error on the tJDBCRow_66:

Starting job TalJb_Del_Staging_to_Public_Section_Sales at 14:58 11/05/2018.

[statistics] connecting to socket on port 3527
[statistics] connected
SQL compilation error:
syntax error line 1 at position 0 unexpected '20180508'.
[statistics] disconnected
Job TalJb_Del_Staging_to_Public_Section_Sales ended at 14:58 11/05/2018. [exit code=0]

Seven Stars

Re: Set context variable from database

Hi,
In place of the tjdbcrow, use tJavarow.
Four Stars

Re: Set context variable from database

Thank you!!!  You totally said that before, and my brain apparently got stuck in the jdbc world.  Everything now works Smiley Happy