NamedPreparedStatementBatch: SQL with Named Parameters in Batch Mode 1

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

Input

CODE

<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<]]><id type="INTEGER" /> 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 can contain at least one elements with a name that corresponds to the parameter name in
the SQL statement.
The elements inside contain the corresponding value.

For each data element within <row/> make sure to avoid unintended line breaks within the element
value, 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 und VARCHAR (Standard), XML (alias für SQLXML).

Example

CODE

<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:

CODE

<Data>
	<result-element-name count="Number of affected database entries">
		<!--status of the batch operation-->
	</result-element-name>
</Data>

The following status outputs are possible for the batch operation:

rows affected or
SUCCESS_NO_INFO or
EXECUTE_FAILED