CallableStatementBatch: Stored Procedures in Batch Mode

The JDBC Iterator executes stored procedures via JDBC callable statements with dynamical input data, if they are specified with the following input XML structure. Only SQL statements are feasible that do not return any ResultSet, for example no queries with a SELECT.

Input

HTML/XML

<any element name>
   <callablestatementbatch resulttag="result element name" timezone="timezone" format="date format">
      <sql>  
        <!--SQL statement -->
      </sql>
      <columns>
         <column mode="mode" name="any name" index="placeholder" type="data type" timezone="timezone" format="date format">
         </column>
      </columns>
      <rowset>
         <row>
            <Name><!--value--></Name>
         </row> 
      </rowset> 
   </callablestatementbatch> 
</any element name>

The following rules apply to each <callablestatementbatch/> element:

The optional attribute timezone specifies a time zone according to java.util.TimeZone, e. g. GMT+2; see http://docs.oracle.com/javase/1.5.0/docs/api/java/util/TimeZone.html.
The optional attribute format specifies a data format according to java.text.SimpleDateFormat.
It contains at least one <sql/> element.

If you use characters within the SQL statement or for the values or data of the table columns that have a meaning in XML, mask the SQL statement with surrounding CDATA block, e.g. <![CDATA[ SELECT * FROM TABLE WHERE ID<1 ]]> or <rowset><row><name><!CDATA[ XML character “<“ in CDATA element]]></name></row></rowset>.

This ensures that the characters in the CDATA element are not interpreted as XML, but as data for the adapter.

The following rules apply to each <column/> element:

It can be used as often as required (0-n times)
The optional attribute mode indicates the “direction” of the data stream (in: Input parameter for the database; out: Result parameter from the database; inout: Both input parameter and result parameter; see http://download.oracle.com/javase/1,5.0/docs/guide/jdbc/getstart/callablestatement.html); Default: in.
The attribute name specifies an arbitrary name for a parameter.
The attribute index specifies an integer placeholder index for the parameter defined within the attribute name.
The optional attribute type specifies the data type (allowed data types see note below; default: VARCHAR).
The attribute name specifies an arbitrary name for a parameter.
The optionale Attribut timezone specifies a time zone according to java.util.TimeZone, e.g. GMT+2, see http://docs.oracle.com/javase/1.5.0/docs/api/java/util/TimeZone.html.
The optional attribute format specifies a date format according to java.text.SimpleDateFormat an.

The following rules apply to each <rowset/> element:

It can be used as often as required (0-n times).
It contains any number of <row/> elements controlling the statements' execution.

The following rules apply to each <row/> element:

It can contain any number of elements with the column name or the column alias set in the SQL statement.
The elements inside contain the respective value.

Please note

The attributes mode and format are required parameters up till X4 Server Revision 31469; in newer version they are optional.
Allowed data types for the attribute type when setting (input parameter) and reading (ResultSet) values are: BIGINT, BINARY, BIT, BOOLEAN, BLOB, CHAR, CLOB, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, LONGNVARCHAR, LONGVARBINARY, LONGVARCHAR, NCHAR, NCLOB, NULL, NUMERIC, NVARCHAR, REAL, SMALLINT, SQLXML, TIME, TIMESTAMP, TINYINT, VARBINARY and VARCHAR (default), XML (alias for SQLXML).

Example

HTML/XML

<Rootelement>
   <callablestatementbatch resulttag="abc" timezone="MEZ" format="dd.MM.yyyy">
      <sql>  
      <![CDATA[ {call getData(?)} ]]>
      </sql>
      <columns>
         <column mode="out" name="id" index="1" type="INTEGER">
         </column>
      </columns>
      <rowset>
         <row>
            <id>TestUser</id>
         </row>
      </rowset>
   </callablestatementbatch>
   <callablestatementbatch resulttag="abc" timezone="MEZ" format="dd.MM.yyyy">
      <sql>  
      <![CDATA[ {call getData(?)} ]]>
      </sql>
      <columns>
         <column mode="out" name="id" index="1" type="INTEGER">
         </column>
      </columns>
      <rowset>
         <row>
            <id>TestUser</id>
         </row>
      </rowset>
   </callablestatementbatch>
</Rootelement>

Output

If the batch statement was executed successfully, the database connection element outputs the following XML structure:

HTML/XML

<Data>
   <result element name>
      <!--status of the batch operation-->
   </result element name>
</Data>

The following status outputs of the batch operation are possible:

(number of rows) rows affected. or
SUCCESS_NO_INFO or
EXECUTE_FAILED