topic Set context variable from database in Talend Studio

Set context variable from database

Anonymous — Thu, 10 May 2018 14:20:45 GMT

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

Re: Set context variable from database

Anonymous — Thu, 10 May 2018 14:54:47 GMT

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

Re: Set context variable from database

Anonymous — Thu, 10 May 2018 16:04:19 GMT

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

Re: Set context variable from database

Anonymous — Fri, 11 May 2018 20:28:03 GMT

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]

Re: Set context variable from database

Anonymous — Sat, 12 May 2018 05:25:13 GMT

Hi,
In place of the tjdbcrow, use tJavarow.

Re: Set context variable from database

Anonymous — Mon, 14 May 2018 14:25:47 GMT

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