com.massimo.tools.scriptrunner.work
Class SQLTask

java.lang.Object
  com.massimo.tools.scriptrunner.AbstractTask
      com.massimo.tools.scriptrunner.work.SQLTask

All Implemented Interfaces:

Task

Direct Known Subclasses:

SQLFunctionTask, SQLGeneratorTask, ThreadedSQLTask

public class SQLTask
extends AbstractTask

Task to handle SQL or procedural SQL from the definition text.
Introduces attributes Constants.ATTRIBUTE_BINDS and Constants.ATTRIBUTE_STATMENT_SPLIT.
The task element body is the SQL to execute. The SQL is split into statements according to the statement-split attribute or pre-defined patterns (for supported databases). The statements are executed and progress logged. After an error and exit, a re-start will pick up at the statement that errored.

Field Summary

protected static String	BIND_END_DELIMITERS Punctuation that can make the end of a bind variable
protected String[]	bindNames
protected Connection	connection
protected DatabaseType	dbType
protected String	run_id
protected static String	SQL_CHECK_PREFIX_ORACLE
protected static String	SQL_CHECK_PREFIX_POSTGRES
protected static String	SQL_CHECK_SUFFIX_ORACLE
protected static String	SQL_CHECK_SUFFIX_POSTGRES
protected static Pattern	SQL_NON_COMMENT_PATTERN
protected static Pattern	SQL_STMT_PATTERN_ORACLE Oracle uses /
protected static Pattern	SQL_STMT_PATTERN_POSTGRES Postgres uses \g

Fields inherited from class com.massimo.tools.scriptrunner.AbstractTask

definition, name, parent, properties, type

Fields inherited from interface com.massimo.tools.scriptrunner.Task

DATA_ERROR, ERROR, FINISHED, INTERRUPTED, PROMPT, RUNNING, START, SUCCESS

Constructor Summary

SQLTask(Task parent, String type, Element definition, Map<String,String> properties)

Method Summary

protected int	doProcess(ExecutionContext context) Implementing classes only need to do their own work and log progress when appropriate.
protected int	doValidate(ValidationContext context) Check whether this task should be able to run.
protected int	executeSQL(ExecutionContext context, String sqlText)
protected void	finalize() Attempts to close the database connection.
protected String[]	getBindNames() Read the binds attribute and return variable names in a String array.
protected org.apache.log4j.Logger	getLogger()
protected Pattern	getSQLSplitPattern() Checks attribute statement-split, before looking at the DBMS connection.
protected String	getSQLText() Sub-classes may override this if the SQL is not simply the XML text.
protected String	getSQLWarning(SQLWarning sqlWarning) Walk down the chain of warnings, adding each to the return value.
protected String[]	parseTextIntoStatements(String parameterizedText) Break the text up into individaul statments.
protected int	postCheck(ExecutionContext context) Execute the configured pre-execution check function The default is run_progress.postcheck(:run_id, :module).
protected int	preCheck() A precheck can be done to see if the task should be executed.
protected void	setBinds(PreparedStatement stmt, List<String> binds) Bind the variables to the statement.
protected String	substitiuteBinds(String sql, List<String> binds) Parses the statement looking for known bind variables.
protected int	validateBinds(ValidationContext context) Checks that bind variables in bindNames have values.
protected int	validateSQL(String sqlText, ValidationContext context)

Methods inherited from class com.massimo.tools.scriptrunner.AbstractTask

getBooleanProperty, getCurrentWorkingDirectory, getDefinition, getIntProperty, getName, getParent, getProperties, getStartProcessingRow, getText, getType, interrupt, isIgnoreableError, isIgnoreableError, isInterrupted, isLogProgress, postProcess, preProcess, process, resultCodeToString, substituteParameters, toString, validate, validateConnections, validateParameters

Methods inherited from class java.lang.Object

clone, equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

BIND_END_DELIMITERS

protected static final String BIND_END_DELIMITERS

Punctuation that can make the end of a bind variable

See Also:

Constant Field Values

bindNames

protected String[] bindNames

connection

protected Connection connection

dbType

protected DatabaseType dbType

run_id

protected String run_id

SQL_CHECK_PREFIX_ORACLE

protected static final String SQL_CHECK_PREFIX_ORACLE

See Also:

Constant Field Values

SQL_CHECK_PREFIX_POSTGRES

protected static final String SQL_CHECK_PREFIX_POSTGRES

See Also:

Constant Field Values

SQL_CHECK_SUFFIX_ORACLE

protected static final String SQL_CHECK_SUFFIX_ORACLE

See Also:

Constant Field Values

SQL_CHECK_SUFFIX_POSTGRES

protected static final String SQL_CHECK_SUFFIX_POSTGRES

See Also:

Constant Field Values

SQL_NON_COMMENT_PATTERN

protected static final Pattern SQL_NON_COMMENT_PATTERN

SQL_STMT_PATTERN_ORACLE

protected static final Pattern SQL_STMT_PATTERN_ORACLE

Oracle uses /

SQL_STMT_PATTERN_POSTGRES

protected static final Pattern SQL_STMT_PATTERN_POSTGRES

Postgres uses \g

Constructor Detail

SQLTask

public SQLTask(Task parent,
               String type,
               Element definition,
               Map<String,String> properties)
        throws Exception

Parameters:

parent -

type -

definition -

properties -

Throws:

Exception

Method Detail

doProcess

protected int doProcess(ExecutionContext context)
                 throws Exception

Implementing classes only need to do their own work and log progress when appropriate.

Specified by:

doProcess in class AbstractTask

Returns:

O for success. Anything else is a failure.

Throws:

Exception

See Also:

AbstractTask.process(ExecutionContext)

doValidate

protected int doValidate(ValidationContext context)

Check whether this task should be able to run. Implementations should check for the prescence of files, attributes,...

Specified by:

doValidate in class AbstractTask

Returns:

O for success. Anything else is a failure.

executeSQL

protected int executeSQL(ExecutionContext context,
                         String sqlText)
                  throws SQLException,
                         SRException

Parameters:

context -

sqlText -

Returns:

result code from executing the sql fragment

Throws:

SQLException

SRException

finalize

protected void finalize()
                 throws Throwable

Attempts to close the database connection.

Overrides:

finalize in class Object

Throws:

Throwable

getBindNames

protected String[] getBindNames()

Read the binds attribute and return variable names in a String array.

Returns:

String[] The bind variable names, specified in the task definition.

getLogger

protected org.apache.log4j.Logger getLogger()

Specified by:

getLogger in class AbstractTask

Returns:

Class logger

getSQLSplitPattern

protected Pattern getSQLSplitPattern()

Checks attribute statement-split, before looking at the DBMS connection.

Returns:

Pattern to split some text into SQL statements

getSQLText

protected String getSQLText()

Sub-classes may override this if the SQL is not simply the XML text.

Returns:

SQL text

getSQLWarning

protected String getSQLWarning(SQLWarning sqlWarning)

Walk down the chain of warnings, adding each to the return value.

Parameters:

sqlWarning -

Returns:

A String concatenating all chained messages

parseTextIntoStatements

protected String[] parseTextIntoStatements(String parameterizedText)

Break the text up into individaul statments. This is based on finding the execution character, '/', on a line by itself. Each statement is further processed: - SHOW ERRORS is ignored - '--' comment lines are ignored - 'REM' comment lines are ignored

Parameters:

parameterizedText -

Returns:

Array of statements, which may be null.

postCheck

protected int postCheck(ExecutionContext context)
                 throws SQLException,
                        SRException

Execute the configured pre-execution check function The default is run_progress.postcheck(:run_id, :module). The postcheck function must have signature: FUNCTION f(run_id IN INTEGER, module IN VARCHAR2) RETURNS INTEGER

Parameters:

context -

Returns:

Task.SUCCESS if the task succeeded.

Throws:

SQLException

SRException

preCheck

protected int preCheck()
                throws SQLException

A precheck can be done to see if the task should be executed. Unless this is set, no precheck is run. The precheck function must have signature: FUNCTION f(run_id IN INTEGER, module IN VARCHAR2) RETURNS INTEGER

Returns:

If 0 processing will continue and the main task executed. Otherwise, the task returns failure.

Throws:

SQLException - if the query fails.

setBinds

protected void setBinds(PreparedStatement stmt,
                        List<String> binds)
                 throws SQLException

Bind the variables to the statement. Note that if a bind variable name appears twice in the SQL, it must appear twice in binds (JDBC behavior). There are three bind variable names populated by context: run_id, name and cwd. Any other bind variable must have property of the same name defined.

Parameters:

stmt - PreparedStatement

binds - List

Throws:

SQLException

substitiuteBinds

protected String substitiuteBinds(String sql,
                                  List<String> binds)

Parses the statement looking for known bind variables. These will look like :<known name><delimiter> Bind varibles are assumed to start with a colon (:), and end with a delimiter: ,) \n\r\t;=+-*<>/%|& The sql text is returned with the bind variables replaced with the '?' character (JDBC behavior).

Parameters:

sql - StringBuffer

binds - List to populate with the names of bind variables, in the order they appear

Returns:

The SQL text modified for JDBC

validateBinds

protected int validateBinds(ValidationContext context)

Checks that bind variables in bindNames have values. This validates that setBinds(PreparedStatement, List) will run.

Parameters:

context -

Returns:

Task SUCCESS or ERROR

validateSQL

protected int validateSQL(String sqlText,
                          ValidationContext context)