source: other-projects/trunk/gs3-release-maker/apache-ant-1.6.5/src/main/org/apache/tools/ant/taskdefs/SQLExec.java@ 14627

Last change on this file since 14627 was 14627, checked in by oranfry, 17 years ago

initial import of the gs3-release-maker
File size: 20.6 KB
Line 
1/*
2 * Copyright 2000-2005 The Apache Software Foundation
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 * http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 *
16 */
17
18package org.apache.tools.ant.taskdefs;
19
20import org.apache.tools.ant.BuildException;
21import org.apache.tools.ant.DirectoryScanner;
22import org.apache.tools.ant.Project;
23import org.apache.tools.ant.types.EnumeratedAttribute;
24import org.apache.tools.ant.types.FileSet;
25
26import java.io.File;
27import java.io.PrintStream;
28import java.io.BufferedOutputStream;
29import java.io.FileOutputStream;
30import java.io.IOException;
31import java.io.Reader;
32import java.io.BufferedReader;
33import java.io.StringReader;
34import java.io.FileReader;
35import java.io.InputStreamReader;
36import java.io.FileInputStream;
37import java.util.Enumeration;
38import java.util.StringTokenizer;
39import java.util.Vector;
40
41import java.sql.Connection;
42import java.sql.Statement;
43import java.sql.SQLException;
44import java.sql.SQLWarning;
45import java.sql.ResultSet;
46import java.sql.ResultSetMetaData;
47
48/**
49 * Executes a series of SQL statements on a database using JDBC.
50 *
51 * <p>Statements can
52 * either be read in from a text file using the <i>src</i> attribute or from
53 * between the enclosing SQL tags.</p>
54 *
55 * <p>Multiple statements can be provided, separated by semicolons (or the
56 * defined <i>delimiter</i>). Individual lines within the statements can be
57 * commented using either --, // or REM at the start of the line.</p>
58 *
59 * <p>The <i>autocommit</i> attribute specifies whether auto-commit should be
60 * turned on or off whilst executing the statements. If auto-commit is turned
61 * on each statement will be executed and committed. If it is turned off the
62 * statements will all be executed as one transaction.</p>
63 *
64 * <p>The <i>onerror</i> attribute specifies how to proceed when an error occurs
65 * during the execution of one of the statements.
66 * The possible values are: <b>continue</b> execution, only show the error;
67 * <b>stop</b> execution and commit transaction;
68 * and <b>abort</b> execution and transaction and fail task.</p>
69 *
70 * @since Ant 1.2
71 *
72 * @ant.task name="sql" category="database"
73 */
74public class SQLExec extends JDBCTask {
75
76 /**
77 * delimiters we support, "normal" and "row"
78 */
79 public static class DelimiterType extends EnumeratedAttribute {
80 public static final String NORMAL = "normal";
81 public static final String ROW = "row";
82 public String[] getValues() {
83 return new String[] {NORMAL, ROW};
84 }
85 }
86
87
88
89 private int goodSql = 0;
90
91 private int totalSql = 0;
92
93 /**
94 * Database connection
95 */
96 private Connection conn = null;
97
98 /**
99 * files to load
100 */
101 private Vector filesets = new Vector();
102
103 /**
104 * SQL statement
105 */
106 private Statement statement = null;
107
108 /**
109 * SQL input file
110 */
111 private File srcFile = null;
112
113 /**
114 * SQL input command
115 */
116 private String sqlCommand = "";
117
118 /**
119 * SQL transactions to perform
120 */
121 private Vector transactions = new Vector();
122
123 /**
124 * SQL Statement delimiter
125 */
126 private String delimiter = ";";
127
128 /**
129 * The delimiter type indicating whether the delimiter will
130 * only be recognized on a line by itself
131 */
132 private String delimiterType = DelimiterType.NORMAL;
133
134 /**
135 * Print SQL results.
136 */
137 private boolean print = false;
138
139 /**
140 * Print header columns.
141 */
142 private boolean showheaders = true;
143
144 /**
145 * Results Output file.
146 */
147 private File output = null;
148
149
150 /**
151 * Action to perform if an error is found
152 **/
153 private String onError = "abort";
154
155 /**
156 * Encoding to use when reading SQL statements from a file
157 */
158 private String encoding = null;
159
160 /**
161 * Append to an existing file or overwrite it?
162 */
163 private boolean append = false;
164
165 /**
166 * Keep the format of a sql block?
167 */
168 private boolean keepformat = false;
169
170 /**
171 * Argument to Statement.setEscapeProcessing
172 *
173 * @since Ant 1.6
174 */
175 private boolean escapeProcessing = true;
176
177 /**
178 * Set the name of the SQL file to be run.
179 * Required unless statements are enclosed in the build file
180 */
181 public void setSrc(File srcFile) {
182 this.srcFile = srcFile;
183 }
184
185 /**
186 * Set an inline SQL command to execute.
187 * NB: Properties are not expanded in this text.
188 */
189 public void addText(String sql) {
190 this.sqlCommand += sql;
191 }
192
193 /**
194 * Adds a set of files (nested fileset attribute).
195 */
196 public void addFileset(FileSet set) {
197 filesets.addElement(set);
198 }
199
200
201 /**
202 * Add a SQL transaction to execute
203 */
204 public Transaction createTransaction() {
205 Transaction t = new Transaction();
206 transactions.addElement(t);
207 return t;
208 }
209
210 /**
211 * Set the file encoding to use on the SQL files read in
212 *
213 * @param encoding the encoding to use on the files
214 */
215 public void setEncoding(String encoding) {
216 this.encoding = encoding;
217 }
218
219 /**
220 * Set the delimiter that separates SQL statements. Defaults to &quot;;&quot;;
221 * optional
222 *
223 * <p>For example, set this to "go" and delimitertype to "ROW" for
224 * Sybase ASE or MS SQL Server.</p>
225 */
226 public void setDelimiter(String delimiter) {
227 this.delimiter = delimiter;
228 }
229
230 /**
231 * Set the delimiter type: "normal" or "row" (default "normal").
232 *
233 * <p>The delimiter type takes two values - normal and row. Normal
234 * means that any occurrence of the delimiter terminate the SQL
235 * command whereas with row, only a line containing just the
236 * delimiter is recognized as the end of the command.</p>
237 */
238 public void setDelimiterType(DelimiterType delimiterType) {
239 this.delimiterType = delimiterType.getValue();
240 }
241
242 /**
243 * Print result sets from the statements;
244 * optional, default false
245 */
246 public void setPrint(boolean print) {
247 this.print = print;
248 }
249
250 /**
251 * Print headers for result sets from the
252 * statements; optional, default true.
253 */
254 public void setShowheaders(boolean showheaders) {
255 this.showheaders = showheaders;
256 }
257
258 /**
259 * Set the output file;
260 * optional, defaults to the Ant log.
261 */
262 public void setOutput(File output) {
263 this.output = output;
264 }
265
266 /**
267 * whether output should be appended to or overwrite
268 * an existing file. Defaults to false.
269 *
270 * @since Ant 1.5
271 */
272 public void setAppend(boolean append) {
273 this.append = append;
274 }
275
276
277 /**
278 * Action to perform when statement fails: continue, stop, or abort
279 * optional; default &quot;abort&quot;
280 */
281 public void setOnerror(OnError action) {
282 this.onError = action.getValue();
283 }
284
285 /**
286 * whether or not format should be preserved.
287 * Defaults to false.
288 *
289 * @param keepformat The keepformat to set
290 */
291 public void setKeepformat(boolean keepformat) {
292 this.keepformat = keepformat;
293 }
294
295 /**
296 * Set escape processing for statements.
297 *
298 * @since Ant 1.6
299 */
300 public void setEscapeProcessing(boolean enable) {
301 escapeProcessing = enable;
302 }
303
304 /**
305 * Load the sql file and then execute it
306 */
307 public void execute() throws BuildException {
308 Vector savedTransaction = (Vector) transactions.clone();
309 String savedSqlCommand = sqlCommand;
310
311 sqlCommand = sqlCommand.trim();
312
313 try {
314 if (srcFile == null && sqlCommand.length() == 0
315 && filesets.isEmpty()) {
316 if (transactions.size() == 0) {
317 throw new BuildException("Source file or fileset, "
318 + "transactions or sql statement "
319 + "must be set!", getLocation());
320 }
321 }
322
323 if (srcFile != null && !srcFile.exists()) {
324 throw new BuildException("Source file does not exist!", getLocation());
325 }
326
327 // deal with the filesets
328 for (int i = 0; i < filesets.size(); i++) {
329 FileSet fs = (FileSet) filesets.elementAt(i);
330 DirectoryScanner ds = fs.getDirectoryScanner(getProject());
331 File srcDir = fs.getDir(getProject());
332
333 String[] srcFiles = ds.getIncludedFiles();
334
335 // Make a transaction for each file
336 for (int j = 0; j < srcFiles.length; j++) {
337 Transaction t = createTransaction();
338 t.setSrc(new File(srcDir, srcFiles[j]));
339 }
340 }
341
342 // Make a transaction group for the outer command
343 Transaction t = createTransaction();
344 t.setSrc(srcFile);
345 t.addText(sqlCommand);
346 conn = getConnection();
347 if (!isValidRdbms(conn)) {
348 return;
349 }
350 try {
351 statement = conn.createStatement();
352 statement.setEscapeProcessing(escapeProcessing);
353
354 PrintStream out = System.out;
355 try {
356 if (output != null) {
357 log("Opening PrintStream to output file " + output,
358 Project.MSG_VERBOSE);
359 out = new PrintStream(
360 new BufferedOutputStream(
361 new FileOutputStream(output
362 .getAbsolutePath(),
363 append)));
364 }
365
366 // Process all transactions
367 for (Enumeration e = transactions.elements();
368 e.hasMoreElements();) {
369
370 ((Transaction) e.nextElement()).runTransaction(out);
371 if (!isAutocommit()) {
372 log("Committing transaction", Project.MSG_VERBOSE);
373 conn.commit();
374 }
375 }
376 } finally {
377 if (out != null && out != System.out) {
378 out.close();
379 }
380 }
381 } catch (IOException e) {
382 if (!isAutocommit() && conn != null && onError.equals("abort")) {
383 try {
384 conn.rollback();
385 } catch (SQLException ex) {
386 // ignore
387 }
388 }
389 throw new BuildException(e, getLocation());
390 } catch (SQLException e) {
391 if (!isAutocommit() && conn != null && onError.equals("abort")) {
392 try {
393 conn.rollback();
394 } catch (SQLException ex) {
395 // ignore
396 }
397 }
398 throw new BuildException(e, getLocation());
399 } finally {
400 try {
401 if (statement != null) {
402 statement.close();
403 }
404 if (conn != null) {
405 conn.close();
406 }
407 } catch (SQLException ex) {
408 // ignore
409 }
410 }
411
412 log(goodSql + " of " + totalSql
413 + " SQL statements executed successfully");
414 } finally {
415 transactions = savedTransaction;
416 sqlCommand = savedSqlCommand;
417 }
418 }
419
420 /**
421 * read in lines and execute them
422 */
423 protected void runStatements(Reader reader, PrintStream out)
424 throws SQLException, IOException {
425 StringBuffer sql = new StringBuffer();
426 String line = "";
427
428 BufferedReader in = new BufferedReader(reader);
429
430 while ((line = in.readLine()) != null) {
431 if (!keepformat) {
432 line = line.trim();
433 }
434 line = getProject().replaceProperties(line);
435 if (!keepformat) {
436 if (line.startsWith("//")) {
437 continue;
438 }
439 if (line.startsWith("--")) {
440 continue;
441 }
442 StringTokenizer st = new StringTokenizer(line);
443 if (st.hasMoreTokens()) {
444 String token = st.nextToken();
445 if ("REM".equalsIgnoreCase(token)) {
446 continue;
447 }
448 }
449 }
450
451 if (!keepformat) {
452 sql.append(" " + line);
453 } else {
454 sql.append("\n" + line);
455 }
456
457 // SQL defines "--" as a comment to EOL
458 // and in Oracle it may contain a hint
459 // so we cannot just remove it, instead we must end it
460 if (!keepformat) {
461 if (line.indexOf("--") >= 0) {
462 sql.append("\n");
463 }
464 }
465 if ((delimiterType.equals(DelimiterType.NORMAL)
466 && sql.toString().endsWith(delimiter))
467 ||
468 (delimiterType.equals(DelimiterType.ROW)
469 && line.equals(delimiter))) {
470 execSQL(sql.substring(0, sql.length() - delimiter.length()),
471 out);
472 sql.replace(0, sql.length(), "");
473 }
474 }
475 // Catch any statements not followed by ;
476 if (!sql.equals("")) {
477 execSQL(sql.toString(), out);
478 }
479 }
480
481
482 /**
483 * Exec the sql statement.
484 */
485 protected void execSQL(String sql, PrintStream out) throws SQLException {
486 // Check and ignore empty statements
487 if ("".equals(sql.trim())) {
488 return;
489 }
490
491 ResultSet resultSet = null;
492 try {
493 totalSql++;
494 log("SQL: " + sql, Project.MSG_VERBOSE);
495
496 boolean ret;
497 int updateCount = 0, updateCountTotal = 0;
498
499 ret = statement.execute(sql);
500 updateCount = statement.getUpdateCount();
501 resultSet = statement.getResultSet();
502 do {
503 if (!ret) {
504 if (updateCount != -1) {
505 updateCountTotal += updateCount;
506 }
507 } else {
508 if (print) {
509 printResults(resultSet, out);
510 }
511 }
512 ret = statement.getMoreResults();
513 if (ret) {
514 updateCount = statement.getUpdateCount();
515 resultSet = statement.getResultSet();
516 }
517 } while (ret);
518
519 log(updateCountTotal + " rows affected",
520 Project.MSG_VERBOSE);
521
522 if (print) {
523 StringBuffer line = new StringBuffer();
524 line.append(updateCountTotal + " rows affected");
525 out.println(line);
526 }
527
528 SQLWarning warning = conn.getWarnings();
529 while (warning != null) {
530 log(warning + " sql warning", Project.MSG_VERBOSE);
531 warning = warning.getNextWarning();
532 }
533 conn.clearWarnings();
534 goodSql++;
535 } catch (SQLException e) {
536 log("Failed to execute: " + sql, Project.MSG_ERR);
537 if (!onError.equals("continue")) {
538 throw e;
539 }
540 log(e.toString(), Project.MSG_ERR);
541 } finally {
542 if (resultSet != null) {
543 resultSet.close();
544 }
545 }
546 }
547
548 /**
549 * print any results in the statement
550 * @deprecated use {@link #printResults(java.sql.ResultSet, java.io.PrintStream)
551 * the two arg version} instead.
552 * @param out the place to print results
553 * @throws SQLException on SQL problems.
554 */
555 protected void printResults(PrintStream out) throws SQLException {
556 ResultSet rs = null;
557 rs = statement.getResultSet();
558 try {
559 printResults(rs, out);
560 } finally {
561 if (rs != null) {
562 rs.close();
563 }
564 }
565 }
566
567 /**
568 * print any results in the result set.
569 * @param rs the resultset to print information about
570 * @param out the place to print results
571 * @throws SQLException on SQL problems.
572 * @since Ant 1.6.3
573 */
574 protected void printResults(ResultSet rs, PrintStream out) throws SQLException {
575 if (rs != null) {
576 log("Processing new result set.", Project.MSG_VERBOSE);
577 ResultSetMetaData md = rs.getMetaData();
578 int columnCount = md.getColumnCount();
579 StringBuffer line = new StringBuffer();
580 if (showheaders) {
581 for (int col = 1; col < columnCount; col++) {
582 line.append(md.getColumnName(col));
583 line.append(",");
584 }
585 line.append(md.getColumnName(columnCount));
586 out.println(line);
587 line = new StringBuffer();
588 }
589 while (rs.next()) {
590 boolean first = true;
591 for (int col = 1; col <= columnCount; col++) {
592 String columnValue = rs.getString(col);
593 if (columnValue != null) {
594 columnValue = columnValue.trim();
595 }
596
597 if (first) {
598 first = false;
599 } else {
600 line.append(",");
601 }
602 line.append(columnValue);
603 }
604 out.println(line);
605 line = new StringBuffer();
606 }
607 }
608 out.println();
609 }
610
611 /**
612 * The action a task should perform on an error,
613 * one of "continue", "stop" and "abort"
614 */
615 public static class OnError extends EnumeratedAttribute {
616 public String[] getValues() {
617 return new String[] {"continue", "stop", "abort"};
618 }
619 }
620
621 /**
622 * Contains the definition of a new transaction element.
623 * Transactions allow several files or blocks of statements
624 * to be executed using the same JDBC connection and commit
625 * operation in between.
626 */
627 public class Transaction {
628 private File tSrcFile = null;
629 private String tSqlCommand = "";
630
631 /**
632 *
633 */
634 public void setSrc(File src) {
635 this.tSrcFile = src;
636 }
637
638 /**
639 *
640 */
641 public void addText(String sql) {
642 this.tSqlCommand += sql;
643 }
644
645 /**
646 *
647 */
648 private void runTransaction(PrintStream out)
649 throws IOException, SQLException {
650 if (tSqlCommand.length() != 0) {
651 log("Executing commands", Project.MSG_INFO);
652 runStatements(new StringReader(tSqlCommand), out);
653 }
654
655 if (tSrcFile != null) {
656 log("Executing file: " + tSrcFile.getAbsolutePath(),
657 Project.MSG_INFO);
658 Reader reader =
659 (encoding == null) ? new FileReader(tSrcFile)
660 : new InputStreamReader(
661 new FileInputStream(tSrcFile),
662 encoding);
663 try {
664 runStatements(reader, out);
665 } finally {
666 reader.close();
667 }
668 }
669 }
670 }
671
672}
Note: See TracBrowser for help on using the repository browser.