ant sql 插入语句在“--"字符串上失败.解决方法?
背景
我们正在更改我们的安装脚本，以使用 ant 的sql"任务和 jdbc，而不是专有的 sql 客户端 sqlplus (oracle) 和 osql (msft).
We're changing our install scripts to use ant's "sql" task and jdbc rather than proprietary sql clients sqlplus (oracle) and osql (msft).
更新:添加了更多上下文.我们的基础数据"(种子数据)由包含供应商中立"(即在 oracle 和 mssql 中均可使用)sql 语句的 .sql 文件集合组成.
Updated: added more context. Our "base data" (seed data) consists of a collection of .sql files containing "vendor-neutral"(i.e. works both in oracle and mssql) sql statements.
问题
脚本运行良好，只有一个例外:
The scripts run fine, with one exception:
此 sql 在 Oracle 中失败.具体来说，某些东西(ant 或 jdbc 驱动程序)将破折号/连字符视为注释的开头"——即使它们嵌入在字符串中.请注意，同样的 sql 也适用于 ant/sql 和微软的 jdbc 驱动程序.
This sql fails in Oracle. Specifically, something (ant or jdbc driver) treats the dashes/hyphens as "beginning of a comment"--even though they are embedded in a string. Note that the same sql works fine with ant/sql and microsoft's jdbc driver.
INSERT INTO email_client (email_client_id,generated_reply_text) VALUES(100002,'----- Original Message -----');
相关错误
这个ant bug 似乎可以识别问题.由于它仍然开放(8 年后)，我不希望很快得到修复.但是，因为问题只出现在oracle中，所以可能出在驱动上.
This ant bug appears to identify the problem. As it's still open (after 8 years), I'm not hoping for a fix soon. However, because the problem appears only in oracle, it may lie with the driver.
oracle 驱动:jdbc 瘦驱动，版本 10.2.0.1.0
The oracle driver: jdbc thin driver, version 10.2.0.1.0
问题
有没有人有在 mssql 和 oracle 中都有效的解决方法?(例如，更改违规行以定义转义字符?我在插入"sql92 语法中没有看到转义")
Does anyone have a workaround which works in both mssql and oracle? (e.g. changing the offending lines to define an escape character? I don't see an 'escape' on the 'insert' sql92 syntax)
谢谢
推荐答案
在查看SQLExec"源并打开详细日志记录后，我找到了一个解决方法:
After viewing the 'SQLExec' source and turning on verbose logging, I found a workaround:
解决方法
如果 sql 语句包含包含--"的字符串，则将分隔符(分号)放在下一行.
这失败了
INSERT INTO email_client (email_client_id,generated_reply_text) VALUES(100002,'----- Original Message -----');
成功
注意分号在单独的一行
INSERT INTO email_client (email_client_id,generated_reply_text) VALUES(100002,'----- Original Message -----')
;
详情
打开verbose logging，我看到Ant遇到违规的sql语句时，居然一次把三个sql语句传入jdbc驱动.有问题的语句、下一个语句(还包括嵌入的--")和后续语句(不包括嵌入的--").
Turning on verbose logging, I saw that when Ant came across the offending sql statement, it actually passed three sql statements in at once to the jdbc driver. The offending statement, the next statement (which also included an embedded '--'), and the subsequent statement (which did not include an embedded '--').
我快速浏览了 Ant 代码，没有发现任何明显错误.由于我不打算修补 Ant，所以我寻找了一种解决方法.
I gave the Ant code a quick glance and didn't see any obvious errors. Since I wasn't planning to patch Ant, I looked for a workaround.
调整后我发现如果我只是将分隔符(分号)移动到带有嵌入--"的语句的下一行，脚本会成功执行.
Tweaking with it I found that if I simply moved the delimiter (semicolon) to the next line for the statements with embedded '--', the scripts executed successfully.
感谢大家的参与
相关文章