PreparedStatementBatch: SQL with values in batch mode

The JDBC Iterator executes multiple SQL statements at once in batch mode when they are placed in the following input XML structure. Only SQL statements that do not return a result set, i.e. no queries with SELECT, are possible.

As an alternative, as of Release 5.0 of the X4 BPMS, you can also execute SQL statements with named parameters with or without batch mode, see NamedPreparedStatementBatch: SQL with named parameters in batch mode.

Note on using CLOB fields:

When using the PreparedStatementBatch action , note that CLOB fields are processed only as character data.

The connector expects a direct text node as an input value for CLOB fields. If an XML structure is passed instead, the corresponding element does not contain any direct text content. In this case, no value is passed to the prepared statement so that no record is written to the database. This behavior occurs without an error message.

Recommendation

If XML content is to be stored in a CLOB field, it must be explicitly converted to text before being passed, for example, by using a CDATA section.

Example

CLOB column definition:

<column index="..." mode="in" name="..." type="CLOB"/>

Passing XML content as text using CDATA wrapper:

<INPUT>
  <xsl:text disable-output-escaping="yes"><![CDATA[</xsl:text>
  <Object>Test</Object>
  <xsl:text disable-output-escaping="yes">]]></xsl:text>
</INPUT>

Only if the XML content is present as a contiguous text node is it correctly passed to the prepared statement and stored in the database.

Input

<Beliebiger Elementname>
   <preparedstatementbatch resulttag="Ergebniselementname" timezone="Zeitzone" 
    format="Datumsformat">
      <sql>  
         <!-- SQL-Anweisung -->
      </sql>
      <columns>
         <column mode="in" name="frei wählbarer NCName" 
          index="Platzhalter-Index" type="Datentyp" 
          timezone="Zeitzone" format="Datumsformat">
         </column>
      </columns>
      <rowset>
         <row>
            <Name><!--Wert--></Name>
         </row> 
         <!-- Weitere <row>-Elemente für Batch-Modus -->
      </rowset> 
   </preparedstatementbatch> 
</Beliebiger Elementname>

For each <preparedstatementbatch/> element, the following applies:

The optional timezone attribute specifies a time zone according to java.util.TimeZone (see http://docs.oracle.com/javase/1.5.0/docs/api/java/util/TimeZone.html), for example GMT+2.
The optional format attribute specifies a date format according to java.textSimpleDateFormat.
It contains at least one <sql/> element.

If you use characters that have a meaning in XML within the SQL statement or for the values or data of the table columns, mask the SQL statement with a surrounding CDATA block, for example, <![CDATA[ UPDATE PROCESS SET X4_VERSION=4 WHERE X4_DURATION=? ]]> or <rowset> <row> <Name><!CDATA[ XML characters "<" 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.

For each <column/> element, the following applies:

It may be used as often as desired (0 – n times)
The optional mode attribute specifies the "direction" of the data stream (in: Input parameters for the database; out: Result parameters from the database; inout: both input parameters and result parameters; default: In.
The name attribute specifies a freely selectable name (NCName) for a parameter.
The index attribute specifies an integer placeholder index for the parameter defined in the name attribute.
The optional attribute type specifies the data type (e.g. INTEGER or BLOB; default: VARCHAR).
The optional timezone attribute specifies a time zone according to java.util.TimeZone (see http://docs.oracle.com/javase/1.5.0/docs/api/java/util/TimeZone.html), for example GMT+2.
The optional format attribute specifies a date format according to java.textSimpleDateFormat.

For each <rowset/> element, the following applies:

It can be used as often as desired (0 – n times).
It contains any number of <row/> elements that control the statement execution.

For each <row/> element, the following applies:

It can contain any number of elements that have the name of the table column or the column alias set in the SQL statement.
The elements contained therein contain the respective values.

Note the following

The mode and format attributes are mandatory parameters in X4 Server Revision 31469; from newer versions, they are optional.
Permissible data types for the type attribute for 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, ZERO, NUMERIC, NVARCHAR, REAL, SMALLINT, SQLXML, TIME, TIMESTAMP, TINYINT, VARBINARY, and VARCHAR (default), XML (alias for SQLXML).

Example

<Rootelement>
   <preparedstatementbatch resulttag="Ergebniselementname" 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 is executed successfully, the database connection block returns the following XML structure:

<Data>
   <Ergebniselementname>
      <!--Status der Batch-Operation-->
   </Ergebniselementname>
</Data>

The following outputs are possible as the status of the batch operation:

(Number of rows) rows affected. or
SUCCESS_NO_INFO OR
EXECUTE_FAILED