Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
My job design is as follow:
tprejob -> tmysqlinput (date is in date datatype) -> tjavarow tp store the date in the context param
context param is of string type
tjavarow code:
context.last_run = TalendDate.formatDate("yyyy-MM-dd", row9.txndate);
System.out.println("Last run " + context.last_run);
when I am using this to get the data in main job using below query facing issue:
SELECT
a.no,
a.col2,
b.col3
b.datecol -- date datatype
FROM table1 a
inner join table2 b
on a.no=b.no
where b.
datecol
>= ' "+ context.last_run + " 'Error:
Exception in component tDBInput_1 (Copy_of_realtime)
java.sql.SQLException: Incorrect DATE value: ' "+ context.last_run + " '
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1218)
Kindly help. Thanks
what's value of context.last_run printed on the console? is it expected format requires for the query? eg:
"select * from tableName where datecol >='2022-08-01' works.
"select * from tableName where datecol >='01-08-2022' will fail with the error Incorrect DATE value: '01-08-2022'
Regards
Shong
@Shicong Hong
Tjavarow component is giving me the correct date as yyyy-MM-dd.
change your java on tJavaRow to:
context.last_run = TalendDate.formatDate("yyyy-MM-dd", input_row.txndate);
@Shicong Hong
you mean to update row9 to input_row in code?
yes, and try to set a fixed value in the query to test what date format it requires.
@Shicong Hong
It is taking yyyy-MM-dd. Now with context, query is working but result is not correct.
Date records in DB
2022-07-29
2022-07-30
SELECT
a.no,
a.col2,
b.col3
b.datecol -- date datatype
FROM table1 a
inner join table2 b
where b.
datecol
>= ' "+ context.last_run + " '
tjavarow is giving the date as 2022-07-30.
But getting both the dates records from tmysqlinput.
Can you help
@Shicong Hong ,
could you please help here?
Thanks in advance.
use something like
Cast('your format date' as date)
and choose dd-MM-yyyy as pattern
If you want to run the query for each input date, you need to iterate the input data, eg:
tMysqlinput1--row1-->tFlowToIterate--iterate--tJava--onComponentOK--tMysqlInput2-->tLogRow
tMysqlInput1: read the date value from DB
tJava: store the current date value to context variable, eg:
context.last_run = TalendDate.formatDate("yyyy-MM-dd", row1.txndate);
tMysqlInput2: run the main query.