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

[BA621858188]

@Shicong Hong​ ,

I dont want to iterate at once. I wanted to use the same query as below

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 + " '

for 1st load - 2022-07-27

It is taking the data greater or equal to 27

for 2nd load - 2022-07-28

It should take data from 2022-02-28 onwards but it is taking data from previous date as well which is wrong.

Is there any wrong in above sql as when I am running the same query in mysql getting data greater than or equal to 2022-07-28 only.

Also tmysqlinput query with hardcode value works fine.

[BA621858188]

@guenneguez jeremy​ 

You mean like below:

where b.

cast(datecol as date)

>= ' "+ context.last_run + " '

The date format which I want is yyyy-MM-dd and it is coming from both the Tjava and Mysql components.

[Anonymous]

@Vasim Ahmed Mwith your job design, the query will run only one time, the last value '2022-07-28' will be assigned to context.last_run.

[BA621858188]

I tried to run the query alone by disabling the other subjobs but still getting all the records including past date.

Query with hardcode value is running fine in mysql.

[BA621858188]

Its still giving past data as well.

[BA621858188]

@Shicong Hong​ I am using query as below > instead of >= :

select col1,col2 from table1 , table2

on table1.no=table2.no

where substring(date_field,1,10)>'"+context.last_run+"'

still It is giving the past date data.

can you help here?

[Anonymous]

Hi

Should use where date_field >'"+context.last_run+"' to filter data on a Date column. For testing, also query date_field column in the select list.

[BA621858188]

@Shicong Hong​ 

Actually while just selecting date_field column I am getting value as 2022-07-22 00:00:00.00 hence I am using substring to compare the exact value.