| <html> |
| <head> |
| <meta http-equiv="Content-Language" content="en-us"> |
| <title>SQL Task</title> |
| </head> |
| <body> |
| |
| <h2><a name="sql">Sql</a></h2> |
| <h3>Description</h3> |
| <p>Executes a series of SQL statements via JDBC to a database. Statements can |
| either be read in from a text file using the <i>src</i> attribute or from |
| between the enclosing SQL tags.</p> |
| |
| <p>Multiple statements can be provided, separated by semicolons (or the |
| defined <i>delimiter</i>). Individual lines within the statements can be |
| commented using either --, // or REM at the start of the line.</p> |
| |
| <p>The <i>autocommit</i> 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.</p> |
| |
| <p>The <i>onerror</i> attribute specifies how to proceed when an error occurs |
| during the execution of one of the statements. |
| The possible values are: <b>continue</b> execution, only show the error; |
| <b>stop</b> execution and commit transaction; |
| and <b>abort</b> execution and transaction and fail task.</p> |
| |
| <h3>Parameters</h3> |
| <table border="1" cellpadding="2" cellspacing="0"> |
| <tr> |
| <td width="12%" valign="top"><b>Attribute</b></td> |
| <td width="78%" valign="top"><b>Description</b></td> |
| <td width="10%" valign="top"><b>Required</b></td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">driver</td> |
| <td width="78%" valign="top">Class name of the jdbc driver</td> |
| <td width="10%" valign="top">Yes</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">url</td> |
| <td width="78%" valign="top">Database connection url</td> |
| <td width="10%" valign="top">Yes</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">userid</td> |
| <td width="78%" valign="top">Database user name</td> |
| <td width="10%" valign="top">Yes</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">password</td> |
| <td width="78%" valign="top">Database password</td> |
| <td width="10%" valign="top">Yes</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">src</td> |
| <td width="78%" valign="top">File containing SQL statements</td> |
| <td width="10%" valign="top">Yes, unless statements enclosed within tags</td> |
| </tr> |
| <tr> |
| <td valign="top">encoding</td> |
| <td valign="top">The encoding of the files containing SQL statements</td> |
| <td align="center">No - defaults to default JVM encoding</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">delimiter</td> |
| <td width="78%" valign="top">String that separates SQL statements</td> |
| <td width="10%" valign="top">No, default ";"</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">autocommit</td> |
| <td width="78%" valign="top">Auto commit flag for database connection (default false)</td> |
| <td width="10%" valign="top">No, default "false"</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">print</td> |
| <td width="78%" valign="top">Print result sets from the statements (default false)</td> |
| <td width="10%" valign="top">No, default "false"</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">showheaders</td> |
| <td width="78%" valign="top">Print headers for result sets from the statements (default true)</td> |
| <td width="10%" valign="top">No, default "true"</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">output</td> |
| <td width="78%" valign="top">Output file for result sets (defaults to System.out)</td> |
| <td width="10%" valign="top">No (print to System.out by default)</td> |
| </tr> |
| <tr> |
| <td valign="top">append</td> |
| <td valign="top">whether output should be appended to or overwrite |
| an existing file. Defaults to false.</td> |
| <td align="center" valign="top">No</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">classpath</td> |
| <td width="78%" valign="top">Classpath used to load driver</td> |
| <td width="10%" valign="top">No (use system classpath)</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">classpathref</td> |
| <td width="78%" valign="top">The classpath to use, given as a <a href="../using.html#references">reference</a> to a path defined elsewhere.</td> |
| <td width="10%" valign="top">No (use system classpath)</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">onerror</td> |
| <td width="78%" valign="top">Action to perform when statement fails: continue, stop, abort</td> |
| <td width="10%" valign="top">No, default "abort"</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">rdbms</td> |
| <td width="78%" valign="top">Execute task only if this rdbms</td> |
| <td width="10%" valign="top">No (no restriction)</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">version</td> |
| <td width="78%" valign="top">Execute task only if rdbms version match</td> |
| <td width="10%" valign="top">No (no restriction)</td> |
| </tr> |
| <tr> |
| <td width="12%" valign="top">caching</td> |
| <td width="78%" valign="top">Should the task cache loaders and the driver?</td> |
| <td width="10%" valign="top">No (default=true)</td> |
| </tr> |
| </table> |
| |
| <h3>Parameters specified as nested elements</h3> |
| <h4>transaction</h4> |
| <p>Use nested <code><transaction></code> |
| 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.</p> |
| <table border="1" cellpadding="2" cellspacing="0"> |
| <tr> |
| <td valign="top"><b>Attribute</b></td> |
| <td valign="top"><b>Description</b></td> |
| <td align="center" valign="top"><b>Required</b></td> |
| </tr> |
| <tr> |
| <td valign="top">src</td> |
| <td valign="top">File containing SQL statements</td> |
| <td valign="top" align="center">Yes, unless statements enclosed within tags</td> |
| </tr> |
| </table> |
| <h4>fileset</h4> |
| <p>You can specify multiple source files via nested <a |
| href="../CoreTypes/fileset.html">fileset</a> 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.</p> |
| <h4>classpath</h4> |
| <p><code>Sql</code>'s <em>classpath</em> attribute is a <a |
| href="../using.html#path">PATH like structure</a> and can also be set via a nested |
| <em>classpath</em> element. It is used to load the JDBC classes.</p> |
| <p> |
| The |
| </p> |
| |
| <h3>Examples</h3> |
| <blockquote><pre><sql |
| driver="org.database.jdbcDriver" |
| url="jdbc:database-url" |
| userid="sa" |
| password="pass" |
| src="data.sql" |
| /> |
| </pre></blockquote> |
| |
| <p>Connects to the database given in <i>url</i> as the sa user using the |
| org.database.jdbcDriver and executes the SQL statements contained within |
| the file data.sql</p> |
| |
| <blockquote><pre><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> |
| </pre></blockquote> |
| |
| <p>Connects to the database given in <i>url</i> 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 </p> |
| |
| <p>Note that you may want to enclose your statements in |
| <code><![CDATA[</code> ... <code>]]></code> sections so you don't |
| need to escape <code><</code>, <code>></code> <code>&</code> |
| or other special characters. For example:</p> |
| |
| <blockquote><pre><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> |
| </pre></blockquote> |
| |
| <p>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 <i>some_other_table</i>.</p> |
| |
| <blockquote><pre><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> |
| </pre></blockquote> |
| |
| <p>The following example does the same as (and may execute additional |
| SQL files if there are more files matching the pattern |
| <code>data*.sql</code>) but doesn't guarantee that data1.sql will be |
| run before <code>data2.sql</code>.</p> |
| |
| <blockquote><pre><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> |
| </pre></blockquote> |
| |
| <p>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.</p> |
| |
| <blockquote><pre><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> |
| </pre></blockquote> |
| |
| <p>The following will only execute if the RDBMS is "oracle" and the version |
| starts with "8.1."</p> |
| |
| <blockquote><pre><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> |
| </pre></blockquote> |
| |
| <hr> |
| <p align="center">Copyright © 2000-2002 Apache Software Foundation. All rights |
| Reserved.</p> |
| </body> |
| </html> |