[14982] | 1 | <!--
|
---|
| 2 | Licensed to the Apache Software Foundation (ASF) under one or more
|
---|
| 3 | contributor license agreements. See the NOTICE file distributed with
|
---|
| 4 | this work for additional information regarding copyright ownership.
|
---|
| 5 | The ASF licenses this file to You under the Apache License, Version 2.0
|
---|
| 6 | (the "License"); you may not use this file except in compliance with
|
---|
| 7 | the License. You may obtain a copy of the License at
|
---|
| 8 |
|
---|
| 9 | http://www.apache.org/licenses/LICENSE-2.0
|
---|
| 10 |
|
---|
| 11 | Unless required by applicable law or agreed to in writing, software
|
---|
| 12 | distributed under the License is distributed on an "AS IS" BASIS,
|
---|
| 13 | WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
|
---|
| 14 | See the License for the specific language governing permissions and
|
---|
| 15 | limitations under the License.
|
---|
| 16 | -->
|
---|
| 17 | <html>
|
---|
| 18 | <head>
|
---|
| 19 | <meta http-equiv="Content-Language" content="en-us">
|
---|
| 20 | <link rel="stylesheet" type="text/css" href="../stylesheets/style.css">
|
---|
| 21 | <title>SQL Task</title>
|
---|
| 22 | </head>
|
---|
| 23 | <body>
|
---|
| 24 |
|
---|
| 25 | <h2><a name="sql">Sql</a></h2>
|
---|
| 26 | <h3>Description</h3>
|
---|
| 27 | <p>Executes a series of SQL statements via JDBC to a database. Statements can
|
---|
| 28 | either be read in from a text file using the <i>src</i> attribute or from
|
---|
| 29 | between the enclosing SQL tags.</p>
|
---|
| 30 |
|
---|
| 31 | <p>Multiple statements can be provided, separated by semicolons (or the
|
---|
| 32 | defined <i>delimiter</i>). Individual lines within the statements can be
|
---|
| 33 | commented using either --, // or REM at the start of the line.</p>
|
---|
| 34 |
|
---|
| 35 | <p>The <i>autocommit</i> attribute specifies whether auto-commit should be
|
---|
| 36 | turned on or off whilst executing the statements. If auto-commit is turned
|
---|
| 37 | on each statement will be executed and committed. If it is turned off the
|
---|
| 38 | statements will all be executed as one transaction.</p>
|
---|
| 39 |
|
---|
| 40 | <p>The <i>onerror</i> attribute specifies how to proceed when an error occurs
|
---|
| 41 | during the execution of one of the statements.
|
---|
| 42 | The possible values are: <b>continue</b> execution, only show the error;
|
---|
| 43 | <b>stop</b> execution and commit transaction;
|
---|
| 44 | and <b>abort</b> execution and transaction and fail task.</p>
|
---|
| 45 |
|
---|
| 46 | <p>
|
---|
| 47 | <b>Proxies</b>. Some JDBC drivers (including the Oracle thin driver),
|
---|
| 48 | use the JVM's proxy settings to route their JDBC operations to the database.
|
---|
| 49 | Since Ant1.7, Ant running on Java1.5 or later defaults to
|
---|
| 50 | <a href="../proxy.html">using
|
---|
| 51 | the proxy settings of the operating system</a>.
|
---|
| 52 | Accordingly, the OS proxy settings need to be valid, or Ant's proxy
|
---|
| 53 | support disabled with <code>-noproxy</code> option.
|
---|
| 54 | </p>
|
---|
| 55 |
|
---|
| 56 | <h3>Parameters</h3>
|
---|
| 57 | <table border="1" cellpadding="2" cellspacing="0">
|
---|
| 58 | <tr>
|
---|
| 59 | <td width="12%" valign="top"><b>Attribute</b></td>
|
---|
| 60 | <td width="78%" valign="top"><b>Description</b></td>
|
---|
| 61 | <td width="10%" valign="top"><b>Required</b></td>
|
---|
| 62 | </tr>
|
---|
| 63 | <tr>
|
---|
| 64 | <td width="12%" valign="top">driver</td>
|
---|
| 65 | <td width="78%" valign="top">Class name of the jdbc driver</td>
|
---|
| 66 | <td width="10%" valign="top">Yes</td>
|
---|
| 67 | </tr>
|
---|
| 68 | <tr>
|
---|
| 69 | <td width="12%" valign="top">url</td>
|
---|
| 70 | <td width="78%" valign="top">Database connection url</td>
|
---|
| 71 | <td width="10%" valign="top">Yes</td>
|
---|
| 72 | </tr>
|
---|
| 73 | <tr>
|
---|
| 74 | <td width="12%" valign="top">userid</td>
|
---|
| 75 | <td width="78%" valign="top">Database user name</td>
|
---|
| 76 | <td width="10%" valign="top">Yes</td>
|
---|
| 77 | </tr>
|
---|
| 78 | <tr>
|
---|
| 79 | <td width="12%" valign="top">password</td>
|
---|
| 80 | <td width="78%" valign="top">Database password</td>
|
---|
| 81 | <td width="10%" valign="top">Yes</td>
|
---|
| 82 | </tr>
|
---|
| 83 | <tr>
|
---|
| 84 | <td width="12%" valign="top">src</td>
|
---|
| 85 | <td width="78%" valign="top">File containing SQL statements</td>
|
---|
| 86 | <td width="10%" valign="top">Yes, unless statements enclosed within tags</td>
|
---|
| 87 | </tr>
|
---|
| 88 | <tr>
|
---|
| 89 | <td valign="top">encoding</td>
|
---|
| 90 | <td valign="top">The encoding of the files containing SQL statements</td>
|
---|
| 91 | <td align="center">No - defaults to default JVM encoding</td>
|
---|
| 92 | </tr>
|
---|
| 93 | <tr>
|
---|
| 94 | <td width="12%" valign="top">delimiter</td>
|
---|
| 95 | <td width="78%" valign="top">String that separates SQL statements</td>
|
---|
| 96 | <td width="10%" valign="top">No, default ";"</td>
|
---|
| 97 | </tr>
|
---|
| 98 | <tr>
|
---|
| 99 | <td width="12%" valign="top">autocommit</td>
|
---|
| 100 | <td width="78%" valign="top">Auto commit flag for database connection (default false)</td>
|
---|
| 101 | <td width="10%" valign="top">No, default "false"</td>
|
---|
| 102 | </tr>
|
---|
| 103 | <tr>
|
---|
| 104 | <td width="12%" valign="top">print</td>
|
---|
| 105 | <td width="78%" valign="top">Print result sets from the statements (default false)</td>
|
---|
| 106 | <td width="10%" valign="top">No, default "false"</td>
|
---|
| 107 | </tr>
|
---|
| 108 | <tr>
|
---|
| 109 | <td width="12%" valign="top">showheaders</td>
|
---|
| 110 | <td width="78%" valign="top">Print headers for result sets from the statements (default true)</td>
|
---|
| 111 | <td width="10%" valign="top">No, default "true"</td>
|
---|
| 112 | </tr>
|
---|
| 113 | <tr>
|
---|
| 114 | <td width="12%" valign="top">showtrailers</td>
|
---|
| 115 | <td width="78%" valign="top">Print trailer for number of rows affected (default true)</td>
|
---|
| 116 | <td width="10%" valign="top">No, default "true"</td>
|
---|
| 117 | </tr>
|
---|
| 118 | <tr>
|
---|
| 119 | <td width="12%" valign="top">output</td>
|
---|
| 120 | <td width="78%" valign="top">Output file for result sets (defaults to System.out)</td>
|
---|
| 121 | <td width="10%" valign="top">No (print to System.out by default)</td>
|
---|
| 122 | </tr>
|
---|
| 123 | <tr>
|
---|
| 124 | <td valign="top">append</td>
|
---|
| 125 | <td valign="top">whether output should be appended to or overwrite
|
---|
| 126 | an existing file. Defaults to false.</td>
|
---|
| 127 | <td align="center" valign="top">No</td>
|
---|
| 128 | </tr>
|
---|
| 129 | <tr>
|
---|
| 130 | <td width="12%" valign="top">classpath</td>
|
---|
| 131 | <td width="78%" valign="top">Classpath used to load driver</td>
|
---|
| 132 | <td width="10%" valign="top">No (use system classpath)</td>
|
---|
| 133 | </tr>
|
---|
| 134 | <tr>
|
---|
| 135 | <td width="12%" valign="top">classpathref</td>
|
---|
| 136 | <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>
|
---|
| 137 | <td width="10%" valign="top">No (use system classpath)</td>
|
---|
| 138 | </tr>
|
---|
| 139 | <tr>
|
---|
| 140 | <td width="12%" valign="top">onerror</td>
|
---|
| 141 | <td width="78%" valign="top">Action to perform when statement fails: continue, stop, abort</td>
|
---|
| 142 | <td width="10%" valign="top">No, default "abort"</td>
|
---|
| 143 | </tr>
|
---|
| 144 | <tr>
|
---|
| 145 | <td width="12%" valign="top">rdbms</td>
|
---|
| 146 | <td width="78%" valign="top">Execute task only if this rdbms</td>
|
---|
| 147 | <td width="10%" valign="top">No (no restriction)</td>
|
---|
| 148 | </tr>
|
---|
| 149 | <tr>
|
---|
| 150 | <td width="12%" valign="top">version</td>
|
---|
| 151 | <td width="78%" valign="top">Execute task only if rdbms version match</td>
|
---|
| 152 | <td width="10%" valign="top">No (no restriction)</td>
|
---|
| 153 | </tr>
|
---|
| 154 | <tr>
|
---|
| 155 | <td width="12%" valign="top">caching</td>
|
---|
| 156 | <td width="78%" valign="top">Should the task cache loaders and the driver?</td>
|
---|
| 157 | <td width="10%" valign="top">No (default=true)</td>
|
---|
| 158 | </tr>
|
---|
| 159 |
|
---|
| 160 | <tr>
|
---|
| 161 | <td width="12%" valign="top">delimitertype</td>
|
---|
| 162 | <td width="78%" valign="top">Control whether the delimiter will only be recognized on a line by itself.<br>
|
---|
| 163 | Can be "normal" -anywhere on the line, or "row", meaning it must be on a line by itself</td>
|
---|
| 164 | <td width="10%" valign="top">No (default:normal)</td>
|
---|
| 165 | </tr>
|
---|
| 166 |
|
---|
| 167 | <tr>
|
---|
| 168 | <td width="12%" valign="top">keepformat</td>
|
---|
| 169 | <td width="78%" valign="top">Control whether the format of the sql will be preserved.<br>
|
---|
| 170 | Usefull when loading packages and procedures.
|
---|
| 171 | <td width="10%" valign="top">No (default=false)</td>
|
---|
| 172 | </tr>
|
---|
| 173 |
|
---|
| 174 | <tr>
|
---|
| 175 | <td width="12%" valign="top">escapeprocessing</td>
|
---|
| 176 | <td width="78%" valign="top">Control whether the Java statement
|
---|
| 177 | object will perform escape substitution.<br>
|
---|
| 178 | See <a
|
---|
| 179 | href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#setEscapeProcessing(boolean)">Statement's
|
---|
| 180 | API docs</a> for details. <em>Since Ant 1.6</em>.
|
---|
| 181 | <td width="10%" valign="top">No (default=true)</td>
|
---|
| 182 | </tr>
|
---|
| 183 |
|
---|
| 184 | <tr>
|
---|
| 185 | <td width="12%" valign="top">expandproperties</td>
|
---|
| 186 | <td width="78%" valign="top">Set to true to turn on property expansion in
|
---|
| 187 | nested SQL, inline in the task or nested transactions. <em>Since Ant 1.7</em>.
|
---|
| 188 | <td width="10%" valign="top">No (default=false)</td>
|
---|
| 189 | </tr>
|
---|
| 190 |
|
---|
| 191 | </table>
|
---|
| 192 |
|
---|
| 193 | <h3>Parameters specified as nested elements</h3>
|
---|
| 194 | <h4>transaction</h4>
|
---|
| 195 | <p>Use nested <code><transaction></code>
|
---|
| 196 | elements to specify multiple blocks of commands to the executed
|
---|
| 197 | executed in the same connection but different transactions. This
|
---|
| 198 | is particularly useful when there are multiple files to execute
|
---|
| 199 | on the same schema.</p>
|
---|
| 200 | <table border="1" cellpadding="2" cellspacing="0">
|
---|
| 201 | <tr>
|
---|
| 202 | <td valign="top"><b>Attribute</b></td>
|
---|
| 203 | <td valign="top"><b>Description</b></td>
|
---|
| 204 | <td align="center" valign="top"><b>Required</b></td>
|
---|
| 205 | </tr>
|
---|
| 206 | <tr>
|
---|
| 207 | <td valign="top">src</td>
|
---|
| 208 | <td valign="top">File containing SQL statements</td>
|
---|
| 209 | <td valign="top" align="center">Yes, unless statements enclosed within tags</td>
|
---|
| 210 | </tr>
|
---|
| 211 | </table>
|
---|
| 212 | <p>The <code><transaction></code> element supports any <a
|
---|
| 213 | href="../CoreTypes/resources.html">resource</a> or single element
|
---|
| 214 | resource collection as nested element to specify the resource
|
---|
| 215 | containing the SQL statements.</p>
|
---|
| 216 |
|
---|
| 217 | <h4>any <a href="../CoreTypes/resources.html">resource</a> or resource
|
---|
| 218 | collection</h4>
|
---|
| 219 |
|
---|
| 220 | <p>You can specify multiple sources via nested resource collection
|
---|
| 221 | elements. Each resource of the collection will be run in a
|
---|
| 222 | transaction of its own. Prior to Ant 1.7 only filesets were
|
---|
| 223 | supported. Use a sort resource collection to get a predictable order
|
---|
| 224 | of transactions. </p>
|
---|
| 225 |
|
---|
| 226 | <h4>classpath</h4>
|
---|
| 227 | <p><code>Sql</code>'s <em>classpath</em> attribute is a <a
|
---|
| 228 | href="../using.html#path">PATH like structure</a> and can also be set via a nested
|
---|
| 229 | <em>classpath</em> element. It is used to load the JDBC classes.</p>
|
---|
| 230 |
|
---|
| 231 | <h3>Examples</h3>
|
---|
| 232 | <blockquote><pre><sql
|
---|
| 233 | driver="org.database.jdbcDriver"
|
---|
| 234 | url="jdbc:database-url"
|
---|
| 235 | userid="sa"
|
---|
| 236 | password="pass"
|
---|
| 237 | src="data.sql"
|
---|
| 238 | />
|
---|
| 239 | </pre></blockquote>
|
---|
| 240 |
|
---|
| 241 | <p>Connects to the database given in <i>url</i> as the sa user using the
|
---|
| 242 | org.database.jdbcDriver and executes the SQL statements contained within
|
---|
| 243 | the file data.sql</p>
|
---|
| 244 |
|
---|
| 245 | <blockquote><pre><sql
|
---|
| 246 | driver="org.database.jdbcDriver"
|
---|
| 247 | url="jdbc:database-url"
|
---|
| 248 | userid="sa"
|
---|
| 249 | password="pass"
|
---|
| 250 | >
|
---|
| 251 | insert
|
---|
| 252 | into table some_table
|
---|
| 253 | values(1,2,3,4);
|
---|
| 254 |
|
---|
| 255 | truncate table some_other_table;
|
---|
| 256 | </sql>
|
---|
| 257 | </pre></blockquote>
|
---|
| 258 |
|
---|
| 259 | <p>Connects to the database given in <i>url</i> as the sa
|
---|
| 260 | user using the org.database.jdbcDriver and executes the two SQL statements
|
---|
| 261 | inserting data into some_table and truncating some_other_table. Ant Properties
|
---|
| 262 | in the nested text will not be expanded.</p>
|
---|
| 263 |
|
---|
| 264 | <p>Note that you may want to enclose your statements in
|
---|
| 265 | <code><![CDATA[</code> ... <code>]]></code> sections so you don't
|
---|
| 266 | need to escape <code><</code>, <code>></code> <code>&</code>
|
---|
| 267 | or other special characters. For example:</p>
|
---|
| 268 |
|
---|
| 269 | <blockquote><pre><sql
|
---|
| 270 | driver="org.database.jdbcDriver"
|
---|
| 271 | url="jdbc:database-url"
|
---|
| 272 | userid="sa"
|
---|
| 273 | password="pass"
|
---|
| 274 | ><![CDATA[
|
---|
| 275 |
|
---|
| 276 | update some_table set column1 = column1 + 1 where column2 < 42;
|
---|
| 277 |
|
---|
| 278 | ]]></sql>
|
---|
| 279 | </pre></blockquote>
|
---|
| 280 |
|
---|
| 281 | The following command turns property expansion in nested text on (it is off purely for backwards
|
---|
| 282 | compatibility), then creates a new user in the HSQLDB database using Ant properties.
|
---|
| 283 |
|
---|
| 284 | <blockquote><pre><sql
|
---|
| 285 | driver="org.hsqldb.jdbcDriver";
|
---|
| 286 | url="jdbc:hsqldb:file:${database.dir}"
|
---|
| 287 | userid="sa"
|
---|
| 288 | password=""
|
---|
| 289 | expandProperties="true"
|
---|
| 290 | >
|
---|
| 291 | <transaction>
|
---|
| 292 | CREATE USER ${newuser} PASSWORD ${newpassword}
|
---|
| 293 | </transaction>
|
---|
| 294 | </sql>
|
---|
| 295 | </pre></blockquote>
|
---|
| 296 |
|
---|
| 297 |
|
---|
| 298 | <p>The following connects to the database given in url as the sa user using
|
---|
| 299 | the org.database.jdbcDriver and executes the SQL statements contained within
|
---|
| 300 | the files data1.sql, data2.sql and data3.sql and then executes the truncate
|
---|
| 301 | operation on <i>some_other_table</i>.</p>
|
---|
| 302 |
|
---|
| 303 | <blockquote><pre><sql
|
---|
| 304 | driver="org.database.jdbcDriver"
|
---|
| 305 | url="jdbc:database-url"
|
---|
| 306 | userid="sa"
|
---|
| 307 | password="pass" >
|
---|
| 308 | <transaction src="data1.sql"/>
|
---|
| 309 | <transaction src="data2.sql"/>
|
---|
| 310 | <transaction src="data3.sql"/>
|
---|
| 311 | <transaction>
|
---|
| 312 | truncate table some_other_table;
|
---|
| 313 | </transaction>
|
---|
| 314 | </sql>
|
---|
| 315 | </pre></blockquote>
|
---|
| 316 |
|
---|
| 317 | <p>The following example does the same as (and may execute additional
|
---|
| 318 | SQL files if there are more files matching the pattern
|
---|
| 319 | <code>data*.sql</code>) but doesn't guarantee that data1.sql will be
|
---|
| 320 | run before <code>data2.sql</code>.</p>
|
---|
| 321 |
|
---|
| 322 | <blockquote><pre><sql
|
---|
| 323 | driver="org.database.jdbcDriver"
|
---|
| 324 | url="jdbc:database-url"
|
---|
| 325 | userid="sa"
|
---|
| 326 | password="pass">
|
---|
| 327 | <path>
|
---|
| 328 | <fileset dir=".">
|
---|
| 329 | <include name="data*.sql"/>
|
---|
| 330 | </fileset>
|
---|
| 331 | <path>
|
---|
| 332 | <transaction>
|
---|
| 333 | truncate table some_other_table;
|
---|
| 334 | </transaction>
|
---|
| 335 | </sql>
|
---|
| 336 | </pre></blockquote>
|
---|
| 337 |
|
---|
| 338 | <p>The following connects to the database given in url as the sa user using the
|
---|
| 339 | org.database.jdbcDriver and executes the SQL statements contained within the
|
---|
| 340 | file data.sql, with output piped to outputfile.txt, searching /some/jdbc.jar
|
---|
| 341 | as well as the system classpath for the driver class.</p>
|
---|
| 342 |
|
---|
| 343 | <blockquote><pre><sql
|
---|
| 344 | driver="org.database.jdbcDriver"
|
---|
| 345 | url="jdbc:database-url"
|
---|
| 346 | userid="sa"
|
---|
| 347 | password="pass"
|
---|
| 348 | src="data.sql"
|
---|
| 349 | print="yes"
|
---|
| 350 | output="outputfile.txt"
|
---|
| 351 | >
|
---|
| 352 | <classpath>
|
---|
| 353 | <pathelement location="/some/jdbc.jar"/>
|
---|
| 354 | </classpath>
|
---|
| 355 | </sql>
|
---|
| 356 | </pre></blockquote>
|
---|
| 357 |
|
---|
| 358 | <p>The following will only execute if the RDBMS is "oracle" and the version
|
---|
| 359 | starts with "8.1."</p>
|
---|
| 360 |
|
---|
| 361 | <blockquote><pre><sql
|
---|
| 362 | driver="org.database.jdbcDriver"
|
---|
| 363 | url="jdbc:database-url"
|
---|
| 364 | userid="sa"
|
---|
| 365 | password="pass"
|
---|
| 366 | src="data.sql"
|
---|
| 367 | rdbms="oracle"
|
---|
| 368 | version="8.1."
|
---|
| 369 | >
|
---|
| 370 | insert
|
---|
| 371 | into table some_table
|
---|
| 372 | values(1,2,3,4);
|
---|
| 373 |
|
---|
| 374 | truncate table some_other_table;
|
---|
| 375 | </sql>
|
---|
| 376 | </pre></blockquote>
|
---|
| 377 |
|
---|
| 378 |
|
---|
| 379 | </body>
|
---|
| 380 | </html>
|
---|