1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
|
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<link rel="stylesheet" type="text/css" href="../stylesheets/style.css">
<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, log the error but don't fail the task
and <b>abort</b> execution and transaction and fail task.</p>
<p>
<b>Proxies</b>. Some JDBC drivers (including the Oracle thin driver),
use the JVM's proxy settings to route their JDBC operations to the database.
Since Apache Ant1.7, Ant running on Java1.5 or later defaults to
<a href="../proxy.html">using
the proxy settings of the operating system</a>.
Accordingly, the OS proxy settings need to be valid, or Ant's proxy
support disabled with <code>-noproxy</code> option.
</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 valign="top">outputencoding</td>
<td valign="top">The encoding of the files holding
results. <em>since 1.9.4</em</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">showtrailers</td>
<td width="78%" valign="top">Print trailer for number of rows affected (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)
<b>Since Ant 1.8</b> can specify any Resource that supports output (see
<a href="../develop.html#set-magic">note</a>).
</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, ignored if <i>output</i> does not
specify a filesystem destination.</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>
<tr>
<td width="12%" valign="top">delimitertype</td>
<td width="78%" valign="top">Control whether the delimiter will only be recognized on a line by itself.<br>
Can be "normal" -anywhere on the line, or "row", meaning it must be on a line by itself</td>
<td width="10%" valign="top">No (default:normal)</td>
</tr>
<tr>
<td width="12%" valign="top">keepformat</td>
<td width="78%" valign="top">Control whether the format of the sql will be preserved.<br>
Useful when loading packages and procedures.
<td width="10%" valign="top">No (default=false)</td>
</tr>
<tr>
<td width="12%" valign="top">escapeprocessing</td>
<td width="78%" valign="top">Control whether the Java statement
object will perform escape substitution.<br>
See <a
href="http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setEscapeProcessing%28boolean%29">Statement's
API docs</a> for details. <em>Since Ant 1.6</em>.
<td width="10%" valign="top">No (default=true)</td>
</tr>
<tr>
<td width="12%" valign="top">expandproperties</td>
<td width="78%" valign="top">Set to true to turn on property expansion in
nested SQL, inline in the task or nested transactions. <em>Since Ant 1.7</em>.
<td width="10%" valign="top">No (default=true)</td>
</tr>
<tr>
<td width="12%" valign="top">rawblobs</td>
<td width="78%" valign="top">If true, will write raw streams rather than hex encoding when
printing BLOB results. <em>Since Ant 1.7.1</em>.</td>
<td width="10%" valign="top">No, default <em>false</em></td>
</tr>
<tr>
<td width="12%" valign="top">failOnConnectionError</td>
<td width="78%" valign="top">If false, will only print a warning
message and not execute any statement if the task fails to connect
to the database. <em>Since Ant 1.8.0</em>.</td>
<td width="10%" valign="top">No, default <em>true</em></td>
</tr>
<tr>
<td width="12%" valign="top">strictDelimiterMatching</td>
<td width="78%" valign="top">If false, delimiters will be searched
for in a case-insensitive manner (i.e. delimiter="go" matches "GO")
and surrounding whitespace will be ignored (delimiter="go" matches
"GO "). <em>Since Ant 1.8.0</em>.</td>
<td width="10%" valign="top">No, default <em>true</em></td>
</tr>
<tr>
<td width="12%" valign="top">showWarnings</td>
<td width="78%" valign="top">If true, SQLWarnings will be logged at
the WARN level. <em>Since Ant 1.8.0</em>.<br/>
<b>Note:</b> even if the attribute is set to false, warnings that
apply to the connection will be logged at the verbose level.</td>
<td width="10%" valign="top">No, default <em>false</em></td>
</tr>
<tr>
<td width="12%" valign="top">treatWarningsAsErrors</td>
<td width="78%" valign="top">If true, SQLWarnings will be treated
like errors - and the logic selected via the onError attribute
applies.
<em>Since Ant 1.8.0</em>.</td>
<td width="10%" valign="top">No, default <em>false</em></td>
</tr>
<tr>
<td width="12%" valign="top">csvColumnSeparator</td>
<td width="78%" valign="top">The column separator used when printing
the results.
<em>Since Ant 1.8.0</em>.</td>
<td width="10%" valign="top">No, default <em>','</em></td>
</tr>
<tr>
<td width="12%" valign="top">csvQuoteCharacter</td>
<td width="78%" valign="top">The character used to quote column
values.<br/>
If set, columns that contain either the column separator or the
quote character itself will be surrounded by the quote character.
The quote character itself will be doubled if it appears inside of
the column's value.<br/>
<b>Note:</b> BLOB values will never be quoted.
<em>Since Ant 1.8.0</em>.</td>
<td width="10%" valign="top">No, default is not set (i.e. no quoting
ever occurs)</td>
</tr>
<tr>
<td valign="top">errorproperty</td>
<td valign="top">The name of a property to set in the event of an
error. <em>Since Ant 1.8.0</em></td>
<td align="center" valign="top">No</td>
</tr>
<tr>
<td valign="top">warningproperty</td>
<td valign="top">The name of a property to set in the event of an
warning. <em>Since Ant 1.8.0</em></td>
<td align="center" valign="top">No</td>
</tr>
<tr>
<td valign="top">rowcountproperty</td>
<td valign="top">The name of a property to set to the number of rows
updated by the first statement/transaction that actually returned
a row count. <em>Since Ant 1.8.0</em></td>
<td align="center" valign="top">No</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>
<p>The <code><transaction></code> element supports any <a
href="../Types/resources.html">resource</a> or single element
resource collection as nested element to specify the resource
containing the SQL statements.</p>
<h4>any <a href="../Types/resources.html">resource</a> or resource
collection</h4>
<p>You can specify multiple sources via nested resource collection
elements. Each resource of the collection will be run in a
transaction of its own. Prior to Ant 1.7 only filesets were
supported. Use a sort resource collection to get a predictable order
of transactions. </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>
<h4>connectionProperty</h4>
<p><em>Since Ant 1.8.0</em></p>
<p>Use nested <code><connectionProperty></code> elements to
specify additional JDBC properties that need to be set when
connecting to the database.</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">name</td>
<td valign="top">Name of the property</td>
<td valign="top" align="center">Yes</td>
</tr>
<tr>
<td valign="top">value</td>
<td valign="top">Value of the property</td>
<td valign="top" align="center">Yes</td>
</tr>
</table>
<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"
src="data.sql">
<connectionProperty name="internal_logon" value="SYSDBA">
</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. Also sets the
property <i>internal_logon</i> to the value <i>SYSDBA</i>.</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. Ant Properties
in the nested text will not be expanded.</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>
The following command turns property expansion in nested text on (it is off purely for backwards
compatibility), then creates a new user in the HSQLDB database using Ant properties.
<blockquote><pre><sql
driver="org.hsqldb.jdbcDriver";
url="jdbc:hsqldb:file:${database.dir}"
userid="sa"
password=""
expandProperties="true"
>
<transaction>
CREATE USER ${newuser} PASSWORD ${newpassword}
</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 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">
<path>
<fileset dir=".">
<include name="data*.sql"/>
</fileset>
</path>
<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>
</body>
</html>
|
