DO NOT REPLY [Bug 30760] New: - sql task handle pl/sql with ';' in pl/sql code. (patch)

[bu...@apache.org]

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG 
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://issues.apache.org/bugzilla/show_bug.cgi?id=30760>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND 
INSERTED IN THE BUG DATABASE.

http://issues.apache.org/bugzilla/show_bug.cgi?id=30760

sql task handle pl/sql with ';' in pl/sql code. (patch)

           Summary: sql task handle pl/sql with ';' in pl/sql code. (patch)
           Product: Ant
           Version: 1.6.2
          Platform: PC
        OS/Version: Linux
            Status: NEW
          Severity: Normal
          Priority: Other
         Component: Core tasks
        AssignedTo: dev@ant.apache.org
        ReportedBy: ervandew@yahoo.com


ant: 1.6.2
os: linux 2.4.26
java: 1.4.2_05
db: postgresql 7.4.3

- Current <sql> task behavior:

When supplying a sql file to <sql> task any line ending in a ';'
results in the end of the statement, and that statement is sent to the
rdbms.  Problems exists when a create function call contains pl/sql code
that has line ending ';' denoting statements within the procedure.
When <sql> task encounters the ';' in the procedure only the portion of
the procedure up to the first line ending ';' is sent to the rdbms.

Ex. Procedure statement:

CREATE OR REPLACE FUNCTION test () RETURNS TRIGGER AS '
  DECLARE
    theTime timestamp;
  BEGIN
    theTime := ''now'';
    UPDATE TheTable
      SET theTime = theTime
      WHERE tableId = NEW.tableId;
    RETURN NEW;
  END;
' LANGUAGE 'plpgsql';

Ex. Fragment sent to rdbms:

CREATE OR REPLACE FUNCTION test () RETURNS TRIGGER AS '
  DECLARE
    theTime timestamp

Although there are workarounds for this, (delimetertype="row", etc),
they all result in reformatting the sql script.  This is very
cumbersome if the script is auto generated from any sort of relational tool.


- Patch:
  i've attached a patch that fixes this issue by checking if line ending
  ';' chars occur with a quoted expression or not.

  The patch works fine on postgresql, but i have yet to test it on
  oracle, sybase, sqlserver, etc.

I'm hoping people can check this patch for issues, bugs, etc and
hopefully this functionality can make it into future release of ant.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@ant.apache.org
For additional commands, e-mail: dev-help@ant.apache.org