問題描述
背景
我們正在更改我們的安裝腳本，以使用 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.
感謝大家的參與
這篇關于ant sql 插入語句在“--"字符串上失敗.解決方法?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！