source: release-kits/lirk3/bin/ant-installer/web/manual1.7.0/manual/CoreTasks/sql.html@ 14982

Last change on this file since 14982 was 14982, checked in by oranfry, 16 years ago

initial import of LiRK3
File size: 13.5 KB
Line 
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
28either be read in from a text file using the <i>src</i> attribute or from
29between the enclosing SQL tags.</p>
30
31<p>Multiple statements can be provided, separated by semicolons (or the
32defined <i>delimiter</i>). Individual lines within the statements can be
33commented 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
36turned on or off whilst executing the statements. If auto-commit is turned
37on each statement will be executed and committed. If it is turned off the
38statements will all be executed as one transaction.</p>
39
40<p>The <i>onerror</i> attribute specifies how to proceed when an error occurs
41during the execution of one of the statements.
42The possible values are: <b>continue</b> execution, only show the error;
43<b>stop</b> execution and commit transaction;
44and <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 &quot;;&quot;</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 &quot;false&quot;</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 &quot;false&quot;</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 &quot;true&quot;</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 &quot;true&quot;</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 &quot;abort&quot;</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>&lt;transaction&gt;</code>
196elements to specify multiple blocks of commands to the executed
197executed in the same connection but different transactions. This
198is particularly useful when there are multiple files to execute
199on 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>&lt;transaction&gt;</code> element supports any <a
213href="../CoreTypes/resources.html">resource</a> or single element
214resource collection as nested element to specify the resource
215containing the SQL statements.</p>
216
217<h4>any <a href="../CoreTypes/resources.html">resource</a> or resource
218collection</h4>
219
220<p>You can specify multiple sources via nested resource collection
221elements. Each resource of the collection will be run in a
222transaction of its own. Prior to Ant 1.7 only filesets were
223supported. Use a sort resource collection to get a predictable order
224of transactions. </p>
225
226<h4>classpath</h4>
227<p><code>Sql</code>'s <em>classpath</em> attribute is a <a
228href="../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>&lt;sql
233 driver=&quot;org.database.jdbcDriver&quot;
234 url=&quot;jdbc:database-url&quot;
235 userid=&quot;sa&quot;
236 password=&quot;pass&quot;
237 src=&quot;data.sql&quot;
238/&gt;
239</pre></blockquote>
240
241<p>Connects to the database given in <i>url</i> as the sa user using the
242org.database.jdbcDriver and executes the SQL statements contained within
243the file data.sql</p>
244
245<blockquote><pre>&lt;sql
246 driver=&quot;org.database.jdbcDriver&quot;
247 url=&quot;jdbc:database-url&quot;
248 userid=&quot;sa&quot;
249 password=&quot;pass&quot;
250 &gt;
251insert
252into table some_table
253values(1,2,3,4);
254
255truncate table some_other_table;
256&lt;/sql&gt;
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>&lt;![CDATA[</code> ... <code>]]&gt;</code> sections so you don't
266need to escape <code>&lt;</code>, <code>&gt;</code> <code>&amp;</code>
267or other special characters. For example:</p>
268
269<blockquote><pre>&lt;sql
270 driver=&quot;org.database.jdbcDriver&quot;
271 url=&quot;jdbc:database-url&quot;
272 userid=&quot;sa&quot;
273 password=&quot;pass&quot;
274 &gt;&lt;![CDATA[
275
276update some_table set column1 = column1 + 1 where column2 &lt; 42;
277
278]]&gt;&lt;/sql&gt;
279</pre></blockquote>
280
281The following command turns property expansion in nested text on (it is off purely for backwards
282compatibility), then creates a new user in the HSQLDB database using Ant properties.
283
284<blockquote><pre>&lt;sql
285 driver="org.hsqldb.jdbcDriver";
286 url="jdbc:hsqldb:file:${database.dir}"
287 userid="sa"
288 password=""
289 expandProperties="true"
290 &gt;
291 &lt;transaction&gt;
292 CREATE USER ${newuser} PASSWORD ${newpassword}
293 &lt;/transaction&gt;
294&lt;/sql&gt;
295</pre></blockquote>
296
297
298<p>The following connects to the database given in url as the sa user using
299the org.database.jdbcDriver and executes the SQL statements contained within
300the files data1.sql, data2.sql and data3.sql and then executes the truncate
301operation on <i>some_other_table</i>.</p>
302
303<blockquote><pre>&lt;sql
304 driver=&quot;org.database.jdbcDriver&quot;
305 url=&quot;jdbc:database-url&quot;
306 userid=&quot;sa&quot;
307 password=&quot;pass&quot; &gt;
308 &lt;transaction src=&quot;data1.sql&quot;/&gt;
309 &lt;transaction src=&quot;data2.sql&quot;/&gt;
310 &lt;transaction src=&quot;data3.sql&quot;/&gt;
311 &lt;transaction&gt;
312 truncate table some_other_table;
313 &lt;/transaction&gt;
314&lt;/sql&gt;
315</pre></blockquote>
316
317<p>The following example does the same as (and may execute additional
318SQL files if there are more files matching the pattern
319<code>data*.sql</code>) but doesn't guarantee that data1.sql will be
320run before <code>data2.sql</code>.</p>
321
322<blockquote><pre>&lt;sql
323 driver=&quot;org.database.jdbcDriver&quot;
324 url=&quot;jdbc:database-url&quot;
325 userid=&quot;sa&quot;
326 password=&quot;pass&quot;&gt;
327 &lt;path&gt;
328 &lt;fileset dir=&quot;.&quot;&gt;
329 &lt;include name=&quot;data*.sql&quot;/&gt;
330 &lt;/fileset&gt;
331 &lt;path&gt;
332 &lt;transaction&gt;
333 truncate table some_other_table;
334 &lt;/transaction&gt;
335&lt;/sql&gt;
336</pre></blockquote>
337
338<p>The following connects to the database given in url as the sa user using the
339org.database.jdbcDriver and executes the SQL statements contained within the
340file data.sql, with output piped to outputfile.txt, searching /some/jdbc.jar
341as well as the system classpath for the driver class.</p>
342
343<blockquote><pre>&lt;sql
344 driver=&quot;org.database.jdbcDriver&quot;
345 url=&quot;jdbc:database-url&quot;
346 userid=&quot;sa&quot;
347 password=&quot;pass&quot;
348 src=&quot;data.sql&quot;
349 print=&quot;yes&quot;
350 output=&quot;outputfile.txt&quot;
351 &gt;
352&lt;classpath&gt;
353 &lt;pathelement location=&quot;/some/jdbc.jar&quot;/&gt;
354&lt;/classpath&gt;
355&lt;/sql&gt;
356</pre></blockquote>
357
358<p>The following will only execute if the RDBMS is &quot;oracle&quot; and the version
359starts with &quot;8.1.&quot;</p>
360
361<blockquote><pre>&lt;sql
362 driver=&quot;org.database.jdbcDriver&quot;
363 url=&quot;jdbc:database-url&quot;
364 userid=&quot;sa&quot;
365 password=&quot;pass&quot;
366 src=&quot;data.sql&quot;
367 rdbms=&quot;oracle&quot;
368 version=&quot;8.1.&quot;
369 &gt;
370insert
371into table some_table
372values(1,2,3,4);
373
374truncate table some_other_table;
375&lt;/sql&gt;
376</pre></blockquote>
377
378
379</body>
380</html>
Note: See TracBrowser for help on using the repository browser.