Sql

Description

Executes a series of SQL statements via JDBC to a database. Statements can either be read in from a text file using the src attribute or from between the enclosing SQL tags.

Multiple statements can be provided, separated by semicolons (or the defined delimiter). Individual lines within the statements can be commented using either --, // or REM at the start of the line.

The autocommit attribute specifies whether auto-commit should be turned on or off whilst executing the statements. If auto-commit is turned on each statement will be executed and committed. If it is turned off the statements will all be executed as one transaction.

The onerror attribute specifies how to proceed when an error occurs during the execution of one of the statements. The possible values are: continue execution, only show the error; stop execution, log the error but don't fail the task and abort execution and transaction and fail task.

Proxies. Some JDBC drivers (including the Oracle thin driver), use the JVM's proxy settings to route their JDBC operations to the database. Since Apache Ant 1.7, Ant running on Java 5 or later defaults to using the proxy settings of the operating system. Accordingly, the OS proxy settings need to be valid, or Ant's proxy support disabled with -noproxy option.

Parameters

Parameters specified as nested elements

transaction

Use nested <transaction> elements to specify multiple blocks of commands to the executed executed in the same connection but different transactions. This is particularly useful when there are multiple files to execute on the same schema.

The <transaction> element supports any resource or single element resource collection as nested element to specify the resource containing the SQL statements.

any resource or resource collection

You can specify multiple sources via nested resource collection elements. Each resource of the collection will be run in a transaction of its own. Prior to Ant 1.7 only filesets were supported. Use a sort resource collection to get a predictable order of transactions.

classpath

Sql's classpath attribute is a path-like structure and can also be set via a nested classpath element. It is used to load the JDBC classes.

connectionProperty

Since Ant 1.8.0

Use nested <connectionProperty> elements to specify additional JDBC properties that need to be set when connecting to the database.

Examples

Connect to the database given in url as the sa user using the org.database.jdbcDriver and execute the SQL statements contained within the file data.sql

<sql driver="org.database.jdbcDriver"
     url="jdbc:database-url"
     userid="sa"
     password="pass"
     src="data.sql"/>

Connect to the database given in url as the sa user using the org.database.jdbcDriver and execute the SQL statements contained within the file data.sql. Also set the property internal_logon to the value SYSDBA.

<sql driver="org.database.jdbcDriver"
     url="jdbc:database-url"
     userid="sa"
     password="pass"
     src="data.sql">
  <connectionProperty name="internal_logon" value="SYSDBA">
</sql>

Connect to the database given in url as the sa user using the org.database.jdbcDriver and execute the two SQL statements inserting data into some_table and truncating some_other_table. Ant properties in the nested text will not be expanded.

<sql driver="org.database.jdbcDriver"
     url="jdbc:database-url"
     userid="sa"
     password="pass">
insert
into table some_table
values(1,2,3,4);

truncate table some_other_table;
</sql>

Note that you may want to enclose your statements in <![CDATA[ ... ]]> sections so you don't need to escape <, >, & or other special characters. For example:

<sql driver="org.database.jdbcDriver"
     url="jdbc:database-url"
     userid="sa"
     password="pass"><![CDATA[

update some_table set column1 = column1 + 1 where column2 < 42;

]]></sql>

The following command turns property expansion in nested text on (it is off purely for backwards compatibility), then creates a new user in the HSQLDB database using Ant properties.

<sql driver="org.hsqldb.jdbcDriver";
     url="jdbc:hsqldb:file:${database.dir}"
     userid="sa"
     password=""
     expandProperties="true">
  <transaction>
    CREATE USER ${newuser} PASSWORD ${newpassword}
  </transaction>
</sql>

The following connects to the database given in url as the sa user using the org.database.jdbcDriver and executes the SQL statements contained within the files data1.sql, data2.sql and data3.sql and then executes the truncate operation on some_other_table.

<sql driver="org.database.jdbcDriver"
     url="jdbc:database-url"
     userid="sa"
     password="pass">
  <transaction  src="data1.sql"/>
  <transaction  src="data2.sql"/>
  <transaction  src="data3.sql"/>
  <transaction>
    truncate table some_other_table;
  </transaction>
</sql>

The following example does the same as (and may execute additional SQL files if there are more files matching the pattern data*.sql) but doesn't guarantee that data1.sql will be run before data2.sql.

<sql driver="org.database.jdbcDriver"
     url="jdbc:database-url"
     userid="sa"
     password="pass">
  <path>
    <fileset dir=".">
      <include name="data*.sql"/>
    </fileset>
  </path>
  <transaction>
    truncate table some_other_table;
  </transaction>
</sql>

The following connects to the database given in url as the sa user using the org.database.jdbcDriver and executes the SQL statements contained within the file data.sql, with output piped to outputfile.txt, searching /some/jdbc.jar as well as the system classpath for the driver class.

<sql driver="org.database.jdbcDriver"
     url="jdbc:database-url"
     userid="sa"
     password="pass"
     src="data.sql"
     print="yes"
     output="outputfile.txt">
  <classpath>
    <pathelement location="/some/jdbc.jar"/>
  </classpath>
</sql>

The following will only execute if the RDBMS is oracle and the version starts with 8.1.

<sql driver="org.database.jdbcDriver"
     url="jdbc:database-url"
     userid="sa"
     password="pass"
     src="data.sql"
     rdbms="oracle"
     version="8.1.">
insert
into table some_table
values(1,2,3,4);

truncate table some_other_table;
</sql>