topic Re: Date glob. variable within tMysqlInput in Talend Studio

Date glob. variable within tMysqlInput

Romanesku — Sat, 16 Nov 2024 07:51:06 GMT

Hi,

do you have guidance for date values as well?

I have problem with using it in where clause for MySQL.

when I use

" Select...

....WHERE `t_application`.`c_change_date` >= '2018-07-01 00:00:00.000'"

the select return 167 records.

but when I want to use global var

.....WHERE `t_application`.`c_change_date` >= '" + (globalMap.get("last_run")) +"' "

return nothing.

I tried

WHERE `t_application`.`c_change_date` >= '" + ((Date)globalMap.get("last_run")) +"' "

WHERE `t_application`.`c_change_date` >= '" + (globalMap.get("last_run")) +"' "

also with no records.

When I change the part after >=, without single parenthesis ' ... '

WHERE `t_application`.`c_change_date` >= " + ((Date)globalMap.get("last_run")) +" "

This return message where I see the value from variable

...

Sync_AppCat|2018-07-01 00:00:00.000|N|2018-08-07 09:18:26.026

Exception in component tMysqlInput_1 (ReadAPPFromCatalogue)

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Jul 01 00:00:00 CEST 2018' at line 122

...

Thank you

Roman

Re: Date glob. variable within tMysqlInput

TRF — Tue, 07 Aug 2018 09:47:35 GMT

Try the following:
WHERE `t_application`.`c_change_date` >= '" + ((String)globalMap.get("last_run")) +"'"

Re: Date glob. variable within tMysqlInput

Romanesku — Tue, 07 Aug 2018 12:15:37 GMT

Hi,

I tested it with (String), an this is the response

Starting job ReadAPPFromCatalogue at 11:20 07/08/2018.

[statistics] connecting to socket on port 4024

[statistics] connected

2018-07-01 12:00:00.000|Sync_AppCat

Sync_AppCat|2018-07-01 00:00:00.000|N|2018-08-07 11:20:54.054

Exception in component tMysqlInput_1 (ReadAPPFromCatalogue)

java.lang.ClassCastException: java.util.Date cannot be cast to java.lang.String

at localromanproject.readappfromcatalogue_0_4.ReadAPPFromCatalogue.tMysqlInput_1Process(ReadAPPFromCatalogue.java:7892)

....

Roman

Re: Date glob. variable within tMysqlInput

TRF — Tue, 07 Aug 2018 13:20:14 GMT

Right.
As your column is typed as Datetime, you need to use a STR_TO_DATE in your query to convert the value from String to Datetime

Re: Date glob. variable within tMysqlInput

Romanesku — Wed, 08 Aug 2018 09:37:40 GMT

Hi

but the question is how I can use it. Or can I see somewhere the final SQL which is called from Talend? some log...

Regards,

Roman

Re: Date glob. variable within tMysqlInput

Romanesku — Wed, 08 Aug 2018 12:46:01 GMT

But the global variable is datetime value, why I have to sett it to String and then again to Date?

"SELECT

......

`t_application`.`c_retired_date`

FROM `t_application`

WHERE `t_application`.`c_change_date` >= '"+((Date)globalMap.get("last_run"))+"'"

Anyway I tried it, but how I can use this function

WHERE `t_application`.`c_change_date` >= STR_TO_DATE('"+((Date)globalMap.get("last_run"))+"', "%Y-%m-%d %H:%i:%s")"

return compilation error

...

Detail Message: Syntax error on token "")"", delete this token

Re: Date glob. variable within tMysqlInput

cterenzi — Wed, 08 Aug 2018 14:56:19 GMT

Because you have to construct a sql statement in a string to send to the database.

WHERE `t_application`.`c_change_date` >= STR_TO_DATE('"+((String)globalMap.get("last_run"))+"', \"%Y-%m-%d %H:%i:%s\")"

When you want to include quote characters inside a String, you need to escape them.

Re: Date glob. variable within tMysqlInput

Romanesku — Thu, 09 Aug 2018 08:17:47 GMT

Hi,

thank you, this is working, but the script still return zero rows. How can I see the final statement which is executed on DB? Is there any log or option for debugging...

If I use this

WHERE DATE_FORMAT(`t_application`.`c_change_date`, \"%Y-%m-%d %H:%i:%s\") > DATE_FORMAT( '"+(globalMap.get("last_run"))+"', \"%Y-%m-%d %H:%i:%s\")" .... last_run is datetime value fetch from DB

return nothing.

if I use this

WHERE DATE_FORMAT(`t_application`.`c_change_date`, "%Y-%m-%d %H:%i:%s") > '2018-08-01 00:00:00.000'

return 3 rows.

I can not move forward...

Re: Date glob. variable within tMysqlInput

Romanesku — Thu, 16 Aug 2018 12:54:09 GMT

Hi,

the issue solved with following CAST function, but first I have to format value into string

where t_application.c_change_date > Cast('"+ globalMap.get("s_last_run") +"' as datetime)

It is strange workaround but works.

Regards,

Re: Date glob. variable within tMysqlInput

KarthikGs — Wed, 08 Mar 2023 08:01:38 GMT

globalMap as Date will work and do other casting based on DB