very good, ';' is mostly used on many occasions, but as shaoj and boyi said, I think we should care about the ";" not at the end of the sentence, how to modify the sql script for user? give user a solution will be better. what about double ";;" ? please leave message
By the way, don't use picture in mail list, it can't be shown on apache mailing list you can upload picture on github, then use pic url replace Best Regards --------------- DolphinScheduler(Incubator) PPMC Lidong Dai 代立冬 [email protected] --------------- zx liu <[email protected]> 于2020年9月24日周四 下午8:43写道： > First of all, yes, executeFuncAndSql is in a loop state, it will frequently > close and open connections, > > I found that sqltask connect always uses jdbc and DAO modules to have > integrated druid, so I think you > > can use druid to connect to the database so that you can use the connection > pool, it can solve the problem > > of frequent opening and closing of connections and improve the efficiency of > multiple query connections > > [image: image.png] > > [image: image.png] > > Secondly, for sending mail, it is processed in executeFuncAndSql. In fact, it > will be sent once when > executed once. There is no case that only support mulitiple non-query > statement and support only > one select statement。 > > [image: image.png] > > > Finally, regarding the';' segmentation, this is based on the habit of > executing sqlscript in common > databases. If the comment exists';', the error reporting mechanism will be > executed and the user will > be prompted to modify sqlscript > > 首先，是的，executeFuncAndSql处于循环状态，它将频繁关闭和打开连接，我发现sqltask > connect始终使用jdbc和DAO模块来集成druid， > 因此我认为您可以使用druid连接到数据库，因此可以使用连接池，而不是在executeFuncAndSql中执行sql语句前建立数据库连接，同时本次 > 提交的sql语句只连接同一个数据库。可以在执行前就完成数据库的连接。这样就可以解决频繁打开和关闭连接的问题，并提高多个查询连接的效率， > 其次，对于发送邮件，它在executeFuncAndSql中进行处理。实际上一次执行将发送一次。每一个sql都能发送邮件没有只支持多种非查询语句 > 而仅支持一个select语句的情况。 > > 最后，关于'；'分割的情况，这是基于常见数据库的执行sqlscript的习惯来决定的。假如注释存在';',执行报错机制，提示使用者修改sqlscript. > 我认为这样并没有太大的问题。 > > > wu shaoj <[email protected]> 于2020年9月23日周三 下午6:00写道： > >> Yes, don't split with semicolon [";"], >> So PLEASE DO NOT SUPPORT MULTI SQLS IN SQL TASK !!! >> We can add a new task, which type is SQL-SCRIPT, to suport the multil dml >> SQLs, and not support multi query sqls is better >> >> >> On 2020/9/23, 15:30, "boyi" <[email protected]> wrote: >> >> hi: >> >> >> >> >> Don't split with semicolon [";"], it's a problem >> >> >> For example, SQL contains comments with semicolons [";"] >> >> >> In this way, there will be problems when the program is executed >> -------------------------------------- >> BoYi ZhangE-mail : [email protected] >> On 09/23/2020 15:06，[email protected]<[email protected]> >> wrote： >> Hi,If execute executeFuncAndSql in a loop,it will close and open >> connection frequently. >> You can try use the `addBatch` and `executeBatch` method of >> PrepareStatement, >> But it seems hive jdbc didn't support it. >> >> BTW,Beacause the sql task can send the result by the email now,So If >> support multiple queries,How to send the result. >> Whether we only support mulitiple non-query statement and support >> only one select statement? >> >> Welcome discuss!Thanks! >> >> >> >> >> Thanks, >> Gang Li >> >> DolphinScheduler(Incubator) PPMC >> Gang Li 李岗 >> >> [email protected] >> >> From: zx liu >> Date: 2020-09-21 17:09 >> To: dev >> Subject: [DISCUSS] support multi-queries in single sql transformation >> task #3710 >> hi all, >> >> The current sql task cannot support multiple queries, but my project >> is >> very emergency about this feature. I chang this feature in my >> local.but >> I'm not sure if my change is reasonable, so i need some help or >> suggestions。 >> >> hive and sql use the same code to execute sql， >> >> executeFuncAndSql(mainSqlBinds, preStatementSqlBinds, >> postStatementSqlBinds, createFuncs); >> >> But the problem here is that hive JDBC does not support multiple >> queries, >> mysql mssql supports multiple queries. If both mysql mssql and hive >> supports multiple queries , so I consider the compatibility of the two >> here, and modify it to split the sql statement according to the >> conventional ";", but execute them one by one. >> >> // support multiQueries 2020/9/16 >> String[] sqlsplit = mainSqlBinds.getSql().split(";"); >> if (sqlsplit.length == 0) { >> logger.error("please check sqlscript can splited by ';'"); >> return; >> } >> Map<Integer, Property> sqlParamsMap = mainSqlBinds.getParamsMap(); >> for (String sqlscript : sqlsplit) { >> executeFuncAndSql(new SqlBinds(sqlscript, sqlParamsMap), >> preStatementSqlBinds, postStatementSqlBinds, createFuncs); >> } >> >> I don’t know if this change is reasonable, I hope you can give some >> reasonable suggestions >> >> https://github.com/apache/incubator-dolphinscheduler/issues/3710 >> >> thk! >> >> 当前的sql组件支持正常的多条语句同时执行，但是我们项目对这个功能比较着急，我在本地做了修改，但是我不确定我改的是否合理。 >> >> 希望得到大家的建议或者帮助。 >> >> 跟踪代码发现，hive 与mysql 使用了同样的执行语句的入口： >> >> executeFuncAndSql(mainSqlBinds, preStatementSqlBinds, >> postStatementSqlBinds, createFuncs); >> >> >> 有些数据源可能直接在jdbc 的连接参数加上allowmultiqueries=true就可以,但是hive jdbc是不支持 >> 批量sql支持的这个功能， >> >> 我的考虑是在不能确定其它的数据源是否也支持多sql查询的功能，所以使用";"分割sql语句依然保持一条一条的语句执行，这样能 >> >> 保证这两种情况兼容，同样能够实现基本的功能，代码的改动量很少。修改成这样： >> >> // support multiQueries 2020/9/16 >> String[] sqlsplit = mainSqlBinds.getSql().split(";"); >> if (sqlsplit.length == 0) { >> logger.error("please check sqlscript can splited by ';'"); >> return; >> } >> Map<Integer, Property> sqlParamsMap = mainSqlBinds.getParamsMap(); >> for (String sqlscript : sqlsplit) { >> executeFuncAndSql(new SqlBinds(sqlscript, sqlParamsMap), >> preStatementSqlBinds, postStatementSqlBinds, createFuncs); >> } >> >> 这样修改是否合理，希望能够给予帮助或者建议。 >> >> 谢谢！ https://github.com/apache/incubator-dolphinscheduler/issues/3710 >> >
