PreparedStatementBatch: SQL mit Werten im Batch-Modus

In batch mode, the JDBC Connector executes multiple SQL statements at the same time, if they are deposited in the following input XML structure. In this case, only SQL statements are possible, which do not return a ResultSet, i.e. no queries with SELECT.

With X4 BPMS release 5.0 or higher, you can alternatively execute SQL statements in batch mode using named parameters, see NamedPreparedStatementBatch: SQL with Named Parameters in Batch Mode 1.

Input

HTML/XML

<any element name>
   <preparedstatementbatch resulttag="result element name" timezone="timezone" format="date format">
      <sql>  
         <!--SQL statement-->
      </sql>
      <columns>
         <column mode="in" name="any NC name" 
          index="placeholder index" type="data type" 
          timezone="time zone" format="date format">
         </column>
      </columns>
      <rowset>
         <row>
            <Name><!--value--></Name>
         </row> 
         <!-- Multiple <row> elements for batch mode -->
      </rowset> 
   </preparedstatementbatch> 
</any element name>

The following rules apply to each <preparedstatementbatch/> 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 date 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[ UPDATE PROCESS SET X4_VERSION=4 WHERE X4_DURATION=? ]]> 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; default: in.
The attribute name specifies any NC 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 (e. g. INTEGER or BLOB; Default: VARCHAR).
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.

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>
   <preparedstatementbatch resulttag="result-element-name" timezone="MEZ" format="dd.MM.yyyy">
      <sql>  
      <![CDATA[ UPDATE PROCESS SET X4_VERSION=4 WHERE X4_DURATION=?]]>
      </sql>
      <columns>
         <column mode="in" name="id" index="1" type="INTEGER">
         </column>
      </columns>
      <rowset>
         <row>
            <id>23</id>
         </row>
         <row>
            <id>25</id>
         </row>
      </rowset>
   </preparedstatementbatch>
</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 are possible for the batch operation:

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