Class SQLExec

  • All Implemented Interfaces:
    java.lang.Cloneable

    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
    • Constructor Detail

      • SQLExec

        public SQLExec()
    • Method Detail

      • setSrc

        public void setSrc​(java.io.File srcFile)
        Set the name of the SQL file to be run. Required unless statements are enclosed in the build file
        Parameters:
        srcFile - the file containing the SQL command.
      • setExpandProperties

        public void setExpandProperties​(boolean expandProperties)
        Enable property expansion inside nested text
        Parameters:
        expandProperties - if true expand properties.
        Since:
        Ant 1.7
      • getExpandProperties

        public boolean getExpandProperties()
        is property expansion inside inline text enabled?
        Returns:
        true if properties are to be expanded.
        Since:
        Ant 1.7
      • addText

        public void addText​(java.lang.String sql)
        Set an inline SQL command to execute. NB: Properties are not expanded in this text unless expandProperties is set.
        Parameters:
        sql - an inline string containing the SQL command.
      • addFileset

        public void addFileset​(FileSet set)
        Adds a set of files (nested fileset attribute).
        Parameters:
        set - a set of files contains SQL commands, each File is run in a separate transaction.
      • add

        public void add​(ResourceCollection rc)
        Adds a collection of resources (nested element).
        Parameters:
        rc - a collection of resources containing SQL commands, each resource is run in a separate transaction.
        Since:
        Ant 1.7
      • createTransaction

        public SQLExec.Transaction createTransaction()
        Add a SQL transaction to execute
        Returns:
        a Transaction to be configured.
      • setEncoding

        public void setEncoding​(java.lang.String encoding)
        Set the file encoding to use on the SQL files read in
        Parameters:
        encoding - the encoding to use on the files
      • setDelimiter

        public void setDelimiter​(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.

        Parameters:
        delimiter - the separator.
      • setDelimiterType

        public void setDelimiterType​(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.

        Parameters:
        delimiterType - the type of delimiter - "normal" or "row".
      • setPrint

        public void setPrint​(boolean print)
        Print result sets from the statements; optional, default false
        Parameters:
        print - if true print result sets.
      • setShowheaders

        public void setShowheaders​(boolean showheaders)
        Print headers for result sets from the statements; optional, default true.
        Parameters:
        showheaders - if true print headers of result sets.
      • setShowtrailers

        public void setShowtrailers​(boolean showtrailers)
        Print trailing info (rows affected) for the SQL Addresses Bug/Request #27446
        Parameters:
        showtrailers - if true prints the SQL rows affected
        Since:
        Ant 1.7
      • setOutput

        public void setOutput​(java.io.File output)
        Set the output file; optional, defaults to the Ant log.
        Parameters:
        output - the output file to use for logging messages.
      • setOutput

        public void setOutput​(Resource output)
        Set the output Resource; optional, defaults to the Ant log.
        Parameters:
        output - the output Resource to store results.
        Since:
        Ant 1.8
      • setOutputEncoding

        public void setOutputEncoding​(java.lang.String outputEncoding)
        The encoding to use when writing the result to a resource.

        Default's to the platform's default encoding

        Parameters:
        outputEncoding - the name of the encoding or null for the platform's default encoding
        Since:
        Ant 1.9.4
      • setAppend

        public void setAppend​(boolean append)
        whether output should be appended to or overwrite an existing file. Defaults to false.
        Parameters:
        append - if true append to an existing file.
        Since:
        Ant 1.5
      • setOnerror

        public void setOnerror​(SQLExec.OnError action)
        Action to perform when statement fails: continue, stop, or abort optional; default "abort"
        Parameters:
        action - the action to perform on statement failure.
      • setKeepformat

        public void setKeepformat​(boolean keepformat)
        whether or not format should be preserved. Defaults to false.
        Parameters:
        keepformat - The keepformat to set
      • setEscapeProcessing

        public void setEscapeProcessing​(boolean enable)
        Set escape processing for statements.
        Parameters:
        enable - if true enable escape processing, default is true.
        Since:
        Ant 1.6
      • setRawBlobs

        public void setRawBlobs​(boolean rawBlobs)
        Set whether to print raw BLOBs rather than their string (hex) representations.
        Parameters:
        rawBlobs - whether to print raw BLOBs.
        Since:
        Ant 1.7.1
      • setStrictDelimiterMatching

        public void setStrictDelimiterMatching​(boolean b)
        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 ").
        Parameters:
        b - boolean
        Since:
        Ant 1.8.0
      • setShowWarnings

        public void setShowWarnings​(boolean b)
        whether to show SQLWarnings as WARN messages.
        Parameters:
        b - boolean
        Since:
        Ant 1.8.0
      • setTreatWarningsAsErrors

        public void setTreatWarningsAsErrors​(boolean b)
        Whether a warning is an error - in which case onError applies.
        Parameters:
        b - boolean
        Since:
        Ant 1.8.0
      • setCsvColumnSeparator

        public void setCsvColumnSeparator​(java.lang.String s)
        The column separator used when printing the results.

        Defaults to ","

        Parameters:
        s - String
        Since:
        Ant 1.8.0
      • setCsvQuoteCharacter

        public void setCsvQuoteCharacter​(java.lang.String s)
        The character used to quote column values.

        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.

        If this value is not set (the default), no column values will be quoted, not even if they contain the column separator.

        Note: BLOB values will never be quoted.

        Defaults to "not set"

        Parameters:
        s - String
        Since:
        Ant 1.8.0
      • setErrorProperty

        public void setErrorProperty​(java.lang.String errorProperty)
        Property to set to "true" if a statement throws an error.
        Parameters:
        errorProperty - the name of the property to set in the event of an error.
        Since:
        Ant 1.8.0
      • setWarningProperty

        public void setWarningProperty​(java.lang.String warningProperty)
        Property to set to "true" if a statement produces a warning.
        Parameters:
        warningProperty - the name of the property to set in the event of a warning.
        Since:
        Ant 1.8.0
      • setRowCountProperty

        public void setRowCountProperty​(java.lang.String rowCountProperty)
        Sets a given property to the number of rows in the first statement that returned a row count.
        Parameters:
        rowCountProperty - String
        Since:
        Ant 1.8.0
      • setForceCsvQuoteChar

        public void setForceCsvQuoteChar​(boolean forceCsvQuoteChar)
        Force the csv quote character
        Parameters:
        forceCsvQuoteChar - boolean
      • runStatements

        protected void runStatements​(java.io.Reader reader,
                                     java.io.PrintStream out)
                              throws java.sql.SQLException,
                                     java.io.IOException
        read in lines and execute them
        Parameters:
        reader - the reader contains sql lines.
        out - the place to output results.
        Throws:
        java.sql.SQLException - on sql problems
        java.io.IOException - on io problems
      • execSQL

        protected void execSQL​(java.lang.String sql,
                               java.io.PrintStream out)
                        throws java.sql.SQLException
        Exec the sql statement.
        Parameters:
        sql - the SQL statement to execute
        out - the place to put output
        Throws:
        java.sql.SQLException - on SQL problems
      • printResults

        @Deprecated
        protected void printResults​(java.io.PrintStream out)
                             throws java.sql.SQLException
        Deprecated.
        since 1.6.x. Use the two arg version instead.
        print any results in the statement
        Parameters:
        out - the place to print results
        Throws:
        java.sql.SQLException - on SQL problems.
      • printResults

        protected void printResults​(java.sql.ResultSet rs,
                                    java.io.PrintStream out)
                             throws java.sql.SQLException
        print any results in the result set.
        Parameters:
        rs - the resultset to print information about
        out - the place to print results
        Throws:
        java.sql.SQLException - on SQL problems.
        Since:
        Ant 1.6.3
      • getConnection

        protected java.sql.Connection getConnection()
        Caches the connection returned by the base class's getConnection method.

        Subclasses that need to provide a different connection than the base class would, should override this method but keep in mind that this class expects to get the same connection instance on consecutive calls.

        returns null if the connection does not connect to the expected RDBMS.

        Overrides:
        getConnection in class JDBCTask
        Returns:
        Connection the newly created connection or null if the connection failed and failOnConnectionError is false.
      • getStatement

        protected java.sql.Statement getStatement()
                                           throws java.sql.SQLException
        Creates and configures a Statement instance which is then cached for subsequent calls.

        Subclasses that want to provide different Statement instances, should override this method but keep in mind that this class expects to get the same connection instance on consecutive calls.

        Returns:
        Statement
        Throws:
        java.sql.SQLException - if statement creation or processing fails
      • lastDelimiterPosition

        public int lastDelimiterPosition​(java.lang.StringBuffer buf,
                                         java.lang.String currentLine)
      • setErrorProperty

        protected final void setErrorProperty()
      • setWarningProperty

        protected final void setWarningProperty()
      • setRowCountProperty

        protected final void setRowCountProperty​(int rowCount)