Ant The sql insert statement does not work on '-' strings. workaround?

Question

Context

We change our installation scripts to use the ant "sql" task and jdbc, and not to our own SQL clients sqlplus (oracle) and osql (msft).

Updated: added more context. Our "base data" (source data) consists of a set of .sql files containing "vendor-neutral" (i.e., it works in both oracle and mssql) SQL statements.

Problem

Scripts run fine, with one exception:

This sql does not work in Oracle. In particular, something (ant or jdbc driver) treats dashes / hyphens as "the beginning of a comment" - even if they are embedded in a line. Note that the same sql works fine with ant / sql and the microsoft jdbc driver.

INSERT INTO email_client (email_client_id,generated_reply_text) VALUES(100002,'----- Original Message -----'); 

Error related

This ant problem indicates a problem. Since it is still open (after 8 years), I do not hope to fix it soon. However, since the problem only occurs in the oracle, this may be due to the driver.

Oracle driver: jdbc thin driver, version 10.2.0.1.0

Question

Does anyone have a workaround that works in both mssql and oracle? (for example, changing violation lines to define an escape character? I do not see "escape" in sql92 "insert" syntax)

thanks

Answer 1

After looking at the SQLExec source and enabling verbose logging, I found a workaround:

Bypass

if the sql statement contains a string containing '-', put a separator (with a comma) on the next line.

This does not work

 INSERT INTO email_client (email_client_id,generated_reply_text) VALUES(100002,'----- Original Message -----'); 

It succeeds

Note that the semicolon is on a separate line

 INSERT INTO email_client (email_client_id,generated_reply_text) VALUES(100002,'----- Original Message -----') ; 

More details

Enabling verbose logging, I saw that when Ant came across an offensive sql expression, it actually passed three sql statements to the jdbc driver at once. An abusive statement, the next statement (which also includes the built-in "-") and the subsequent statement (which did not include the "-").

I quickly figured out Ant code and did not see any obvious errors. Since I did not plan for the Ant patch, I was looking for a workaround.

Fine tuning with it, I found that if I just moved the separator (semicolon) to the next line for statements with built-in "-", the scripts were executed successfully.

thank you all for weighing

Answer 2

You can try the following:

 INSERT INTO email_client (email_client_id,generated_reply_text) VALUES(100002,LPAD('-',5,'-') || ' Original Message ' || LPAD('-',5,'-'));