nauu commented on issue #1439: Improve ParameterUtils.setInParameter, Use Datetype instead of String. URL: https://github.com/apache/incubator-dolphinscheduler/pull/1439#issuecomment-564434883 I have been tested with db2、mysql、psql and hive in my development environment. DB2: ``` [INFO] 2019-12-11 16:15:31.281 - [taskAppId=TASK-9-59-75]:[94] - sql task params {"postStatements":[],"connParams":"","receiversCc":"","udfs":"","type":"DB2","title":"111","sql":"SELECT * FROM SALES WHERE SALES_DATE = ${vdate}","preStatements":[],"sqlType":"0","receivers":"[email protected]","datasource":2,"showType":"TABLE","localParams":[{"prop":"vdate","direct":"IN","type":"DATE","value":"$[yyyy-MM-dd]"}]} [INFO] 2019-12-11 16:15:31.282 - [taskAppId=TASK-9-59-75]:[109] - SqlParameters{type='DB2', datasource=2, sql='SELECT * FROM SALES WHERE SALES_DATE = ${vdate}', sqlType=0, udfs='', showType='TABLE', connParams='', title='111', receivers='[email protected]', receiversCc='', preStatements=[], postStatements=[]} [INFO] 2019-12-11 16:15:31.284 - [taskAppId=TASK-9-59-75]:[110] - sql type : DB2, datasource : 2, sql : SELECT * FROM SALES WHERE SALES_DATE = ${vdate} , localParams : [Property{prop='vdate', direct=IN, type=DATE, value='$[yyyy-MM-dd]'}],udfs : ,showType : TABLE,connParams : [INFO] 2019-12-11 16:15:31.286 - [taskAppId=TASK-9-59-75]:[127] - datasource name : db2数据源 , type : DB2 , desc : , user_id : 2 , parameter : {"address":"jdbc:db2://127.0.0.1:50000","database":"SAMPLE","jdbcUrl":"jdbc:db2://127.0.0.1:50000/SAMPLE","user":"db2inst1","password":"root"} [INFO] 2019-12-11 16:15:31.289 - [taskAppId=TASK-9-59-75]:[214] - SQL title : 111 [INFO] 2019-12-11 16:15:31.290 - [taskAppId=TASK-9-59-75]:[448] - after replace sql , preparing : SELECT * FROM SALES WHERE SALES_DATE = ? [INFO] 2019-12-11 16:15:31.290 - [taskAppId=TASK-9-59-75]:[453] - replaced sql , parameters:2019-12-11(DATE) [INFO] 2019-12-11 16:15:31.500 - [taskAppId=TASK-9-59-75]:[364] - prepare statement replace sql : com.ibm.db2.jcc.am.k4@7477b9cc [INFO] 2019-12-11 16:15:31.532 org.apache.dolphinscheduler.server.worker.runner.TaskScheduleThread:[156] - task instance id : 75,task final status : SUCCESS ``` MYSQL: ``` [INFO] 2019-12-11 16:16:56.824 - [taskAppId=TASK-6-61-77]:[109] - SqlParameters{type='MYSQL', datasource=5, sql='select * from t_escheduler_user where create_time = ${v_createdate};', sqlType=0, udfs='', showType='TABLE', connParams='', title='1', receivers='[email protected]', receiversCc='', preStatements=[], postStatements=[]} [INFO] 2019-12-11 16:16:56.825 - [taskAppId=TASK-6-61-77]:[110] - sql type : MYSQL, datasource : 5, sql : select * from t_escheduler_user where create_time = ${v_createdate}; , localParams : [Property{prop='v_createdate', direct=IN, type=DATE, value='$[yyyy-MM-dd]'}],udfs : ,showType : TABLE,connParams : [INFO] 2019-12-11 16:16:56.827 - [taskAppId=TASK-6-61-77]:[127] - datasource name : 10.37.129.2 , type : MYSQL , desc : , user_id : 2 , parameter : {"address":"jdbc:mysql://10.37.129.2:3306","database":"escheduler","jdbcUrl":"jdbc:mysql://10.37.129.2:3306/escheduler","user":"root","password":""} [INFO] 2019-12-11 16:16:56.827 - [taskAppId=TASK-6-61-77]:[214] - SQL title : 1 [INFO] 2019-12-11 16:16:56.828 - [taskAppId=TASK-6-61-77]:[448] - after replace sql , preparing : select * from t_escheduler_user where create_time = ?; [INFO] 2019-12-11 16:16:56.828 - [taskAppId=TASK-6-61-77]:[453] - replaced sql , parameters:2019-12-11(DATE) [INFO] 2019-12-11 16:16:56.832 - [taskAppId=TASK-6-61-77]:[364] - prepare statement replace sql : com.mysql.jdbc.JDBC4PreparedStatement@7340c332: select * from t_escheduler_user where create_time = '2019-12-11'; [INFO] 2019-12-11 16:16:56.833 org.apache.dolphinscheduler.server.worker.runner.TaskScheduleThread:[156] - task instance id : 77,task final status : SUCCESS ``` PSQL: ``` [INFO] 2019-12-11 16:18:07.252 - [taskAppId=TASK-6-63-79]:[109] - SqlParameters{type='POSTGRESQL', datasource=3, sql='select * from t_ds_user where create_time = ${v_createdate};', sqlType=0, udfs='', showType='TABLE', connParams='', title='1', receivers='[email protected]', receiversCc='', preStatements=[], postStatements=[]} [INFO] 2019-12-11 16:18:07.253 - [taskAppId=TASK-6-63-79]:[110] - sql type : POSTGRESQL, datasource : 3, sql : select * from t_ds_user where create_time = ${v_createdate}; , localParams : [Property{prop='v_createdate', direct=IN, type=DATE, value='$[yyyy-MM-dd]'}],udfs : ,showType : TABLE,connParams : [INFO] 2019-12-11 16:18:07.255 - [taskAppId=TASK-6-63-79]:[127] - datasource name : pg , type : POSTGRESQL , desc : , user_id : 2 , parameter : {"address":"jdbc:postgresql://localhost:5432","database":"dolphinscheduler","jdbcUrl":"jdbc:postgresql://localhost:5432/dolphinscheduler","user":"postgres","password":"postgres"} [INFO] 2019-12-11 16:18:07.255 - [taskAppId=TASK-6-63-79]:[214] - SQL title : 1 [INFO] 2019-12-11 16:18:07.255 - [taskAppId=TASK-6-63-79]:[448] - after replace sql , preparing : select * from t_ds_user where create_time = ?; [INFO] 2019-12-11 16:18:07.256 - [taskAppId=TASK-6-63-79]:[453] - replaced sql , parameters:2019-12-11(DATE) [INFO] 2019-12-11 16:18:07.260 - [taskAppId=TASK-6-63-79]:[364] - prepare statement replace sql : select * from t_ds_user where create_time = '2019-12-11 +08' [INFO] 2019-12-11 16:18:07.262 org.apache.dolphinscheduler.server.worker.runner.TaskScheduleThread:[156] - task instance id : 79,task final status : SUCCESS ``` Hive: ``` [INFO] 2019-12-11 16:30:51.121 - [taskAppId=TASK-8-72-88]:[94] - sql task params {"postStatements":[],"connParams":"","receiversCc":"","udfs":"","type":"HIVE","title":"33","sql":"SELECT * FROM testdb1 WHERE ctime = ${ctime}","preStatements":[],"sqlType":"0","receivers":"[email protected]","datasource":4,"showType":"TABLE","localParams":[{"prop":"ctime","direct":"IN","type":"DATE","value":"$[yyyy-MM-dd]"}]} [INFO] 2019-12-11 16:30:51.122 - [taskAppId=TASK-8-72-88]:[109] - SqlParameters{type='HIVE', datasource=4, sql='SELECT * FROM testdb1 WHERE ctime = ${ctime}', sqlType=0, udfs='', showType='TABLE', connParams='', title='33', receivers='[email protected]', receiversCc='', preStatements=[], postStatements=[]} [INFO] 2019-12-11 16:30:51.123 - [taskAppId=TASK-8-72-88]:[110] - sql type : HIVE, datasource : 4, sql : SELECT * FROM testdb1 WHERE ctime = ${ctime} , localParams : [Property{prop='ctime', direct=IN, type=DATE, value='$[yyyy-MM-dd]'}],udfs : ,showType : TABLE,connParams : [INFO] 2019-12-11 16:30:51.126 - [taskAppId=TASK-8-72-88]:[127] - datasource name : hive , type : HIVE , desc : , user_id : 2 , parameter : {"address":"jdbc:hive2://10.121.8.4:10000","database":"default","jdbcUrl":"jdbc:hive2://10.121.8.4:10000/default","user":"hive","password":""} [INFO] 2019-12-11 16:30:51.127 - [taskAppId=TASK-8-72-88]:[214] - SQL title : 33 [INFO] 2019-12-11 16:30:51.127 - [taskAppId=TASK-8-72-88]:[448] - after replace sql , preparing : SELECT * FROM testdb1 WHERE ctime = ? [INFO] 2019-12-11 16:30:51.127 - [taskAppId=TASK-8-72-88]:[453] - replaced sql , parameters:2019-12-11(DATE) ```
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services
