[resolved] Parameterize Salesforce Input Query using variables

[Anonymous]

All,
I am trying to parameterize a Salesforce input so that I only get records from Salesforce WHERE LastModifiedDate > the last run date of the job and LastModifiedDate < current datetime.
Basically, I am getting the last run date of the job from a SQL server database table using a SQL server input and the query does a SELECT TOP 1 LastRunDate FROM table.
Then the main flow goes to a tJava component to set my variables as follows:
context.lastRunDate = row3.LastRunDate;
context.currentDate = TalendDate.getCurrentDate();
When I use a message box to print the date, they appear correct; however, when I try to use them in my query condition of Salesforce input component I get error(s). The error that appears looks like the lastRunDate variable is null. Please refer to attachment.
I know this post is similar to a post from 2009 that is marked resolved, but I didn’t see any resolution in the post so I am creating a new post due to the age of the similar post. Any assistance would be greatly appreciated.   
     

[Anonymous]

Have you got a screenshot of your Job, the error message and your constructed SOQL statement?

[Anonymous]

Have you got a screenshot of your Job, the error message and your constructed SOQL statement?

Thank you for your reply. The screenshots didn't attach the first time. I've added them now. 

[Anonymous]

with you're two subjobs disconnected, Talend will run them in parallel ( if multi-thread execution is checked in the Job->Extra tab )
best way to fix this is to ensure proper order by connecting the subjobs with a subJobOK link

[Anonymous]

It looks like my current issue is down to the way the date is format (maybe) or how it is being stored in the parameter not being compatible with the Salesforce API. 
Hard coding a date like the screenshot below works. However, when I try to hard code the dates in the context variables and pass the variables in, it does not work. 
 
 

[Anonymous]

Sorry if I was not very clear before. The issue is what it says in the error: the operator less than (<) is invalid for the types date and null  -- LastModifiedDate < null
This indicates that the value of context.lastRunDate is null -- likely because it is not being set before you execute your query. 

[Anonymous]

Sorry if I was not very clear before. The issue is what it says in the error: the operator less than (<) is invalid for the types date and null  -- LastModifiedDate < null
This indicates that the value of context.lastRunDate is null -- likely because it is not being set before you execute your query. 

Thank you, I understand. I am confused though how to make sure the context variables are set before the query execute. I have given the context variables default values to test with but still get the null value error. 
I apologize if this seems like a dumb question, I've been using Talend for about 3 days so I'm still pretty new to this tool. 

[Anonymous]

hmm. it does look like you've given defaults correctly, so they should be used if there are no other components in your job. weird. Sometimes the talend client gets a little confused, so it cant hurt to close the studio and re-open it and see if that clears things up. 
if its still acting silly, you should take that query string and print it out in a tJava prior to executing the query-- if the context vars are null, try assigning them a value in that same tJava. 

[Anonymous · Accepted solution]

Thanks for all the help. It helped me resolve my problem. Here is what I did to resolve the problem. 
First, I added a tFlowToIterate between my MSSQLInput and my tJava, then in my tJava I user the formatDateUnUTC function to format the date. I change the conttext variables to string datatype. Then I was able to use them in the Salesforce query condition field. 
context.lastRunDate = TalendDate.formatDateInUTC("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",row3.LastRunDate);
context.currentDate = TalendDate.formatDateInUTC("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", TalendDate.getCurrentDate());