[resolved] How to use context variables in Query?

One Star

[resolved] How to use context variables in Query?

Hi
Can some one please let me know if it is possible to update the context variables? If yes, how?
and is it possible to use the context variables in Query area of a Input table object? (Not using tFilterRow)
Best Regards,
Kalyan

Accepted Solutions
Four Stars

Re: [resolved] How to use context variables in Query?

Datetime context variable set it to Date...
before using the query in component, use tjava and use print statement and check the correctness of the query using query browser... else make appropriate changes in the datatype or casting operations...
vaibhav

All Replies
Five Stars

Re: [resolved] How to use context variables in Query?

You can update a context variable.
context.new1 = "this is some text.";
context.new1 = context.new1 + " this is some more text";
If you want to use a context variable in a Query, then you're just using String concatenation.
"select ID, Name from " + context.myTable
of you can use String.format; which can look a little more readable sometimes.
One Star

Re: [resolved] How to use context variables in Query?

Hi
Thanks for your response. I am trying to access a date stored in context variable in my oracle input query to apply a where clause on date field. I get the below error, while trying to access the date field and apply it in select query:
I am trying to access the context variable in the following way:
"select col1, col2, col3dtcol from tblName where col3dtcol > " + context.myconfigdate
Exception in component tOracleInput_1
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:852)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1477)
    at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:392)
    at sandbox_kalyan_project.cdc_s_evt_act_0_1.CDC_S_EVT_ACT.tOracleInput_1Process(CDC_S_EVT_ACT.java:8359)
disconnected
    at sandbox_kalyan_project.cdc_s_evt_act_0_1.CDC_S_EVT_ACT.runJobInTOS(CDC_S_EVT_ACT.java:16931)
    at sandbox_kalyan_project.cdc_s_evt_act_0_1.CDC_S_EVT_ACT.main(CDC_S_EVT_ACT.java:16763)
Best Regards,
Kalyan
Moderator

Re: [resolved] How to use context variables in Query?

Hi kalyangupta,
You can switch to "Code" mode to check the error java code to see if there is something wrong with your sql query. Your job setting screenshot will be preferred
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: [resolved] How to use context variables in Query?

Hi Kalyan,
"select col1, col2, col3dtcol from tblName where col3dtcol > " + context.myconfigdate
>> I think the date is in string format and not in date, so try following way
"select col1, col2, col3dtcol from tblName where col3dtcol > '" + context.myconfigdate+"'"

Also you can test above query in tjava using print statement you can identify an issue in query syntax.. use printed query in client to check the results.
thanks
vaibhav
One Star

Re: [resolved] How to use context variables in Query?

Hi
Here is what i am trying to do. Screenshot 1 has the input columns with their data type. 2nd and 3rd screenshots has the context variable settings and 4th screenshot shows how i am trying to use context variable in Query part.
Please let me know how to access the date context variable in query part of input table to filter based on the value available in context date variable.
Best Regards,
Kalyan
Four Stars

Re: [resolved] How to use context variables in Query?

Hi,
Remove dd-mm-yyyy and semicolon part from the value section for built in context. Rest is ok.
Vaibhav
One Star

Re: [resolved] How to use context variables in Query?

How do i update the it? It is readonly and i cannot update the value of context. I am selecting the value from a calendar popup which opens up.
Four Stars

Re: [resolved] How to use context variables in Query?

This might be a new change, which version of Talend are you using...
- Another quick turn around is, use tPreJob-->tJava component and set the required value for the context variable 
context.myconfigdate = ''01-01-2000";

And try again.

thanks
Vaibhav
One Star

Re: [resolved] How to use context variables in Query?

I am using 5.4.2.
Okay ill try your suggestion.
One Star

Re: [resolved] How to use context variables in Query?

Thanks Vaibhav, I have started working with your suggestion. But i would need to surpass one last hurdle to complete this job.
As you suggested, I have taken the approach of PreJob -> Which reads the datetime from a sql table and updates a context variable of Date type.
Then, i am using the context variable value to use it in where clause of a oracle table.
But i am getting some exceptions like below: Exception in component tMSSqlInput_1
java.sql.SQLException: Unable to convert between net.sourceforge.jtds.jdbc.DateTime and INTEGER.
Four Stars

Re: [resolved] How to use context variables in Query?

Datetime context variable set it to Date...
before using the query in component, use tjava and use print statement and check the correctness of the query using query browser... else make appropriate changes in the datatype or casting operations...
vaibhav
One Star

Re: [resolved] How to use context variables in Query?

Works like a charm, Thanks.