DO NOT REPLY [Bug 19921] New: - Optional replace of properties in SQL task

[bu...@apache.org]

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

http://nagoya.apache.org/bugzilla/show_bug.cgi?id=19921

Optional replace of properties in SQL task

           Summary: Optional replace of properties in SQL task
           Product: Ant
           Version: 1.5
          Platform: All
        OS/Version: All
            Status: NEW
          Severity: Normal
          Priority: Other
         Component: Core tasks
        AssignedTo: dev@ant.apache.org
        ReportedBy: christer.holmer@corustechnologies.com


The SQL task will replace all ANT properties, e.g. ${basedir}, from the SQL 
string before executing it. This is probably desirable in many cases, but in 
some cases it's destructive. We want to store the string without property 
replacement.

Consider this:
CREATE TABLE TEST (TEST_COL VARCHAR2(100));
INSERT INTO TEST (TEST_COL) VALUES ('${basedir}');
SELECT TEST_COL FROM TEST;

Resulting in:
TEST_COL
-----------------------------------------
C:\temp

What I want is:
TEST_COL
-----------------------------------------
${basedir}

My proposal is to add an attribute 'replaceProperties' to the SQL task, which 
could be used to turn of the replacing. The default value would be 'true' so 
that existing scripts wont break.

This problem is especially serious when using the task on Oracle Wrapped files 
(.plb), which won't work at all if the properties have been replaced.

I have tried this on my own by adding the following code in SQLExec.java:

Added instance attribute:

>      * <p>
>      * Replace ANT properties in the SQL.
>      * </p>
>      */
>     private boolean replaceProperties = true;

Added instance method:

>     /**
>      * Set the replace properties flag. Defaults to true.
>      */
>     public void setReplaceProperties(boolean replace) {
>         replaceProperties = replace;
>     }

Modified runStatements()

>            if (replaceProperties) {
>                line = project.replaceProperties(line);
>            }

The SQL task would then look like this:

       <sql driver="oracle.jdbc.driver.OracleDriver" 
            url="jdbc:oracle:thin:@localhost:1521:SID" 
            userid="USER" 
            password="PWD" 
            autocommit="true" 
            replaceProperties="true"> 
          <![CDATA[
DROP TABLE TEST;
CREATE TABLE TEST (TEST_COL VARCHAR2(100));
INSERT INTO TEST (TEST_COL) VALUES ('${XYZ}');
INSERT INTO TEST (TEST_COL) VALUES ('${basedir}');
SELECT TEST_COL FROM TEST;
          ]]>
          <classpath>
            <pathelement location="lib/classes12.zip"/>
          </classpath>          
       </sql>