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

[resolved] How to use context variables in Query?

satyavar — Tue, 04 Nov 2014 15:07:43 GMT

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

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

Anonymous — Tue, 04 Nov 2014 15:46:10 GMT

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.

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

satyavar — Tue, 04 Nov 2014 17:22:24 GMT

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

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

Anonymous — Wed, 05 Nov 2014 08:30:16 GMT

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

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

Anonymous — Wed, 05 Nov 2014 08:38:08 GMT

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

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

satyavar — Thu, 06 Nov 2014 07:17:35 GMT

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

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

Anonymous — Thu, 06 Nov 2014 07:55:07 GMT

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

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

satyavar — Thu, 06 Nov 2014 08:00:00 GMT

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.

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

Anonymous — Thu, 06 Nov 2014 08:07:31 GMT

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

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

satyavar — Thu, 06 Nov 2014 08:12:41 GMT

I am using 5.4.2.
Okay ill try your suggestion.

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

satyavar — Fri, 07 Nov 2014 08:23:13 GMT

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.

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

Anonymous — Mon, 10 Nov 2014 07:47:37 GMT

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

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

satyavar — Mon, 10 Nov 2014 08:16:45 GMT

Works like a charm, Thanks.