JDBC Iterator

The adapter connects to a JDBC-compatible database and executes one or multiple database statements handed over as input XML document with an adapter-specific structure. The iterator retrieves and provides the data for processing block by block. The database connection remains open until all data has been retrieved.

The connector requires a JDBC datasource that has been deployed within the application server and for which the connection URL, credentials, schema and transaction behavior etc. are defined. For further information on deploying JDBC datasources consult your application server's documentation.

Properties

Operation

Defines the operation executed by the function adapter

Possible values:

Iterate: Read the amount of data from the database depending on the value specified within the parameter fetchSize and output it in a result document
Clean: Reset the iterator of a JDBC Iterator, which has the same ID as the desired JDBC Iterator with the operation Iterate (see parameter id)

Parameters

Adapter	Main adapter class (do not change!) Possible values: de.softproject.integration.adapter.jdbc.JdbcIterator: Main class (Default)
JndiName	JNDI name of the DataSource If the connection is established via the JNDI name, the parameter `connectionURL` must not be specified. Possible values: Valid JNDI name (e. g. `java:/DefaultDS`)
ColumnCase	Controls how column names are taken over into the result. Since databases have different behaviors regarding the upper and lower case spelling of columns, the processing of the result can be specified here regardless of the database's behavior. Possible values: `NO_CHANGE`: Does not change the column names, i. e. the column names will be written to the result document as returned by the database (Default) `TO_LOWER`: Changes the column names to lower case `TO_UPPER`: Changes the column names to upper case Column names, which are not legal element names (`NCNAME`) are converted to `_<number>` (e. g. `_2` ) where `<number>` is the column index.
connectionURL	Connection URL for the JDBC database If the connection is established via the connection URL, the parameter `JndiName` must not be specified. Possible values: The URLs structure depends on the database to which a connection is to be established. Example for sqlite: `jdbc:sqlite:C:/Temp/mydatabase.db`
user	User name for authentication via the parameter `connectionURL`
password	Password for authentication via the parameter `connectionURL`
CommitMode	Defines the version to be used for commits Possible values: `NONE`: Do not execute any commit `AUTO`: Automatic commit according to the JDBC API (i. e. after each batch) (Default) `STATEMENT`: Explicit commit after each statement (i. e. after all batches for this statement) `DOCUMENT`: At the end of the document (i. e. after executing all statements) `MANAGED`: Use adapter in transactions (for externally managed transactions)
AddMetaDataForQueries	Controls whether database metadata, see JDBC Metadata Explorer, should be output for each result set within the adapter's result XML document, if the adapter executes an SQL query (not available for `UPDATE, DELETE` etc.). This function is neither available in batch mode (`StatementBatch, PreparedStatementBatch`, and `CallableStatementBatch`). Possible values: `true`: Add database metadata to result document `false`: Output result document without database metadata (Default)
fetchSize	The number of results per query can be limited by entering a positive integer. If there is data available, the status `1` is returned, if the result is empty, the status `0` is output. If the entered number is smaller than `1`, the complete data will be fetched.
id	Unique ID of the JDBC Iterator component within the process context, respectively the process instance (if you want to use several JDBC Iterators that iterate regardless of each other within the same process context); Possible values: `0`: Use current action ID (number of the JDBC Iterator's process step within the current process) as ID. Please note, that a JDBC Iterator may have the same action ID within a subprocess (default) Any integer except `0`, in order to mark the corresponding JDBC Iterator component within the process context clearly. Recommendation: use a value `>1000` to avoid using an already existing action ID.

Status values

1

The query has returned data records or the instruction has changed data records

The status 1 is returned, if the document contains multiple queries or instructions and at least one query has delivered or changed a result. The adapter returns this status as long as data blocks are still available for processing.

0

The query did not return any results or the statement hasn't changed any data records

The status 0 is returned, if the document contains multiple queries or instructions and no query has returned or changed a result.

-1

Technical error: more information on the error cause can be found in the server log

Input

The JDBC Iterator excepts specific input XML structures containing database statements. These input XML structures can be generated dynamically by using XSL mappings that depend on the processed XML data.

Note:

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=100 ]]> 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.