[Index]

sql


Runs one or more SQL statements.

Parsing

The SQL will be parsed and broken into individual statements before being executed using JDBC. The statements are broken according to the 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.

Result Processing

An optional result processor may be provided. sql-results-sheet allows the results to be displayed on a result sheet in a similar style to an SQL query tool. sql-results-bean allows results to be captured as beans who's properties can be used elsewhere in Oddjob.

Errors and Auto Commit

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

Parameterised Statements and Procedures

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 by wrapping them with an identify. See example 2 for an example of this.

Caveats

SQLServer stored procedures with parameters must be made using the JDBC style call. E.g. { call sp_help(?) } otherwise an exception is thrown from getParameterMetaData.

Property Summary

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:
CONTINUE
Ignore the failure and continue executing.
STOP
Commit what has been executed but don't execute any more.
ABORT
Rollback what has been executed and don't execute any more.
Note that if 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.
services  
successfulSQLCount The number of SQL statements successfully executed.

Example Summary

Example 1 A simple example shows first the execution of multiple statements, then a simple parameterised query.
Example 2 An Callable Statement example.

Property Detail

autocommit

Configured ByATTRIBUTE
AccessREAD_WRITE
RequiredNo, defaults to false.

Autocommit statements once executed.

callable

Configured ByATTRIBUTE
AccessREAD_WRITE
RequiredNo, defaults to false.

If the statement calls a stored procedure.

connection

Configured ByELEMENT
AccessWRITE_ONLY
RequiredYes.

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.

delimiter

Configured ByATTRIBUTE
AccessREAD_WRITE
RequiredNo. 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.

delimiterType

Configured ByATTRIBUTE
AccessREAD_WRITE
RequiredNo, 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.

dialect

Configured ByELEMENT
AccessREAD_WRITE
RequiredNo. A default is used.

Allows a org.oddjob.sql.DatabaseDialect to be provided that can tune the way the result set is processed.

encoding

Configured ByATTRIBUTE
AccessREAD_WRITE
RequiredNo.

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

escapeProcessing

Configured ByATTRIBUTE
AccessREAD_WRITE
RequiredNo, defaults to false.

Set escape processing for statements. See the java doc for Statement.setEscapeProcessing for more information.

executedSQLCount

AccessREAD_ONLY

The number of SQL statements executed.

expandProperties

Configured ByATTRIBUTE
AccessREAD_WRITE
RequiredNo, defaults to false.

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

input

Configured ByELEMENT
AccessWRITE_ONLY
RequiredYes.

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.

keepFormat

Configured ByATTRIBUTE
AccessREAD_WRITE
RequiredNo. Defaults to false.

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

name

Configured ByATTRIBUTE
AccessREAD_WRITE
RequiredNo.

A name, can be any text.

onError

Configured ByATTRIBUTE
AccessREAD_WRITE
RequiredNo, defaults to ABORT.

What to do when a statement fails:

CONTINUE
Ignore the failure and continue executing.
STOP
Commit what has been executed but don't execute any more.
ABORT
Rollback what has been executed and don't execute any more.
Note that if autocommit is true then ABORT behaves like STOP as no roll back is possible.

parameters

Configured ByELEMENT
AccessREAD_WRITE
RequiredNo.

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.

results

Configured ByELEMENT
AccessREAD_WRITE
RequiredNo, defaults to none.

Optional result processor. Probably one of sql-results-bean or sql-results-sheet.

services

AccessREAD_ONLY

successfulSQLCount

AccessREAD_ONLY

The number of SQL statements successfully executed.


Examples

Example 1

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.

Example 2

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>


(c) R Gordon Ltd 2005 - Present