FileDocCategorySizeDatePackage
SQLExec.javaAPI DocApache Ant 1.7024680Wed Dec 13 06:16:20 GMT 2006org.apache.tools.ant.taskdefs

SQLExec

public class SQLExec extends JDBCTask
Executes a series of SQL statements on a database using JDBC.

Statements can either be read in from a text file using the src attribute or from between the enclosing SQL tags.

Multiple statements can be provided, separated by semicolons (or the defined delimiter). Individual lines within the statements can be commented using either --, // or REM at the start of the line.

The autocommit 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.

The onerror attribute specifies how to proceed when an error occurs during the execution of one of the statements. The possible values are: continue execution, only show the error; stop execution and commit transaction; and abort execution and transaction and fail task.

since
Ant 1.2
ant.task
name="sql" category="database"

Fields Summary
private int
goodSql
private int
totalSql
private Connection
conn
Database connection
private org.apache.tools.ant.types.resources.Union
resources
files to load
private Statement
statement
SQL statement
private File
srcFile
SQL input file
private String
sqlCommand
SQL input command
private Vector
transactions
SQL transactions to perform
private String
delimiter
SQL Statement delimiter
private String
delimiterType
The delimiter type indicating whether the delimiter will only be recognized on a line by itself
private boolean
print
Print SQL results.
private boolean
showheaders
Print header columns.
private boolean
showtrailers
Print SQL stats (rows affected)
private File
output
Results Output file.
private String
onError
Action to perform if an error is found
private String
encoding
Encoding to use when reading SQL statements from a file
private boolean
append
Append to an existing file or overwrite it?
private boolean
keepformat
Keep the format of a sql block?
private boolean
escapeProcessing
Argument to Statement.setEscapeProcessing
private boolean
expandProperties
should properties be expanded in text? false for backwards compatibility
Constructors Summary
Methods Summary
public voidadd(org.apache.tools.ant.types.ResourceCollection rc)
Adds a collection of resources (nested element).

param
rc a collection of resources containing SQL commands, each resource is run in a separate transaction.
since
Ant 1.7

        resources.add(rc);
public voidaddFileset(org.apache.tools.ant.types.FileSet set)
Adds a set of files (nested fileset attribute).

param
set a set of files contains SQL commands, each File is run in a separate transaction.

        add(set);
public voidaddText(java.lang.String sql)
Set an inline SQL command to execute. NB: Properties are not expanded in this text unless {@link #expandProperties} is set.

param
sql an inline string containing the SQL command.

        //there is no need to expand properties here as that happens when Transaction.addText is
        //called; to do so here would be an error.
        this.sqlCommand += sql;
private voidcloseQuietly()

        if (!isAutocommit() && conn != null && onError.equals("abort")) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                // ignore
            }
        }
public org.apache.tools.ant.taskdefs.SQLExec$TransactioncreateTransaction()
Add a SQL transaction to execute

return
a Transaction to be configured.

        Transaction t = new Transaction();
        transactions.addElement(t);
        return t;
protected voidexecSQL(java.lang.String sql, java.io.PrintStream out)
Exec the sql statement.

param
sql the SQL statement to execute
param
out the place to put output
throws
SQLException on SQL problems

        // Check and ignore empty statements
        if ("".equals(sql.trim())) {
            return;
        }

        ResultSet resultSet = null;
        try {
            totalSql++;
            log("SQL: " + sql, Project.MSG_VERBOSE);

            boolean ret;
            int updateCount = 0, updateCountTotal = 0;

            ret = statement.execute(sql);
            updateCount = statement.getUpdateCount();
            resultSet = statement.getResultSet();
            do {
                if (!ret) {
                    if (updateCount != -1) {
                        updateCountTotal += updateCount;
                    }
                } else {
                    if (print) {
                        printResults(resultSet, out);
                    }
                }
                ret = statement.getMoreResults();
                if (ret) {
                    updateCount = statement.getUpdateCount();
                    resultSet = statement.getResultSet();
                }
            } while (ret);

            log(updateCountTotal + " rows affected",
                Project.MSG_VERBOSE);

            if (print && showtrailers) {
                out.println(updateCountTotal + " rows affected");
            }

            SQLWarning warning = conn.getWarnings();
            while (warning != null) {
                log(warning + " sql warning", Project.MSG_VERBOSE);
                warning = warning.getNextWarning();
            }
            conn.clearWarnings();
            goodSql++;
        } catch (SQLException e) {
            log("Failed to execute: " + sql, Project.MSG_ERR);
            if (!onError.equals("continue")) {
                throw e;
            }
            log(e.toString(), Project.MSG_ERR);
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
        }
public voidexecute()
Load the sql file and then execute it

throws
BuildException on error.

        Vector savedTransaction = (Vector) transactions.clone();
        String savedSqlCommand = sqlCommand;

        sqlCommand = sqlCommand.trim();

        try {
            if (srcFile == null && sqlCommand.length() == 0
                && resources.size() == 0) {
                if (transactions.size() == 0) {
                    throw new BuildException("Source file or resource "
                                             + "collection, "
                                             + "transactions or sql statement "
                                             + "must be set!", getLocation());
                }
            }

            if (srcFile != null && !srcFile.exists()) {
                throw new BuildException("Source file does not exist!", getLocation());
            }

            // deal with the resources
            Iterator iter = resources.iterator();
            while (iter.hasNext()) {
                Resource r = (Resource) iter.next();
                // Make a transaction for each resource
                Transaction t = createTransaction();
                t.setSrcResource(r);
            }

            // Make a transaction group for the outer command
            Transaction t = createTransaction();
            t.setSrc(srcFile);
            t.addText(sqlCommand);
            conn = getConnection();
            if (!isValidRdbms(conn)) {
                return;
            }
            try {
                statement = conn.createStatement();
                statement.setEscapeProcessing(escapeProcessing);

                PrintStream out = System.out;
                try {
                    if (output != null) {
                        log("Opening PrintStream to output file " + output,
                            Project.MSG_VERBOSE);
                        out = new PrintStream(
                                  new BufferedOutputStream(
                                      new FileOutputStream(output
                                                           .getAbsolutePath(),
                                                           append)));
                    }

                    // Process all transactions
                    for (Enumeration e = transactions.elements();
                         e.hasMoreElements();) {

                        ((Transaction) e.nextElement()).runTransaction(out);
                        if (!isAutocommit()) {
                            log("Committing transaction", Project.MSG_VERBOSE);
                            conn.commit();
                        }
                    }
                } finally {
                    if (out != null && out != System.out) {
                        out.close();
                    }
                }
            } catch (IOException e) {
                closeQuietly();
                throw new BuildException(e, getLocation());
            } catch (SQLException e) {
                closeQuietly();
                throw new BuildException(e, getLocation());
            } finally {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException ex) {
                    // ignore
                }
            }

            log(goodSql + " of " + totalSql
                + " SQL statements executed successfully");
        } finally {
            transactions = savedTransaction;
            sqlCommand = savedSqlCommand;
        }
public booleangetExpandProperties()
is property expansion inside inline text enabled?

return
true if properties are to be expanded.
since
Ant 1.7

        return expandProperties;
protected voidprintResults(java.io.PrintStream out)
print any results in the statement

deprecated
since 1.6.x. Use {@link #printResults(java.sql.ResultSet, java.io.PrintStream) the two arg version} instead.
param
out the place to print results
throws
SQLException on SQL problems.

        ResultSet rs = statement.getResultSet();
        try {
            printResults(rs, out);
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
protected voidprintResults(java.sql.ResultSet rs, java.io.PrintStream out)
print any results in the result set.

param
rs the resultset to print information about
param
out the place to print results
throws
SQLException on SQL problems.
since
Ant 1.6.3

        if (rs != null) {
            log("Processing new result set.", Project.MSG_VERBOSE);
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            StringBuffer line = new StringBuffer();
            if (showheaders) {
                for (int col = 1; col < columnCount; col++) {
                     line.append(md.getColumnName(col));
                     line.append(",");
                }
                line.append(md.getColumnName(columnCount));
                out.println(line);
                line = new StringBuffer();
            }
            while (rs.next()) {
                boolean first = true;
                for (int col = 1; col <= columnCount; col++) {
                    String columnValue = rs.getString(col);
                    if (columnValue != null) {
                        columnValue = columnValue.trim();
                    }

                    if (first) {
                        first = false;
                    } else {
                        line.append(",");
                    }
                    line.append(columnValue);
                }
                out.println(line);
                line = new StringBuffer();
            }
        }
        out.println();
protected voidrunStatements(java.io.Reader reader, java.io.PrintStream out)
read in lines and execute them

param
reader the reader contains sql lines.
param
out the place to output results.
throws
SQLException on sql problems
throws
IOException on io problems

        StringBuffer sql = new StringBuffer();
        String line;

        BufferedReader in = new BufferedReader(reader);

        while ((line = in.readLine()) != null) {
            if (!keepformat) {
                line = line.trim();
            }
            line = getProject().replaceProperties(line);
            if (!keepformat) {
                if (line.startsWith("//")) {
                    continue;
                }
                if (line.startsWith("--")) {
                    continue;
                }
                StringTokenizer st = new StringTokenizer(line);
                if (st.hasMoreTokens()) {
                    String token = st.nextToken();
                    if ("REM".equalsIgnoreCase(token)) {
                        continue;
                    }
                }
            }

            if (!keepformat) {
                sql.append(" ");
                sql.append(line);
            } else {
                sql.append("\n");
                sql.append(line);
            }

            // SQL defines "--" as a comment to EOL
            // and in Oracle it may contain a hint
            // so we cannot just remove it, instead we must end it
            if (!keepformat) {
                if (line.indexOf("--") >= 0) {
                    sql.append("\n");
                }
            }
            if ((delimiterType.equals(DelimiterType.NORMAL)
                 && StringUtils.endsWith(sql, delimiter))
                ||
                (delimiterType.equals(DelimiterType.ROW)
                 && line.equals(delimiter))) {
                execSQL(sql.substring(0, sql.length() - delimiter.length()),
                        out);
                sql.replace(0, sql.length(), "");
            }
        }
        // Catch any statements not followed by ;
        if (sql.length() > 0) {
            execSQL(sql.toString(), out);
        }
public voidsetAppend(boolean append)
whether output should be appended to or overwrite an existing file. Defaults to false.

since
Ant 1.5
param
append if true append to an existing file.

        this.append = append;
public voidsetDelimiter(java.lang.String delimiter)
Set the delimiter that separates SQL statements. Defaults to ";"; optional

For example, set this to "go" and delimitertype to "ROW" for Sybase ASE or MS SQL Server.

param
delimiter the separator.

        this.delimiter = delimiter;
public voidsetDelimiterType(org.apache.tools.ant.taskdefs.SQLExec$DelimiterType delimiterType)
Set the delimiter type: "normal" or "row" (default "normal").

The delimiter type takes two values - normal and row. Normal means that any occurrence of the delimiter terminate the SQL command whereas with row, only a line containing just the delimiter is recognized as the end of the command.

param
delimiterType the type of delimiter - "normal" or "row".

        this.delimiterType = delimiterType.getValue();
public voidsetEncoding(java.lang.String encoding)
Set the file encoding to use on the SQL files read in

param
encoding the encoding to use on the files

        this.encoding = encoding;
public voidsetEscapeProcessing(boolean enable)
Set escape processing for statements.

param
enable if true enable escape processing, default is true.
since
Ant 1.6

        escapeProcessing = enable;
public voidsetExpandProperties(boolean expandProperties)
Enable property expansion inside nested text

param
expandProperties if true expand properties.
since
Ant 1.7

        this.expandProperties = expandProperties;
public voidsetKeepformat(boolean keepformat)
whether or not format should be preserved. Defaults to false.

param
keepformat The keepformat to set

        this.keepformat = keepformat;
public voidsetOnerror(org.apache.tools.ant.taskdefs.SQLExec$OnError action)
Action to perform when statement fails: continue, stop, or abort optional; default "abort"

param
action the action to perform on statement failure.

        this.onError = action.getValue();
public voidsetOutput(java.io.File output)
Set the output file; optional, defaults to the Ant log.

param
output the output file to use for logging messages.

        this.output = output;
public voidsetPrint(boolean print)
Print result sets from the statements; optional, default false

param
print if true print result sets.

        this.print = print;
public voidsetShowheaders(boolean showheaders)
Print headers for result sets from the statements; optional, default true.

param
showheaders if true print headers of result sets.

        this.showheaders = showheaders;
public voidsetShowtrailers(boolean showtrailers)
Print trailing info (rows affected) for the SQL Addresses Bug/Request #27446

param
showtrailers if true prints the SQL rows affected
since
Ant 1.7

        this.showtrailers = showtrailers;
public voidsetSrc(java.io.File srcFile)
Set the name of the SQL file to be run. Required unless statements are enclosed in the build file

param
srcFile the file containing the SQL command.


                                    
        
        this.srcFile = srcFile;