java left logo
java middle logo
java right logo
 

Home arrow Other API Tips
 
 
Main Menu
Home
Java Tutorials
Book Reviews
Java SE Tips
Java ME Tips
Java EE Tips
Other API Tips
Java Applications
Java Libraries
Java Games
Java Network
Java Forums
Java Blog




Most Visited Tips
Java SE Tips
Java ME Tips
Java EE Tips
Other API Tips
Java Applications
Java Libraries
Java Games
Book Reviews
Top Rated Tips
Java SE Tips
Java ME Tips
Java EE Tips
Other API Tips
Java Applications
Java Libraries
Java Games
Book Reviews


Statistics
Registered Users: 4092
Java SE Tips: 614
Java ME Tips: 202
Java EE Tips: 183
Other API Tips: 779
Java Applications: 298
Java Libraries: 209
Java Games: 16
Book Reviews:
 
 
 
How to use Sql task E-mail
User Rating: / 10
PoorBest 

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 and commit transaction; and abort execution and transaction and fail task.

Parameters

Attribute Description Required
driver Class name of the jdbc driver Yes
url Database connection url Yes
userid Database user name Yes
password Database password Yes
src File containing SQL statements Yes, unless statements enclosed within tags
encoding The encoding of the files containing SQL statements No - defaults to default JVM encoding
delimiter String that separates SQL statements No, default ";"
autocommit Auto commit flag for database connection (default false) No, default "false"
print Print result sets from the statements (default false) No, default "false"
showheaders Print headers for result sets from the statements (default true) No, default "true"
output Output file for result sets (defaults to System.out) No (print to System.out by default)
append whether output should be appended to or overwrite an existing file. Defaults to false. No
classpath Classpath used to load driver No (use system classpath)
classpathref The classpath to use, given as a reference to a path defined elsewhere. No (use system classpath)
onerror Action to perform when statement fails: continue, stop, abort No, default "abort"
rdbms Execute task only if this rdbms No (no restriction)
version Execute task only if rdbms version match No (no restriction)
caching Should the task cache loaders and the driver? No (default=true)
delimitertype Control whether the delimiter will only be recognized on a line by itself.
Can be "normal" -anywhere on the line, or "row", meaning it must be on a line by itself
No (defaul:normal)
keepformat Control whether the format of the sql will be preserved.
Usefull when loading packages and procedures.
No (defaul=false)
escapeprocessing Control whether the Java statement object will perform escape substitution.
See Statement's API docs for details. since Ant 1.6.
No (defaul=true)

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.

Attribute Description Required
src File containing SQL statements Yes, unless statements enclosed within tags

fileset

You can specify multiple source files via nested fileset elements. Each file of the fileset will be run in a transaction of its own, the order by which the files of a single fileset will be executed is not defined.

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.

Examples

<sql
    driver="org.database.jdbcDriver"
    url="jdbc:database-url"
    userid="sa"

    password="pass"
    src="data.sql"
/>

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

<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>

Connects to the database given in url as the sa user using the org.database.jdbcDriver and executes the two SQL statements inserting data into some_table and truncating some_other_table

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 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">
  <fileset dir=".">
    <include name="data*.sql"/>
  </fileset>
  <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>

Source: Apache Ant


 Related Tips

 
< Prev   Next >

Page 1 of 0 ( 0 comments )

You can share your information about this topic using the form below!

Please do not post your questions with this form! Thanks.


Name (required)


E-Mail (required)

Your email will not be displayed on the site - only to our administrator
Homepage(optional)



Comment Enable HTML code : Yes No



 
       
         
     
 
 
 
   
 
 
java bottom left
java bottom middle
java bottom right
RSS 0.91 FeedRSS 1.0 FeedRSS 2.0 FeedATOM FeedOPML Feed

Home - About Us - Privacy Policy
Copyright 2005 - 2008 www.java-tips.org
Java is a trademark of Sun Microsystems, Inc.