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.
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.
getParameterMetaData
.
autocommit | Autocommit statements once executed. |
callable | If the statement calls a stored procedure. |
connection | The connection to use. |
delimiter | Set the delimiter that separates SQL statements. |
delimiterType | Set the delimiter type: NORMAL or ROW. |
dialect | Allows a org.oddjob.sql.DatabaseDialect to be provided
that can tune the way the result set is processed. |
encoding | Set the string encoding to use on the SQL read in. |
escapeProcessing | Set escape processing for statements. |
executedSQLCount | The number of SQL statements executed. |
expandProperties | Enable property expansion inside the SQL statements read from the input. |
input | The input from where to read the SQL query or DML statement(s) to run. |
keepFormat | Whether or not the format of the SQL should be preserved. |
name | A name, can be any text. |
onError | What to do when a statement fails:
autocommit is true then ABORT behaves
like STOP as no roll back is possible. |
parameters | Parameters to be bound to statement(s). |
results | Optional result processor. |
successfulSQLCount | The number of SQL statements successfully executed. |
Example 1 | A simple example shows first the execution of multiple statements, then a simple parameterised query. |
Example 2 | An Callable Statement example. |
Configured By | ATTRIBUTE |
Access | READ_WRITE |
Required | No, defaults to false. |
Autocommit statements once executed.
Configured By | ATTRIBUTE |
Access | READ_WRITE |
Required | No, defaults to false. |
If the statement calls a stored procedure.
Configured By | ELEMENT |
Access | WRITE_ONLY |
Required | Yes. |
The connection to use. This can be provided by a connection or by some other means such as custom data source. This SQL job will always close the connection once it has run.
Configured By | ATTRIBUTE |
Access | READ_WRITE |
Required | No. Defaults to ; |
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.
Configured By | ATTRIBUTE |
Access | READ_WRITE |
Required | No, defaults to NORMAL. |
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.
Configured By | ELEMENT |
Access | READ_WRITE |
Required | No. A default is used. |
Allows a org.oddjob.sql.DatabaseDialect
to be provided
that can tune the way the result set is processed.
Configured By | ATTRIBUTE |
Access | READ_WRITE |
Required | No. |
Set the string encoding to use on the SQL read in.
Configured By | ATTRIBUTE |
Access | READ_WRITE |
Required | No, defaults to false. |
Set escape processing for statements. See the java doc for
Statement.setEscapeProcessing
for more information.
Access | READ_ONLY |
The number of SQL statements executed.
Configured By | ATTRIBUTE |
Access | READ_WRITE |
Required | No, defaults to false. |
Enable property expansion inside the SQL statements read from the input.
Configured By | ELEMENT |
Access | WRITE_ONLY |
Required | Yes. |
The input from where to read the SQL query or DML statement(s) to run. Probably either file for reading the SQL from a file or buffer for configuring the SQL in line.
Configured By | ATTRIBUTE |
Access | READ_WRITE |
Required | No. Defaults to false. |
Whether or not the format of the SQL should be preserved.
Configured By | ATTRIBUTE |
Access | READ_WRITE |
Required | No. |
A name, can be any text.
Configured By | ATTRIBUTE |
Access | READ_WRITE |
Required | No, defaults to ABORT. |
What to do when a statement fails:
autocommit
is true then ABORT behaves
like STOP as no roll back is possible.
Configured By | ELEMENT |
Access | READ_WRITE |
Required | No. |
Parameters to be bound to statement(s). This is either a value or an identify if the parameter is an out parameter that is to be identifiable by an id for other jobs to access.
Configured By | ELEMENT |
Access | READ_WRITE |
Required | No, defaults to none. |
Optional result processor. Probably one of sql-results-bean or sql-results-sheet.
Access | READ_ONLY |
The number of SQL statements successfully executed.
A simple example shows 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('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 sql-results-bean.
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 interrupted 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>