[resolved] How to use context variables in Query?

[satyavar]

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

[Anonymous]

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.

[satyavar]

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

[Anonymous]

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

[Anonymous]

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

[satyavar]

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

[Anonymous]

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

[satyavar]

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.

[Anonymous]

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

[satyavar]

I am using 5.4.2.
Okay ill try your suggestion.