Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
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.
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...
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.
globalMap as Date will work and do other casting based on DB