NamedPreparedStatementBatch: SQL with Named Parameters in Batch Mode

The JDBC Iterator executes multiple SQL statements in batch mode at once, if they are deposited in the following input XML structure, each within a <namedpreparedstatementbatch> block. Thereby, only SQL statements can be used that do not return a ResultSet, i.e. no queries with SELECT.

Input

HTML/XML

<any element name>
   <namedpreparedstatementbatch resulttag="result element name" timezone="time zone" 
    format="date format">
      <sql>  
         <!--SQL statement (optionally within a CDATA block) -->
         <!--Each parameter has an XML element with the same name as the corresponding <row> child element -->
      </sql>
      <rowset>
         <row>
            <Name><!--value--></Name>
         </row> 
         <!-- Further <row> elements for batch mode -->
      </rowset> 
   </namedpreparedstatementbatch> 
</any element name>

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

The optional timezone attribute 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 and defines the returned date values' formats.
It contains at least one <sql/> element.

The following rules apply to each named parameter XML element within element <sql/>:

The element name for the parameter is equivalent to the element name within the corresponding <row/> element.
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; default: in.
The optional attribute type specifies the data type, e. g. INTEGER, BLOB, or VARCHAR (default).
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 date format according to java.text.SimpleDateFormat and defines the expected value format of the corresponding <row> 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 <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 must contain at least one element corresponding to the element name in the SQL statement.
The elements inside contain the corresponding value.

Make sure to avoid unintended line breaks within the element content of each data element within <row/>, as all line breaks would be converted to \n in the generated SQL statement.

Please note

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>
	<namedpreparedstatementbatch resulttag="namedPreparedStatementBatchWithCData" 
     timezone="UTC" format="dd.MM.yyyy">
		<sql>
             <![CDATA[UPDATE PROCESS SET X4_VERSION=4 WHERE X4_DURATION=]]>
             <duration type="INTEGER" mode="inout" />
        </sql>
		<rowset>
			<row>
				<duration>101</duration>
			</row>
			<row>
				<duration>9</duration>
			</row>
		</rowset>
	</namedpreparedstatementbatch>
</Rootelement>

Output

If the SQL statements were executed successfully, the adapter outputs the following XML document:

HTML/XML

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

The following status outputs are possible for the batch operation:

(number of rows) rows affected or
SUCCESS_NO_INFO or
EXECUTE_FAILED