One Star

Context Variable in Query issue

I am having trouble with the job below which retrieves an id from a table and uses that in the next query.
Sync - this table holds an id that I am retrieving.
tJava - sends the ID to a context variable
Contacts - this has a query that get all id's greater than what is stored in the context variable.
The issues is that the context variable gets set ( verified this with tlogrow ) and then the contact query shows the variable as null when you run in debug mode to verify the query is working. Any idea why the context variable would be null in the query?
Query being used which does not present any errors.
"SELECT Contact.i_ContactId,
Contact.vc_Name,
Contact.vc_Title,
Contact.c_Telephone,
Contact.c_Fax,
Contact.vc_Email,
Contact.vc_Address1,
Contact.vc_Address2,
Contact.vc_City,
Contact.vc_State,
Contact.vc_Zip,
Contact.vc_Country,
Contact.b_ProductionDefault,
Contact.vc_UserName,
Contact.vc_Password,
Contact.b_AllowLogin,
Contact.vc_Extension
FROM Contact where Contact.i_ContactId > " + context.Contact_Id +" and Contact.vc_Name != '' "
8 REPLIES
Community Manager

Re: Context Variable in Query issue

Hello
How do you set ID to context var on tJava? Using tJavaRow replace tJava, on tJavaRow, set the ID to context var, eg:
context.Contact_Id=input_row.Contact_Id;

Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Context Variable in Query issue

I used the same technique in tJava that you are suggesting in tJavaRow. Neither of them appear to be doing what I anticipated though. The global variable appear to be set by the tJava component but not when the context variable is used in the query.
Community Manager

Re: Context Variable in Query issue

Hi
Can you show us your code on tJavaRow?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Context Variable in Query issue

Sure:
context.Contact_Id = row2.Last_Id;
Community Manager

Re: Context Variable in Query issue

Hello
context.Contact_Id = row2.Last_Id;

As I said in previous post, please use the tJavaRow instead of tJava and type in expression as:
context.Contact_Id = input_row.Last_Id;

Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Context Variable in Query issue

I have tried using tJavaRow and the code that you posted and that didn't appear to change anything. Also, I notice that when I attache tLogRow to tJava I could se the output of the context variable. When I use tJavarow and tLogRow I do not receive any output. Is there a good was to see the query being used without trying to move to the debug view?
Community Manager

Re: Context Variable in Query issue

Hello
I have tried using tJavaRow and the code that you posted and that didn't appear to change anything.

Strange! Using tJavaRow should be ok, is there only one row query from oracle or many rows? If only one row, redesign your job to:
tOracleInput---row1--tJavaRow
|
onsubjobok
|
the next processing
on tJavaRow:
context.Contact_Id = input_row.Last_Id;
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Seven Stars

Re: Context Variable in Query issue

Is there a good was to see the query being used without trying to move to the debug view?

Use a tJava joined after your input component with OnComponentOK, containing the following (for example), which will print the query SQL to the console:
System.out.println("My query SQL is as follows:\n" (String)globalMap.get("tMSSqlInput_1_QUERY") "\n\n");

How are you using tLogRow to show that the context variable has been set? tLogRow simply dumps the flow to the console...
To check that the context variable is actually set as you expect, include the following in the tJava you use to check the query SQL:
System.out.println("My context variable is set to:" context.Contact_Id "\n\n");

If the above proves that the context variable is set as you expect but it is still not being used in the query SQL (which I doubt), try synchronizing the context as discussed in this post.