I found this in v1.6.1, but the code looks like it didn't change in 1.6.2. The code in SQLExec.runStatements(Reader reader, PrintStream out) that parses the sql input into individual statements fails if the input contains a string with "--" inside of it. So, for example, if the input looks like INSERT INTO some_table VALUES (1, "Blah blah blah--blah blah blah"); INSERT INTO some_table VALUES (2, "Blah blah blah blah blah blah"); it never finds the first delimiter character, so you get a SQL syntax exception: $ ant blah Buildfile: build.xml blah: [sql] Executing commands [sql] Failed to execute: INSERT INTO some_table VALUES (1, 'Blah blah blah--blah blah blah'); [sql] INSERT INTO some_table VALUES (2, "Blah blah blah blah blah blah") BUILD FAILED C:\ici\r180\build\build.xml:814: java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO some_table VALUES (2, "Blah blah blah blah blah " Total time: 2 seconds The same sql seems to work from the "mysql" command line and other jdbc-based tools. Here's the problem: // SQL defines "--" as a comment to EOL // and in Oracle it may contain a hint // so we cannot just remove it, instead we must end it if (!keepformat) { if (line.indexOf("--") >= 0) { sql.append("\n"); } } if ((delimiterType.equals(DelimiterType.NORMAL) && sql.toString().endsWith(delimiter)) || (delimiterType.equals(DelimiterType.ROW) && line.equals(delimiter))) { execSQL(sql.substring(0, sql.length() - delimiter.length()), out); sql.replace(0, sql.length(), ""); } The code that looks for the delimiter character checks only that the sql buffer endsWith ";", but the previous section appends a " " or "\n" character to the sql buffer, so we never find it.
Oh, here's the build.xml snippet: <target name="blah" <sql driver="${mysql.driver}" url="jdbc:mysql://${db.host.mysql}/${db.dbname.mysql}" userid="${db.user.mysql}" password="${db.password.mysql}" classpathref="compile.path" autocommit="false"> DROP TABLE IF EXISTS some_table; CREATE TABLE some_table ( id tinyint(4) NOT NULL, text varchar(20) NOT NULL default '', PRIMARY KEY (id) ) TYPE=InnoDB; INSERT INTO some_table VALUES (1, 'Blah blah blah--blah blah blah'); INSERT INTO some_table VALUES (2, "Blah blah blah blah blah blah"); </sql> </target>
A work-around is to use the "keepformat" attribute. This is by default false, set it to true and your example should work. -- is perfectly value in a string value, but keepformat does not know that it in a string and removes it and the characters after is.
Same problems with : insert into dm_prod_macro_family (id, descr) values ('***********', '---'); Proposed correction : Remove all strings values before making the test of comment. Something like this : In src/main/org/apache/tools/ant/taskdefs/SQLExec.java, replace // SQL defines "--" as a comment to EOL // and in Oracle it may contain a hint // so we cannot just remove it, instead we must end it if (!keepformat) { if (line.indexOf("--") >= 0) { sql.append("\n"); } } by // SQL defines "--" as a comment to EOL // and in Oracle it may contain a hint // so we cannot just remove it, instead we must end it if (!keepformat) { if (line.replaceAll("'.*?'", "''").indexOf("--") >= 0) { sql.append("\n"); } } Regards,
*** This bug has been marked as a duplicate of bug 27739 ***