I failed to execute the sql sample. How do I add delimiters for non-query sql

[liusy-bd]

https://github.com/apache/dolphinscheduler-sdk-python/blob/main/examples/yaml_define/Sql.yaml examples/yaml_define/example_sql.sql

I failed to execute the sql sample. How do I add delimiters for non-query sql； It looks like example sql.sql There are multiple SQLS in the sql file；

kan read the document said that no specified will automatically parse, in fact, in the test did not specify the separator will report an error；

Looking forward to your reply！！！

[liusy-bd]

This is the relevant error log, manually adding the separator on the page will succeed

---

[INFO] 2023-09-06 18:47:30.474 +0800 - Success set taskVarPool: [{"prop":"end_date","direct":"IN","type":"VARCHAR","value":"2023-09-06"},{"prop":"start_year_week","direct":"IN","type":"VARCHAR","value":"202336"},{"prop":"end_year_week","direct":"IN","type":"VARCHAR","value":"202336}"},{"prop":"start_date","direct":"IN","type":"VARCHAR","value":"2023-09-05"}] [INFO] 2023-09-06 18:47:30.474 +0800 - Full sql parameters: SqlParameters{type='CLICKHOUSE', datasource=2, sql='truncate table base_product; alter table dwd_store_sku_sales_order delete where sales_date >= '${start_date}' and sales_date <= '${end_date}' ; ', sqlType=1, sendEmail=null, displayRows=10, limit=0, segmentSeparator=null, udfs='null', showType='null', connParams='null', groupId='0', title='null', preStatements=[], postStatements=[]} [INFO] 2023-09-06 18:47:30.474 +0800 - sql type : CLICKHOUSE, datasource : 2, sql : truncate table base_product; alter table dwd_store_sku_sales_order delete where sales_date >= '${start_date}' and sales_date <= '${end_date}' ; , localParams : [],udfs : null,showType : null,connParams : null,varPool : [Property{prop='end_date', direct=IN, type=VARCHAR, value='2023-09-06'}, Property{prop='start_year_week', direct=IN, type=VARCHAR, value='202336'}, Property{prop='end_year_week', direct=IN, type=VARCHAR, value='202336}'}, Property{prop='start_date', direct=IN, type=VARCHAR, value='2023-09-05'}] ,query max result limit 0 [INFO] 2023-09-06 18:47:30.475 +0800 - setSqlParamsMap: Property with paramName: start_date put in sqlParamsMap of content truncate table base_product; alter table dwd_store_sku_sales_order delete where sales_date >= '${start_date}' and sales_date <= '${end_date}' ; successfully. [INFO] 2023-09-06 18:47:30.475 +0800 - setSqlParamsMap: Property with paramName: end_date put in sqlParamsMap of content truncate table base_product; alter table dwd_store_sku_sales_order delete where sales_date >= '${start_date}' and sales_date <= '${end_date}' ; successfully. [INFO] 2023-09-06 18:47:30.475 +0800 - after replace sql , preparing : truncate table base_product; alter table dwd_store_sku_sales_order delete where sales_date >= ? and sales_date <= ? ;

[INFO] 2023-09-06 18:47:30.475 +0800 - Sql Params are replaced sql , parameters:2023-09-05(VARCHAR)2023-09-06(VARCHAR) [INFO] 2023-09-06 18:47:30.475 +0800 - can't find udf function resource [ERROR] 2023-09-06 18:47:30.485 +0800 - execute sql error: SQL task prepareStatementAndBind error [ERROR] 2023-09-06 18:47:30.486 +0800 - sql task error org.apache.dolphinscheduler.plugin.task.api.TaskException: SQL task prepareStatementAndBind error at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.prepareStatementAndBind(SqlTask.java:377) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeUpdate(SqlTask.java:311) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeFuncAndSql(SqlTask.java:210) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.handle(SqlTask.java:161) at org.apache.dolphinscheduler.server.worker.runner.DefaultWorkerDelayTaskExecuteRunnable.executeTask(DefaultWorkerDelayTaskExecuteRunnable.java:49) at org.apache.dolphinscheduler.server.worker.runner.WorkerTaskExecuteRunnable.run(WorkerTaskExecuteRunnable.java:174) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:131) at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:74) at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:82) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: java.sql.SQLException: Prepared statement only supports one query but we got: 2 at com.clickhouse.jdbc.SqlExceptionUtils.clientError(SqlExceptionUtils.java:73) at com.clickhouse.jdbc.internal.ClickHouseConnectionImpl.prepareStatement(ClickHouseConnectionImpl.java:685) at com.clickhouse.jdbc.ClickHouseConnection.prepareStatement(ClickHouseConnection.java:105) at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337) at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.prepareStatementAndBind(SqlTask.java:363) ... 12 common frames omitted [ERROR] 2023-09-06 18:47:30.486 +0800 - Task execute failed, due to meet an exception

[liusy-bd]

This is the successful image after adding the separator！！！！！！！！！！！

[zhongjiajie]

Hi @liusy-bd seem we do not have separate option in pydolphinscheduler, maybe we can add it to the next release,

and BTW, in 3.2.0 we remove the seperate option from UI and use default one , see more detail in https://github.com/apache/dolphinscheduler/pull/10869

[liusy-bd]

I see from the latest documentation that the default separator is; \n Via pydolphinscheduler yaml -f primary_data_flow.yml does not specify that the default separator should be used, but this is not the case

[zhongjiajie]

fix in https://github.com/apache/dolphinscheduler-sdk-python/pull/115 and will release in version 4.0.4