Set context variable from database

[Anonymous]

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'
;"

[Anonymous · Accepted solution]

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

[Anonymous]

Hi Claire,

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

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.

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

Hope this helps.

Thanks,

RekhaSree

[Anonymous]

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.  

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+ "'
;"

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]

[Anonymous]

Hi,
In place of the tjdbcrow, use tJavarow.

[Anonymous]

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