org.oddjob.sql
Class SQLJob

java.lang.Object
  org.oddjob.sql.SQLJob

All Implemented Interfaces:

Serializable, Runnable, ArooaSessionAware, Stoppable

public class SQLJob
extends Object
implements Runnable, Serializable, ArooaSessionAware, Stoppable

Description

Runs one or more SQL statements.

An optional result processor may be provided. Current implementations allow the results to be displayed on a result sheet in a similar style to an SQL query tool, or results can be captured as beans who's properties can be used elsewhere in Oddjob.

SQL statements can be parameterised, and can be stored procedure or function calls. Out parameter values can also be accessed and used elsewhere in Oddjob.

Example

A simple example show first the execution of multiple statements, then a simple parameterised query.

<oddjob>
    <job>
        <sequential>
            <jobs>
                <variables id="vars">
                    <connection>
                        <connection driver="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:mem:test" username="sa"/>
                    </connection>
                </variables>
                <sql name="Create table">
                    <connection>
                        <value value="${vars.connection}"/>
                    </connection>
                    <input>
                        <buffer>
	        	            
create table GREETINGS(STYLE varchar(20),
       TEXT varchar(20))
       
insert into GREETINGS values('nice', 'Hello')

insert into GREETINGS values('gumpy', 'Bah Humbug')

                        </buffer>
                    </input>
                </sql>
                <sql id="query">
                    <connection>
                        <value value="${vars.connection}"/>
                    </connection>
                    <input>
                        <buffer>
	        	            
select TEXT from GREETINGS where STYLE = ?

                        </buffer>
                    </input>
                    <parameters>
                        <value value="nice"/>
                    </parameters>
                    <results>
                        <sql-results-bean/>
                    </results>
                </sql>
                <echo name="Single Row Result">${query.results.row.TEXT}</echo>
                <echo name="Result By Row Index">${query.results.rows[0].TEXT}</echo>
            </jobs>
        </sequential>
    </job>
</oddjob>

The results are made available to the echo jobs using a SQLResultsBean.

Example

An Callable Statement example. Showing support for IN, INOUT, and OUT parameters. Note that declaring the stored procedure requires a change in delimiter otherwise the semicolon is inturprited as an end of statement.

<oddjob>
    <job>
        <sequential>
            <jobs>
                <variables id="vars">
                    <connection>
                        <connection driver="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:mem:test" username="sa"/>
                    </connection>
                </variables>
                <sql callable="true" delimiterType="ROW">
                    <connection>
                        <value value="${vars.connection}"/>
                    </connection>
                    <input>
                        <buffer>
create procedure TEST (out a int, inout b int, in c int)
    MODIFIES SQL DATA
    begin atomic
        set a = b;
        set b = c;
    end
                        </buffer>
                    </input>
                </sql>
                <sql id="sql-call" callable="true">
                    <connection>
                        <value value="${vars.connection}"/>
                    </connection>
                    <parameters>
                        <identify id="a">
                            <value>
                                <value/>
                            </value>
                        </identify>
                        <identify id="b">
                            <value>
                                <value value="2"/>
                            </value>
                        </identify>
                        <value value="3"/>
                    </parameters>
                    <input>
                        <buffer>
call TEST (?, ?, ?)
                        </buffer>
                    </input>
                </sql>
                <echo>a=${a}, b=${b}.</echo>
            </jobs>
        </sequential>
    </job>
</oddjob>

See Also:

Serialized Form

Nested Class Summary

static class	SQLJob.DelimiterType delimiters we support, "NORMAL" and "ROW"
static class	SQLJob.OnError The action a task should perform on an error, one of "continue", "stop" and "abort"

Constructor Summary

SQLJob()
Constructor.

Method Summary

String	getDelimiter() Get the statement delimiter.
SQLJob.DelimiterType	getDelimiterType() Get the delimiter type.
String	getEncoding() Get the input encoding name.
int	getExecutedSQLCount()
boolean	getExpandProperties() is property expansion inside inline text enabled?
String	getName() Get the name.
SQLJob.OnError	getOnError() Get on error action.
ValueType	getParameters(int index)
SQLResultsProcessor	getResults()
int	getSuccessfulSQLCount()
boolean	isCallable() Is the statement a store procedure.
boolean	isEscapeProcessing()
boolean	isKeepFormat() Get if SQL keeps input format.
void	run()
void	setArooaSession(ArooaSession session)
void	setCallable(boolean callable)
void	setConnection(Connection connection)
void	setDelimiter(String delimiter)
void	setDelimiterType(SQLJob.DelimiterType delimiterType)
void	setEncoding(String encoding)
void	setEscapeProcessing(boolean enable)
void	setExpandProperties(boolean expandProperties)
void	setInput(InputStream sql)
void	setKeepFormat(boolean keepformat)
void	setName(String name) Set the name
void	setOnError(SQLJob.OnError action)
void	setParameters(int index, ValueType parameter)
void	setResults(SQLResultsProcessor results)
void	stop() Stop executing.
String	toString()

Methods inherited from class java.lang.Object

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

Constructor Detail

SQLJob

public SQLJob()

Constructor.

Method Detail

setArooaSession

public void setArooaSession(ArooaSession session)

Specified by:

setArooaSession in interface ArooaSessionAware

getName

public String getName()

Get the name.

Returns:

The name.

setName

public void setName(String name)

Set the name

Parameters:

name - The name.

run

public void run()

Specified by:

run in interface Runnable

stop

public void stop()

Description copied from interface: Stoppable

Stop executing. This method should not return until the Stoppable has actually stopped.

Specified by:

stop in interface Stoppable

getResults

public SQLResultsProcessor getResults()

setResults

public void setResults(SQLResultsProcessor results)

setInput

public void setInput(InputStream sql)

Property: input

Description: The input from where to read the SQL query or DML statement(s) to run.

Required: Yes.

setExpandProperties

public void setExpandProperties(boolean expandProperties)

Property: expandProperties

Description: Enable property expansion inside the SQL statements read from the input.

Required: No, defaults to false.

getExpandProperties

public boolean getExpandProperties()

is property expansion inside inline text enabled?

Since:

Ant 1.7

Returns:

true if properties are to be expanded.

setEncoding

public void setEncoding(String encoding)

Property: encoding

Description: Set the string encoding to use on the SQL read in.

Required: No.

getEncoding

public String getEncoding()

Get the input encoding name.

Returns:

setDelimiter

public void setDelimiter(String delimiter)

Property: delimiter

Description: 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.

Required: No.

Parameters:

delimiter - the separator.

getDelimiter

public String getDelimiter()

Get the statement delimiter.

Returns:

setDelimiterType

public void setDelimiterType(SQLJob.DelimiterType delimiterType)

Property: delimiterType

Description: 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 recognised as the end of the command.

Required: No.

Parameters:

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

getDelimiterType

public SQLJob.DelimiterType getDelimiterType()

Get the delimiter type.

Returns:

setKeepFormat

public void setKeepFormat(boolean keepformat)

Property: keepFormat

Description: Whether or not the format of the SQL should be preserved.

Required: No. Defaults to false.

Parameters:

keepformat - The keepformat to set

isKeepFormat

public boolean isKeepFormat()

Get if SQL keeps input format.

Returns:

setConnection

public void setConnection(Connection connection)

Property: connection

Description: The ConnectionType to use.

Required: Yes.

getParameters

public ValueType getParameters(int index)

setParameters

public void setParameters(int index,
                          ValueType parameter)

Property: parameters

Description: Parameters to be bound to statement(s).

Required: No.

setCallable

public void setCallable(boolean callable)

Property: callable

Description: If the statement calls a stored procedure.

Required: No, defaults to false.

isCallable

public boolean isCallable()

Is the statement a store procedure.

Returns:

setEscapeProcessing

public void setEscapeProcessing(boolean enable)

Property: escapeProcessing

Description: Set escape processing for statements.

Required: No, defaults to false.

Parameters:

enable - if true enable escape processing, default is true.

isEscapeProcessing

public boolean isEscapeProcessing()

Returns:

setOnError

public void setOnError(SQLJob.OnError action)

Property: onError

Description: Action to perform when statement fails: continue, stop, or abort optional; default "abort"

Required: No, defaults to false.

Parameters:

action - the action to perform on statement failure.

getOnError

public SQLJob.OnError getOnError()

Get on error action.

Returns:

getExecutedSQLCount

public int getExecutedSQLCount()

Property: executedSQLCount

Description: The number of SQL statements executed.

Returns:

The number.

getSuccessfulSQLCount

public int getSuccessfulSQLCount()

Property: successfulSQLCount

Description: The number of SQL statements successfully executed.

Returns:

The number.

toString

public String toString()

Overrides:

toString in class Object