org.apache.tools.ant.taskdefs

Class 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

UNKNOWN: name="sql" category="database"

Nested Class Summary
static classSQLExec.DelimiterType
delimiters we support, "normal" and "row"
static classSQLExec.OnError
The action a task should perform on an error, one of "continue", "stop" and "abort"
classSQLExec.Transaction
Contains the definition of a new transaction element.
Method Summary
voidadd(ResourceCollection rc)
Adds a collection of resources (nested element).
voidaddFileset(FileSet set)
Adds a set of files (nested fileset attribute).
voidaddText(String sql)
Set an inline SQL command to execute.
SQLExec.TransactioncreateTransaction()
Add a SQL transaction to execute
protected voidexecSQL(String sql, PrintStream out)
Exec the sql statement.
voidexecute()
Load the sql file and then execute it
protected ConnectiongetConnection()
Caches the connection returned by the base class's getConnection method.
booleangetExpandProperties()
is property expansion inside inline text enabled?
protected StatementgetStatement()
Creates and configures a Statement instance which is then cached for subsequent calls.
intlastDelimiterPosition(StringBuffer buf, String currentLine)
protected voidprintResults(PrintStream out)
print any results in the statement
protected voidprintResults(ResultSet rs, PrintStream out)
print any results in the result set.
protected voidrunStatements(Reader reader, PrintStream out)
read in lines and execute them
voidsetAppend(boolean append)
whether output should be appended to or overwrite an existing file.
voidsetCsvColumnSeparator(String s)
The column separator used when printing the results.
voidsetCsvQuoteCharacter(String s)
The character used to quote column values.
voidsetDelimiter(String delimiter)
Set the delimiter that separates SQL statements.
voidsetDelimiterType(SQLExec.DelimiterType delimiterType)
Set the delimiter type: "normal" or "row" (default "normal").
voidsetEncoding(String encoding)
Set the file encoding to use on the SQL files read in
voidsetErrorProperty(String errorProperty)
Property to set to "true" if a statement throws an error.
protected voidsetErrorProperty()
voidsetEscapeProcessing(boolean enable)
Set escape processing for statements.
voidsetExpandProperties(boolean expandProperties)
Enable property expansion inside nested text
voidsetKeepformat(boolean keepformat)
whether or not format should be preserved.
voidsetOnerror(SQLExec.OnError action)
Action to perform when statement fails: continue, stop, or abort optional; default "abort"
voidsetOutput(File output)
Set the output file; optional, defaults to the Ant log.
voidsetOutput(Resource output)
Set the output Resource; optional, defaults to the Ant log.
voidsetPrint(boolean print)
Print result sets from the statements; optional, default false
voidsetRawBlobs(boolean rawBlobs)
Set whether to print raw BLOBs rather than their string (hex) representations.
voidsetRowCountProperty(String rowCountProperty)
Sets a given property to the number of rows in the first statement that returned a row count.
protected voidsetRowCountProperty(int rowCount)
voidsetShowheaders(boolean showheaders)
Print headers for result sets from the statements; optional, default true.
voidsetShowtrailers(boolean showtrailers)
Print trailing info (rows affected) for the SQL Addresses Bug/Request #27446
voidsetShowWarnings(boolean b)
whether to show SQLWarnings as WARN messages.
voidsetSrc(File srcFile)
Set the name of the SQL file to be run.
voidsetStrictDelimiterMatching(boolean b)
If false, delimiters will be searched for in a case-insesitive manner (i.e. delimer="go" matches "GO") and surrounding whitespace will be ignored (delimter="go" matches "GO ").
voidsetTreatWarningsAsErrors(boolean b)
Whether a warning is an error - in which case onError aplies.
voidsetWarningProperty(String warningProperty)
Property to set to "true" if a statement produces a warning.
protected voidsetWarningProperty()

Method Detail

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

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.

addText

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

createTransaction

public SQLExec.Transaction createTransaction()
Add a SQL transaction to execute

Returns: a Transaction to be configured.

execSQL

protected void execSQL(String sql, PrintStream out)
Exec the sql statement.

Parameters: sql the SQL statement to execute out the place to put output

Throws: SQLException on SQL problems

execute

public void execute()
Load the sql file and then execute it

Throws: BuildException on error.

getConnection

protected 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.

getExpandProperties

public boolean getExpandProperties()
is property expansion inside inline text enabled?

Returns: true if properties are to be expanded.

Since: Ant 1.7

getStatement

protected Statement getStatement()
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.

lastDelimiterPosition

public int lastDelimiterPosition(StringBuffer buf, String currentLine)

printResults

protected void printResults(PrintStream out)

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: SQLException on SQL problems.

printResults

protected void printResults(ResultSet rs, PrintStream out)
print any results in the result set.

Parameters: rs the resultset to print information about out the place to print results

Throws: SQLException on SQL problems.

Since: Ant 1.6.3

runStatements

protected void runStatements(Reader reader, PrintStream out)
read in lines and execute them

Parameters: reader the reader contains sql lines. out the place to output results.

Throws: SQLException on sql problems IOException on io problems

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

setCsvColumnSeparator

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

Defaults to ","

Since: Ant 1.8.0

setCsvQuoteCharacter

public void setCsvQuoteCharacter(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"

Since: Ant 1.8.0

setDelimiter

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

setEncoding

public void setEncoding(String encoding)
Set the file encoding to use on the SQL files read in

Parameters: encoding the encoding to use on the files

setErrorProperty

public void setErrorProperty(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

setErrorProperty

protected final void setErrorProperty()

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

setExpandProperties

public void setExpandProperties(boolean expandProperties)
Enable property expansion inside nested text

Parameters: expandProperties if true expand properties.

Since: Ant 1.7

setKeepformat

public void setKeepformat(boolean keepformat)
whether or not format should be preserved. Defaults to false.

Parameters: keepformat The keepformat to set

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.

setOutput

public void setOutput(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

setPrint

public void setPrint(boolean print)
Print result sets from the statements; optional, default false

Parameters: print if true print result sets.

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

setRowCountProperty

public void setRowCountProperty(String rowCountProperty)
Sets a given property to the number of rows in the first statement that returned a row count.

Since: Ant 1.8.0

setRowCountProperty

protected final void setRowCountProperty(int rowCount)

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

setShowWarnings

public void setShowWarnings(boolean b)
whether to show SQLWarnings as WARN messages.

Since: Ant 1.8.0

setSrc

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

setStrictDelimiterMatching

public void setStrictDelimiterMatching(boolean b)
If false, delimiters will be searched for in a case-insesitive manner (i.e. delimer="go" matches "GO") and surrounding whitespace will be ignored (delimter="go" matches "GO ").

Since: Ant 1.8.0

setTreatWarningsAsErrors

public void setTreatWarningsAsErrors(boolean b)
Whether a warning is an error - in which case onError aplies.

Since: Ant 1.8.0

setWarningProperty

public void setWarningProperty(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

setWarningProperty

protected final void setWarningProperty()