public class SQLJob extends java.lang.Object implements java.lang.Runnable, java.io.Serializable, ArooaSessionAware, Stoppable, BusServiceProvider
delimiter
and delimiterType
properties.
Setting the expandProperties
property to true will cause
Oddjob to expand ${} expressions within the SQL. Comments are achieved
by starting a line with --
or //
or
REM
. Note that /* */
is not yet supported.
SQLResultsSheet
allows the results to be displayed on a result sheet in a similar style
to an SQL query tool. SQLResultsBean
allows results to be
captured as beans who's properties can be used elsewhere in Oddjob.
onError
property controls what to do if a statement fails.
By default it is ABORT. Auto commit is false by default so the changes
are rolled back. If auto commit is true the ABORT has the same affect as
STOP which commits statements already executed.
IdentifiableValueType
. See example 2 for an example of this.
getParameterMetaData
.<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('grumpy', '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
.<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>
Modifier and Type | Class and Description |
---|---|
static class |
SQLJob.DelimiterType
delimiter type between SQL statements.
|
static class |
SQLJob.OnError
The action a task should perform on an error.
|
Constructor and Description |
---|
SQLJob()
Constructor.
|
Modifier and Type | Method and Description |
---|---|
java.lang.String |
getDelimiter()
Get the statement delimiter.
|
SQLJob.DelimiterType |
getDelimiterType()
Get the delimiter type.
|
DatabaseDialect |
getDialect()
Getter for dialect.
|
java.lang.String |
getEncoding()
Get the input encoding name.
|
int |
getExecutedSQLCount() |
boolean |
getExpandProperties()
Is property expansion inside inline text enabled?
|
java.lang.String |
getName()
Get the name.
|
SQLJob.OnError |
getOnError()
Get on error action.
|
ValueType |
getParameters(int index)
Indexed getter for parameter types.
|
java.util.Collection<java.lang.Object> |
getResults()
Getter for results.
|
SimpleBusService |
getServices()
Provider the services.
|
int |
getSuccessfulSQLCount() |
boolean |
isAutocommit()
Getter for autocommit.
|
boolean |
isCallable()
Is the statement a stored procedure.
|
boolean |
isEscapeProcessing()
Getter for escapeProcessing.
|
boolean |
isKeepFormat()
Get if SQL keeps input format.
|
void |
run() |
void |
setArooaSession(ArooaSession session)
Accept the current Arooa Session.
|
void |
setAutocommit(boolean autocommit) |
void |
setCallable(boolean callable) |
void |
setConnection(java.sql.Connection connection) |
void |
setDelimiter(java.lang.String delimiter) |
void |
setDelimiterType(SQLJob.DelimiterType delimiterType) |
void |
setDialect(DatabaseDialect dialect) |
void |
setEncoding(java.lang.String encoding) |
void |
setEscapeProcessing(boolean enable) |
void |
setExpandProperties(boolean expandProperties) |
void |
setInput(java.io.InputStream sql) |
void |
setKeepFormat(boolean keepformat) |
void |
setName(java.lang.String name)
Set the name
|
void |
setOnError(SQLJob.OnError action) |
void |
setParameters(int index,
ValueType parameter) |
void |
setResults(java.util.Collection<java.lang.Object> results)
Setter for results.
|
void |
stop()
Stop executing.
|
java.lang.String |
toString() |
public void setArooaSession(ArooaSession session)
ArooaSessionAware
setArooaSession
in interface ArooaSessionAware
session
- The Arooa Session. Never null.public java.lang.String getName()
public void setName(java.lang.String name)
name
- The name.public void run()
run
in interface java.lang.Runnable
public void stop()
Stoppable
public SimpleBusService getServices()
ServiceProvider
getServices
in interface ServiceProvider
getServices
in interface BusServiceProvider
public java.util.Collection<java.lang.Object> getResults()
public void setResults(java.util.Collection<java.lang.Object> results)
results
- Result Handler. May be null.public void setInput(java.io.InputStream sql)
Property: input
Description: The input from where to read the SQL query
or DML statement(s) to run. Probably either FileType
for
reading the SQL from a file or BufferType
for configuring
the SQL in line.
Required: Yes.
public void setExpandProperties(boolean expandProperties)
Property: expandProperties
Description: Enable property expansion inside the SQL statements read from the input.
Required: No, defaults to false.
public boolean getExpandProperties()
public void setEncoding(java.lang.String encoding)
Property: encoding
Description: Set the string encoding to use on the SQL read in.
Required: No.
public java.lang.String getEncoding()
public void setDelimiter(java.lang.String delimiter)
Property: delimiter
Description: Set the delimiter that separates SQL statements. Defaults to a semicolon.
For scripts that use a separate line delimiter like "GO"
also set the delimiterType
to "ROW".
The delimiter is case insensitive so either "GO" or "go" can be used interchangeably.
Required: No. Defaults to ;
delimiter
- the separator.public java.lang.String getDelimiter()
public void setDelimiterType(SQLJob.DelimiterType delimiterType)
Property: delimiterType
Description: Set the delimiter type: NORMAL or ROW.
NORMAL means that any occurrence of the delimiter terminates the SQL command whereas with ROW, only a line containing just the delimiter is recognised as the end of the command.
ROW is used with delimiters such as GO.
Required: No, defaults to NORMAL.
delimiterType
- the type of delimiter - "NORMAL" or "ROW".public SQLJob.DelimiterType getDelimiterType()
public void setKeepFormat(boolean keepformat)
Property: keepFormat
Description: Whether or not the format of the SQL should be preserved.
Required: No. Defaults to false.
keepformat
- The keepformat to setpublic boolean isKeepFormat()
public void setConnection(java.sql.Connection connection)
Property: connection
Description: The connection to use. This can be provided
by a ConnectionType
or by some other means such as custom
data source. This SQL job will always close the connection once
it has run.
Required: Yes.
public void setAutocommit(boolean autocommit)
Property: autocommit
Description: Autocommit statements once executed.
Required: No, defaults to false.
autocommit
- public boolean isAutocommit()
public ValueType getParameters(int index)
index
- public void setParameters(int index, ValueType parameter)
Property: parameters
Description: Parameters to be bound to statement(s). This
is either a ValueType
or an IdentifiableValueType
if the parameter is an out parameter that is to be identifiable by
an id for other jobs to access.
Required: No.
public void setCallable(boolean callable)
Property: callable
Description: If the statement calls a stored procedure.
Required: No, defaults to false.
public boolean isCallable()
public void setEscapeProcessing(boolean enable)
Property: escapeProcessing
Description: Set escape processing for statements. See the java doc for
Statement.setEscapeProcessing
for more information.
Required: No, defaults to false.
enable
- if true enable escape processing, default is true.public boolean isEscapeProcessing()
public void setDialect(DatabaseDialect dialect)
Property: dialect
Description: Allows a DatabaseDialect
to be provided
that can tune the way the result set is processed.
Required: No. A default is used.
dialect
- The Database Dialect.public DatabaseDialect getDialect()
public void setOnError(SQLJob.OnError action)
Property: onError
Description: What to do when a statement fails:
autocommit
is true then ABORT behaves
like STOP as no roll back is possible.Required: No, defaults to ABORT.
action
- the action to perform on statement failure.public SQLJob.OnError getOnError()
public int getExecutedSQLCount()
Property: executedSQLCount
Description: The number of SQL statements executed.
public int getSuccessfulSQLCount()
Property: successfulSQLCount
Description: The number of SQL statements successfully executed.
public java.lang.String toString()
toString
in class java.lang.Object