Six Stars

Date glob. variable within tMysqlInput

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

at

...

 

Thank you

 

Roman

1 ACCEPTED SOLUTION

Accepted Solutions
Six Stars

Re: Date glob. variable within tMysqlInput

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,

 

r.

8 REPLIES
Forteen Stars TRF
Forteen Stars

Re: Date glob. variable within tMysqlInput

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

TRF
Six Stars

Re: Date glob. variable within tMysqlInput

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)

at

....

 

Roman

Forteen Stars TRF
Forteen Stars

Re: Date glob. variable within tMysqlInput

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

TRF
Six Stars

Re: Date glob. variable within tMysqlInput

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


Six Stars

Re: Date glob. variable within tMysqlInput

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

Ten Stars

Re: Date glob. variable within tMysqlInput

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.

Six Stars

Re: Date glob. variable within tMysqlInput

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

Six Stars

Re: Date glob. variable within tMysqlInput

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,

 

r.