topic Re: Context variable date in mysql query in Talend Studio

Context variable date in mysql query

Anonymous — Sun, 12 Aug 2012 03:38:23 GMT

Hi,
I am having a problem with a datetime context variable in a tMysqlInput component. My query does not return any results, but when I substitute the context variable for the string '2012-08-12 09:18:01' I get results as expected.
So here is my situation.
- The context variable is defined as a string.
- I write the current datetime to the variable (context.BatchTime using a tJava component with:
context.BatchTime = TalendDate.getDate("yyyy-MM-dd hh:mm:ss");
- In my tMysqlInput query I have the following:
"SELECT
`invoicedata`.`RecordCreated`,
`invoicedata`.`meterID`,
`invoicedata`.`identifierID`,
FROM `invoicedata`
where `RecordCreated`= '"+context.BatchTime+ "'"
I have read numerous posts on this subject but I cant see what I am doing wrong. Could a nice Talend expert point me in the right direction please?
thanks,
David

Re: Context variable date in mysql query

alevy — Sun, 12 Aug 2012 05:32:22 GMT

I have to say I can't see anything wrong with what you've done; the common mistake is to forget the single-quote date-delimiters but you have those.
I suggest you add a tJava connected from tMysqlInput with OnSubjobOK with the code: System.out.println((String)globalMap.get("tMysqlInput_2_QUERY")); to see exactly what the SQL is that is being used.

Re: Context variable date in mysql query

Anonymous — Sun, 12 Aug 2012 05:57:38 GMT

Hi David
I can confirm that the query is correct, maybe there is no records match the condition. Try to change the where condition as below for example and see you get records return.
where `RecordCreated`< '"+context.BatchTime+ "'"
Another possible reason is that the context variable is not initialized value before using it, can you upload a screenshot of your job design?
Best regards
Shong

Re: Context variable date in mysql query

Anonymous — Mon, 13 Aug 2012 05:27:45 GMT

Shong and Alevy,
thank you very much for your prompt and informative replies. Embarrassingly in the end it was user error (me) that was at fault. I was writing the date to mysql incorrectly and that's why the query was not working.
Alevy thanks for the tip about: System.out.println((String)globalMap.get("tMysqlInput_2_QUERY")); I will use this in future to debug my queries.
And a final word about Talend - it is awesome! I am doing things with it that I never thought I could achieve. I love it!
thanks,
David