sql

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.

Property Summary

Example Summary

Property Detail

If the statement calls a stored procedure.

The connection to use.

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.

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.

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

Set escape processing for statements.

The number of SQL statements executed.

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

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

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

A name, can be any text.

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

Parameters to be bound to statement(s).

Optional result processor.

The number of SQL statements successfully executed.

Examples

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>

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>