Date glob. variable within tMysqlInput

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


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.


All Replies
Fifteen Stars TRF
Fifteen 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

Fifteen Stars TRF
Fifteen 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.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now