Hi, In the SQL task, using the option "keepformat" will prevent SQL statements lines from having their spaces trimed out. This can prevent ANT from seing a ROW delimiter, which is being found only if an entire line equals the delimiter. In example, if I write the following with the options 'delimiter' set to 'GO', 'delimitertype' set to 'row', and 'keepformat' set to 'true': SELECT * ... FROM ... GO And I put one or more spaces after the 'GO' statement, then it will not trigger sending of the 'SELECT' statement to the database. A simple solution would be changing source code of SQLExec class, and replace lines from method 'runStatements': (delimiterType.equals(DelimiterType.ROW) && line.equals(delimiter))) by (delimiterType.equals(DelimiterType.ROW) && line.trim().equals(delimiter))) Regards, Matthieu Recouly
Additionaly, changing the following line: execSQL(sql.substring(0, sql.length() - delimiter.length()), to: execSQL(sql.substring(0, sql.length() - (delimiterType.equals(DelimiterType.NORMAL)?delimiter.length():line.length())), is required to definitely strip the ROW delimiter from SQL string we send to the database
Also you would prefer using: line.trim().equalsIgnoreCase(delimiter) rather than: line.trim().equals(delimiter)
The whole idea of the keepformat option was to allow users to load packages, triggers, types, and functions without losing the format of their code. For select statements, there is no need to use the keepformat option.
You may be interested to read the discussion that lead to the keepformat option. http://nagoya.apache.org/bugzilla/show_bug.cgi?id=10719 Essentially, the database needs to recognize the delimiter. I will leave it to the committers to comment on whether or not this will get fixed, but personally I think it works as expected.
Hi Robert, The above example is to illustrate description of the problem, in fact I encountered three problems when I tried to load several SQL scripts against a MS-SQL server database: 1/ In those scripts were written comments like the following: /* Section title -----------------*/ Some statements GO Without the 'keepformat' option set to 'true', the second line (several dashes + '*/') was ignored since ANT SQL task considers a line starting with '--' is a comment, and must be dropped. This made the first line (where comment starts) to be sent, and an error to occur since end of comment was not received by the server. That's why I was forced to set the 'keepformat' to true. 2/ Let's assume we set our 'delimiter' option the value 'GO', and we have the following SQL code in a script PRINT 'YOU SHOULD GO THERE' This runs correctly with query analyzers coming with MS-SQL server, but without setting the 'delimiterType' option to 'row' in ANT SQL task, this will cause the text previous to "GO" to be sent as a query. That's why setting 'delimiterType' to 'row' is needed. 3/ In some of the scripts I run, I found the delimiter written using both upper and lower case ('GO'/'go'), or with one or more spaces behind, which is the problem I described above... Currently, I am working in a large company, where people from database department are used to MS tools which allow these practices. I wish they were able to use ANT instead of MS based tools to run their scripts against a DB, that's why I am suggesting those changes, which look useful to me. Note that I AM NOT asking that ANT SQL task become more like MS tools, I am just querying for more compatibility on precise points... If this ever comes to break the original mind of the code ANT developers write, I will use the ANT way of handling such case and continue using the customized ANT task I did write for that. Cheers ! Matthieu Recouly
there's a new strictDelimiterMatching attribute now. svn rev 675949 and 675954
*** Bug 44159 has been marked as a duplicate of this bug. ***