Date issue in Tmysqlinput db component

[BA621858188]

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

[Anonymous]

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

[BA621858188]

@Shicong Hong​ 

Tjavarow component is giving me the correct date as yyyy-MM-dd.

[Anonymous]

change your java on tJavaRow to:

context.last_run = TalendDate.formatDate("yyyy-MM-dd", input_row.txndate);

[BA621858188]

@Shicong Hong​ 

you mean to update row9 to input_row in code?

[Anonymous]

yes, and try to set a fixed value in the query to test what date format it requires.

[BA621858188]

@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

on a.no=b.no

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

[BA621858188]

@Shicong Hong​ ,

could you please help here?

Thanks in advance.

[gjeremy1617088143]

use something like

Cast('your format date' as date)

and choose dd-MM-yyyy as pattern

[Anonymous]

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.