topic Re: Date issue in Tmysqlinput db component in Talend Studio

Date issue in Tmysqlinput db component

BA621858188 — Fri, 15 Nov 2024 22:41:49 GMT

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

Re: Date issue in Tmysqlinput db component

Anonymous — Mon, 01 Aug 2022 04:45:20 GMT

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

Re: Date issue in Tmysqlinput db component

BA621858188 — Mon, 01 Aug 2022 09:10:12 GMT

@Shicong Hong

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

Re: Date issue in Tmysqlinput db component

Anonymous — Mon, 01 Aug 2022 09:24:23 GMT

change your java on tJavaRow to:

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

Re: Date issue in Tmysqlinput db component

BA621858188 — Mon, 01 Aug 2022 13:07:32 GMT

@Shicong Hong

you mean to update row9 to input_row in code?

Re: Date issue in Tmysqlinput db component

Anonymous — Mon, 01 Aug 2022 14:02:38 GMT

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

Re: Date issue in Tmysqlinput db component

BA621858188 — Mon, 01 Aug 2022 15:44:57 GMT

@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

Re: Date issue in Tmysqlinput db component

BA621858188 — Tue, 02 Aug 2022 09:40:15 GMT

@Shicong Hong ,

could you please help here?

Thanks in advance.

Re: Date issue in Tmysqlinput db component

gjeremy1617088143 — Tue, 02 Aug 2022 09:55:21 GMT

use something like

Cast('your format date' as date)

and choose dd-MM-yyyy as pattern

Re: Date issue in Tmysqlinput db component

Anonymous — Tue, 02 Aug 2022 11:20:52 GMT

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.

Re: Date issue in Tmysqlinput db component

BA621858188 — Tue, 02 Aug 2022 15:50:32 GMT

@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.

Re: Date issue in Tmysqlinput db component

BA621858188 — Tue, 02 Aug 2022 16:02:07 GMT

@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.

Re: Date issue in Tmysqlinput db component

Anonymous — Thu, 04 Aug 2022 03:08:23 GMT

@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.

Re: Date issue in Tmysqlinput db component

BA621858188 — Thu, 04 Aug 2022 08:27:26 GMT

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.

Re: Date issue in Tmysqlinput db component

BA621858188 — Tue, 09 Aug 2022 17:49:03 GMT

Its still giving past data as well.

Re: Date issue in Tmysqlinput db component

BA621858188 — Tue, 09 Aug 2022 17:50:21 GMT

@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?

Re: Date issue in Tmysqlinput db component

Anonymous — Wed, 10 Aug 2022 02:35:40 GMT

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.

Re: Date issue in Tmysqlinput db component

BA621858188 — Wed, 10 Aug 2022 10:36:26 GMT

@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.