Bug 27739 - sql insert statement fails on '--' strings
Summary: sql insert statement fails on '--' strings
Status: ASSIGNED
Alias: None
Product: Ant
Classification: Unclassified
Component: Core tasks (show other bugs)
Version: 1.6.1
Hardware: Other All
: P3 normal (vote)
Target Milestone: ---
Assignee: Ant Notifications List
URL:
Keywords:
: 28745 30502 (view as bug list)
Depends on:
Blocks:
 
Reported: 2004-03-17 13:05 UTC by Elmar Keck
Modified: 2008-07-10 08:21 UTC (History)
3 users (show)



Attachments
Should fix the Bug. It trims the line before looking for delimitter. (468 bytes, patch)
2004-08-02 12:53 UTC, Lars Gehrken
Details | Diff
Junit test (4.51 KB, text/plain)
2004-11-19 07:46 UTC, Kev Jackson
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Elmar Keck 2004-03-17 13:05:07 UTC
executing the following statement in an external sql file fails

INSERT INTO mandator (mandator_id, mandator_name, description) VALUES (0,'--','');

whereas 

INSERT INTO mandator (mandator_id, mandator_name, description) VALUES (0,'test','');

or 

INSERT INTO mandator (mandator_id, mandator_name, description) VALUES
(0,'-'||'-','');

works well! 

[sql] Failed to execute:   INSERT INTO mandator (mandator_id, mandator_name,
description) VALUES (0,'--','');
[sql] java.sql.SQLException: ORA-00911: invalid character

tested with Oracle9i 8.1.7.1  and 9.2.0.3 JDBC Drivers

ant section:

<target name="create_db" >
  <sql
    driver="oracle.jdbc.driver.OracleDriver"
    url="jdbc:oracle:thin:@london:1521:oracle"
    userid="test"
    password="test"
    onerror="continue"
    src="${app.home}/database/create_db_user/test.sql">
    <classpath>
	  <pathelement location="${app.home}/lib/classes12.zip"/>
    </classpath>
  </sql>
Comment 1 Steve Loughran 2004-03-17 15:28:47 UTC
A lot of SQL systems take -- as the beginning of a comment. Some (eg MySQL want
"-- ", but it looks like here oracle wants -- on its own. 

I am therefore going to mark this as invalid, unles there is some kind of
escaping trick you want to do that the SQL command is preventing. 
Comment 2 Elmar Keck 2004-03-18 09:27:46 UTC
I do not think that inserting strings like '--', or '----' is illegal.

Performing this statement via the Java tool DBVisiualizer or Oracle SQL Plus, ...
works well.

Why is the ant parser not ignoring any comment tokens inside an sql argument?    
 
Comment 3 Elmar Keck 2004-03-18 09:31:06 UTC
ref. Comment
Comment 4 Steve Loughran 2004-03-18 09:52:31 UTC
heh, you look to be right; there is some 'intelligence' in the source that is
over-eager. Set keepformat=true to turn that off
Comment 5 Jose Alberto Fernandez 2004-03-19 15:40:27 UTC
ANT does not really parse the SQL, so it does not know whether a -- ia inside
a string value or in a comment position.

The reason comments are stripped in the first place was that in certain JDBC 
drivers (MSSQLServer comes to mind) you are not allowed to have comments not 
newlines nor anything. ANT is trying very hard to make it work there.
Comment 6 Lars Gehrken 2004-08-02 12:49:23 UTC
*** Bug 28745 has been marked as a duplicate of this bug. ***
Comment 7 Lars Gehrken 2004-08-02 12:53:47 UTC
Created attachment 12300 [details]
Should fix the Bug. It trims the line before looking for delimitter.
Comment 8 Dan Thompson 2004-11-18 19:05:09 UTC
The -- string is considered an SQL comment to end of line.  However, if the --
occurs within a delimited string, the SQL task still treats it as a comment to
end of line, as this simple task doesn't attempt to parse for string fields in
the sql statement.

A workaround is to use the keepformat="true" flag, but this turns comment
processing off entirely.  It also has a nasty habit of disliking trailing spaces
after the semicolon.  A workaround for this, is to remove all trailing spaces in
any SQL files you want to process using this comment, as well as removing all
comments from the sql file.
Comment 9 Kev Jackson 2004-11-19 07:44:50 UTC
I've just spent an unproductive 30 mins trying to duplicate this bug.

I've only looked at the runStatements method as this seems to be the only one
that actively looks for comments "--, // or REM" and tries to deal with them.

My test SQL was a simple SELECT '--', '--' FROM USERS; --comment

The method deals with these -- as it should do.

I'm attaching a Junit test of the behaviour (excluding DB access).  At first I
was going to have two stubbed methods and then leave one whilst working on the
other, but as the first exhibits the correct behaviour as far as I know, there
didn't seem to be much point rewriting it.

Notes:
[1]I know that the code doesn't execute the SQL - my reasoning is that if the
method doesn't break the SQL at the first --, then it's going to be fine when it
passes it along to the DB - I'd process the statement if I was Oracle!

[2]I know that it won't prove the absence of a bug, but I think it may point to
a problem elsewhere, configuration, JDBC task etc?
Comment 10 Kev Jackson 2004-11-19 07:46:31 UTC
Created attachment 13499 [details]
Junit test

Shows correct behaviour for runStatements method prior to passing statement
onto DB
Comment 11 Stefan Bodewig 2008-07-10 08:21:19 UTC
*** Bug 30502 has been marked as a duplicate of this bug. ***