1 | <html>
|
---|
2 | <head>
|
---|
3 | <meta http-equiv="Content-Language" content="en-us">
|
---|
4 | <title>SQL Task</title>
|
---|
5 | <link rel="stylesheet" type="text/css" href="../stylesheets/antmanual.css">
|
---|
6 | </head>
|
---|
7 | <body>
|
---|
8 |
|
---|
9 | <h2><a name="sql">Sql</a></h2>
|
---|
10 | <h3>Description</h3>
|
---|
11 | <p>Executes a series of SQL statements via JDBC to a database. Statements can
|
---|
12 | either be read in from a text file using the <i>src</i> attribute or from
|
---|
13 | between the enclosing SQL tags.</p>
|
---|
14 |
|
---|
15 | <p>Multiple statements can be provided, separated by semicolons (or the
|
---|
16 | defined <i>delimiter</i>). Individual lines within the statements can be
|
---|
17 | commented using either --, // or REM at the start of the line.</p>
|
---|
18 |
|
---|
19 | <p>The <i>autocommit</i> attribute specifies whether auto-commit should be
|
---|
20 | turned on or off whilst executing the statements. If auto-commit is turned
|
---|
21 | on each statement will be executed and committed. If it is turned off the
|
---|
22 | statements will all be executed as one transaction.</p>
|
---|
23 |
|
---|
24 | <p>The <i>onerror</i> attribute specifies how to proceed when an error occurs
|
---|
25 | during the execution of one of the statements.
|
---|
26 | The possible values are: <b>continue</b> execution, only show the error;
|
---|
27 | <b>stop</b> execution and commit transaction;
|
---|
28 | and <b>abort</b> execution and transaction and fail task.</p>
|
---|
29 |
|
---|
30 | <h3>Parameters</h3>
|
---|
31 | <table border="1" cellpadding="2" cellspacing="0">
|
---|
32 | <tr>
|
---|
33 | <td width="12%" valign="top"><b>Attribute</b></td>
|
---|
34 | <td width="78%" valign="top"><b>Description</b></td>
|
---|
35 | <td width="10%" valign="top"><b>Required</b></td>
|
---|
36 | </tr>
|
---|
37 | <tr>
|
---|
38 | <td width="12%" valign="top">driver</td>
|
---|
39 | <td width="78%" valign="top">Class name of the jdbc driver</td>
|
---|
40 | <td width="10%" valign="top">Yes</td>
|
---|
41 | </tr>
|
---|
42 | <tr>
|
---|
43 | <td width="12%" valign="top">url</td>
|
---|
44 | <td width="78%" valign="top">Database connection url</td>
|
---|
45 | <td width="10%" valign="top">Yes</td>
|
---|
46 | </tr>
|
---|
47 | <tr>
|
---|
48 | <td width="12%" valign="top">userid</td>
|
---|
49 | <td width="78%" valign="top">Database user name</td>
|
---|
50 | <td width="10%" valign="top">Yes</td>
|
---|
51 | </tr>
|
---|
52 | <tr>
|
---|
53 | <td width="12%" valign="top">password</td>
|
---|
54 | <td width="78%" valign="top">Database password</td>
|
---|
55 | <td width="10%" valign="top">Yes</td>
|
---|
56 | </tr>
|
---|
57 | <tr>
|
---|
58 | <td width="12%" valign="top">src</td>
|
---|
59 | <td width="78%" valign="top">File containing SQL statements</td>
|
---|
60 | <td width="10%" valign="top">Yes, unless statements enclosed within tags</td>
|
---|
61 | </tr>
|
---|
62 | <tr>
|
---|
63 | <td valign="top">encoding</td>
|
---|
64 | <td valign="top">The encoding of the files containing SQL statements</td>
|
---|
65 | <td align="center">No - defaults to default JVM encoding</td>
|
---|
66 | </tr>
|
---|
67 | <tr>
|
---|
68 | <td width="12%" valign="top">delimiter</td>
|
---|
69 | <td width="78%" valign="top">String that separates SQL statements</td>
|
---|
70 | <td width="10%" valign="top">No, default ";"</td>
|
---|
71 | </tr>
|
---|
72 | <tr>
|
---|
73 | <td width="12%" valign="top">autocommit</td>
|
---|
74 | <td width="78%" valign="top">Auto commit flag for database connection (default false)</td>
|
---|
75 | <td width="10%" valign="top">No, default "false"</td>
|
---|
76 | </tr>
|
---|
77 | <tr>
|
---|
78 | <td width="12%" valign="top">print</td>
|
---|
79 | <td width="78%" valign="top">Print result sets from the statements (default false)</td>
|
---|
80 | <td width="10%" valign="top">No, default "false"</td>
|
---|
81 | </tr>
|
---|
82 | <tr>
|
---|
83 | <td width="12%" valign="top">showheaders</td>
|
---|
84 | <td width="78%" valign="top">Print headers for result sets from the statements (default true)</td>
|
---|
85 | <td width="10%" valign="top">No, default "true"</td>
|
---|
86 | </tr>
|
---|
87 | <tr>
|
---|
88 | <td width="12%" valign="top">output</td>
|
---|
89 | <td width="78%" valign="top">Output file for result sets (defaults to System.out)</td>
|
---|
90 | <td width="10%" valign="top">No (print to System.out by default)</td>
|
---|
91 | </tr>
|
---|
92 | <tr>
|
---|
93 | <td valign="top">append</td>
|
---|
94 | <td valign="top">whether output should be appended to or overwrite
|
---|
95 | an existing file. Defaults to false.</td>
|
---|
96 | <td align="center" valign="top">No</td>
|
---|
97 | </tr>
|
---|
98 | <tr>
|
---|
99 | <td width="12%" valign="top">classpath</td>
|
---|
100 | <td width="78%" valign="top">Classpath used to load driver</td>
|
---|
101 | <td width="10%" valign="top">No (use system classpath)</td>
|
---|
102 | </tr>
|
---|
103 | <tr>
|
---|
104 | <td width="12%" valign="top">classpathref</td>
|
---|
105 | <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>
|
---|
106 | <td width="10%" valign="top">No (use system classpath)</td>
|
---|
107 | </tr>
|
---|
108 | <tr>
|
---|
109 | <td width="12%" valign="top">onerror</td>
|
---|
110 | <td width="78%" valign="top">Action to perform when statement fails: continue, stop, abort</td>
|
---|
111 | <td width="10%" valign="top">No, default "abort"</td>
|
---|
112 | </tr>
|
---|
113 | <tr>
|
---|
114 | <td width="12%" valign="top">rdbms</td>
|
---|
115 | <td width="78%" valign="top">Execute task only if this rdbms</td>
|
---|
116 | <td width="10%" valign="top">No (no restriction)</td>
|
---|
117 | </tr>
|
---|
118 | <tr>
|
---|
119 | <td width="12%" valign="top">version</td>
|
---|
120 | <td width="78%" valign="top">Execute task only if rdbms version match</td>
|
---|
121 | <td width="10%" valign="top">No (no restriction)</td>
|
---|
122 | </tr>
|
---|
123 | <tr>
|
---|
124 | <td width="12%" valign="top">caching</td>
|
---|
125 | <td width="78%" valign="top">Should the task cache loaders and the driver?</td>
|
---|
126 | <td width="10%" valign="top">No (default=true)</td>
|
---|
127 | </tr>
|
---|
128 |
|
---|
129 | <tr>
|
---|
130 | <td width="12%" valign="top">delimitertype</td>
|
---|
131 | <td width="78%" valign="top">Control whether the delimiter will only be recognized on a line by itself.<br>
|
---|
132 | Can be "normal" -anywhere on the line, or "row", meaning it must be on a line by itself</td>
|
---|
133 | <td width="10%" valign="top">No (defaul:normal)</td>
|
---|
134 | </tr>
|
---|
135 |
|
---|
136 | <tr>
|
---|
137 | <td width="12%" valign="top">keepformat</td>
|
---|
138 | <td width="78%" valign="top">Control whether the format of the sql will be preserved.<br>
|
---|
139 | Usefull when loading packages and procedures.
|
---|
140 | <td width="10%" valign="top">No (defaul=false)</td>
|
---|
141 | </tr>
|
---|
142 |
|
---|
143 | <tr>
|
---|
144 | <td width="12%" valign="top">escapeprocessing</td>
|
---|
145 | <td width="78%" valign="top">Control whether the Java statement
|
---|
146 | object will perform escape substitution.<br>
|
---|
147 | See <a
|
---|
148 | href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#setEscapeProcessing(boolean)">Statement's
|
---|
149 | API docs</a> for details. <em>since Ant 1.6</em>.
|
---|
150 | <td width="10%" valign="top">No (defaul=true)</td>
|
---|
151 | </tr>
|
---|
152 |
|
---|
153 | </table>
|
---|
154 |
|
---|
155 | <h3>Parameters specified as nested elements</h3>
|
---|
156 | <h4>transaction</h4>
|
---|
157 | <p>Use nested <code><transaction></code>
|
---|
158 | elements to specify multiple blocks of commands to the executed
|
---|
159 | executed in the same connection but different transactions. This
|
---|
160 | is particularly useful when there are multiple files to execute
|
---|
161 | on the same schema.</p>
|
---|
162 | <table border="1" cellpadding="2" cellspacing="0">
|
---|
163 | <tr>
|
---|
164 | <td valign="top"><b>Attribute</b></td>
|
---|
165 | <td valign="top"><b>Description</b></td>
|
---|
166 | <td align="center" valign="top"><b>Required</b></td>
|
---|
167 | </tr>
|
---|
168 | <tr>
|
---|
169 | <td valign="top">src</td>
|
---|
170 | <td valign="top">File containing SQL statements</td>
|
---|
171 | <td valign="top" align="center">Yes, unless statements enclosed within tags</td>
|
---|
172 | </tr>
|
---|
173 | </table>
|
---|
174 | <h4>fileset</h4>
|
---|
175 | <p>You can specify multiple source files via nested <a
|
---|
176 | href="../CoreTypes/fileset.html">fileset</a> elements. Each file of
|
---|
177 | the fileset will be run in a transaction of its own, the order by
|
---|
178 | which the files of a single fileset will be executed is not
|
---|
179 | defined.</p>
|
---|
180 | <h4>classpath</h4>
|
---|
181 | <p><code>Sql</code>'s <em>classpath</em> attribute is a <a
|
---|
182 | href="../using.html#path">PATH like structure</a> and can also be set via a nested
|
---|
183 | <em>classpath</em> element. It is used to load the JDBC classes.</p>
|
---|
184 |
|
---|
185 | <h3>Examples</h3>
|
---|
186 | <blockquote><pre><sql
|
---|
187 | driver="org.database.jdbcDriver"
|
---|
188 | url="jdbc:database-url"
|
---|
189 | userid="sa"
|
---|
190 | password="pass"
|
---|
191 | src="data.sql"
|
---|
192 | />
|
---|
193 | </pre></blockquote>
|
---|
194 |
|
---|
195 | <p>Connects to the database given in <i>url</i> as the sa user using the
|
---|
196 | org.database.jdbcDriver and executes the SQL statements contained within
|
---|
197 | the file data.sql</p>
|
---|
198 |
|
---|
199 | <blockquote><pre><sql
|
---|
200 | driver="org.database.jdbcDriver"
|
---|
201 | url="jdbc:database-url"
|
---|
202 | userid="sa"
|
---|
203 | password="pass"
|
---|
204 | >
|
---|
205 | insert
|
---|
206 | into table some_table
|
---|
207 | values(1,2,3,4);
|
---|
208 |
|
---|
209 | truncate table some_other_table;
|
---|
210 | </sql>
|
---|
211 | </pre></blockquote>
|
---|
212 |
|
---|
213 | <p>Connects to the database given in <i>url</i> as the sa
|
---|
214 | user using the org.database.jdbcDriver and executes the two SQL statements
|
---|
215 | inserting data into some_table and truncating some_other_table </p>
|
---|
216 |
|
---|
217 | <p>Note that you may want to enclose your statements in
|
---|
218 | <code><![CDATA[</code> ... <code>]]></code> sections so you don't
|
---|
219 | need to escape <code><</code>, <code>></code> <code>&</code>
|
---|
220 | or other special characters. For example:</p>
|
---|
221 |
|
---|
222 | <blockquote><pre><sql
|
---|
223 | driver="org.database.jdbcDriver"
|
---|
224 | url="jdbc:database-url"
|
---|
225 | userid="sa"
|
---|
226 | password="pass"
|
---|
227 | ><![CDATA[
|
---|
228 |
|
---|
229 | update some_table set column1 = column1 + 1 where column2 < 42;
|
---|
230 |
|
---|
231 | ]]></sql>
|
---|
232 | </pre></blockquote>
|
---|
233 |
|
---|
234 | <p>The following connects to the database given in url as the sa user using
|
---|
235 | the org.database.jdbcDriver and executes the SQL statements contained within
|
---|
236 | the files data1.sql, data2.sql and data3.sql and then executes the truncate
|
---|
237 | operation on <i>some_other_table</i>.</p>
|
---|
238 |
|
---|
239 | <blockquote><pre><sql
|
---|
240 | driver="org.database.jdbcDriver"
|
---|
241 | url="jdbc:database-url"
|
---|
242 | userid="sa"
|
---|
243 | password="pass" >
|
---|
244 | <transaction src="data1.sql"/>
|
---|
245 | <transaction src="data2.sql"/>
|
---|
246 | <transaction src="data3.sql"/>
|
---|
247 | <transaction>
|
---|
248 | truncate table some_other_table;
|
---|
249 | </transaction>
|
---|
250 | </sql>
|
---|
251 | </pre></blockquote>
|
---|
252 |
|
---|
253 | <p>The following example does the same as (and may execute additional
|
---|
254 | SQL files if there are more files matching the pattern
|
---|
255 | <code>data*.sql</code>) but doesn't guarantee that data1.sql will be
|
---|
256 | run before <code>data2.sql</code>.</p>
|
---|
257 |
|
---|
258 | <blockquote><pre><sql
|
---|
259 | driver="org.database.jdbcDriver"
|
---|
260 | url="jdbc:database-url"
|
---|
261 | userid="sa"
|
---|
262 | password="pass">
|
---|
263 | <fileset dir=".">
|
---|
264 | <include name="data*.sql"/>
|
---|
265 | </fileset>
|
---|
266 | <transaction>
|
---|
267 | truncate table some_other_table;
|
---|
268 | </transaction>
|
---|
269 | </sql>
|
---|
270 | </pre></blockquote>
|
---|
271 |
|
---|
272 | <p>The following connects to the database given in url as the sa user using the
|
---|
273 | org.database.jdbcDriver and executes the SQL statements contained within the
|
---|
274 | file data.sql, with output piped to outputfile.txt, searching /some/jdbc.jar
|
---|
275 | as well as the system classpath for the driver class.</p>
|
---|
276 |
|
---|
277 | <blockquote><pre><sql
|
---|
278 | driver="org.database.jdbcDriver"
|
---|
279 | url="jdbc:database-url"
|
---|
280 | userid="sa"
|
---|
281 | password="pass"
|
---|
282 | src="data.sql"
|
---|
283 | print="yes"
|
---|
284 | output="outputfile.txt"
|
---|
285 | >
|
---|
286 | <classpath>
|
---|
287 | <pathelement location="/some/jdbc.jar"/>
|
---|
288 | </classpath>
|
---|
289 | </sql>
|
---|
290 | </pre></blockquote>
|
---|
291 |
|
---|
292 | <p>The following will only execute if the RDBMS is "oracle" and the version
|
---|
293 | starts with "8.1."</p>
|
---|
294 |
|
---|
295 | <blockquote><pre><sql
|
---|
296 | driver="org.database.jdbcDriver"
|
---|
297 | url="jdbc:database-url"
|
---|
298 | userid="sa"
|
---|
299 | password="pass"
|
---|
300 | src="data.sql"
|
---|
301 | rdbms="oracle"
|
---|
302 | version="8.1."
|
---|
303 | >
|
---|
304 | insert
|
---|
305 | into table some_table
|
---|
306 | values(1,2,3,4);
|
---|
307 |
|
---|
308 | truncate table some_other_table;
|
---|
309 | </sql>
|
---|
310 | </pre></blockquote>
|
---|
311 |
|
---|
312 | <hr>
|
---|
313 | <p align="center">Copyright © 2000-2004 The Apache Software Foundation. All rights
|
---|
314 | Reserved.</p>
|
---|
315 | </body>
|
---|
316 | </html>
|
---|