User Guide > Process Steps and Components > Aggregator Step > JDBC WebRowSet Aggregator
Was this helpful?
JDBC WebRowSet Aggregator
JDBC WebRowSet Aggregator connects to a specified database, issues insert, update, and delete statements (based on the WebRowSet data), and iteratively writes the WebRowSet XML data to the database. The write XML formats are described by WebRowSet XML schema at the following link:
https://docs.oracle.com/javase/7/docs/api/javax/sql/rowset/WebRowSet.html
Insert, update, and delete statements have their own schema format (available at this link). The component only accepts the format described by XML schema.
To use this aggregator, you must have the following skill set:
• Database administration
• WebRowSet implementation
• Java Database Connectivity (JDBC) knowledge
To connect to a database using JDBC WebRowSet Aggregator, you must provide information about the required type JDBC drivers, database connection values, and the location of the batch response log file. In addition, the relevant type JDBC-compliant driver JAR files must be available on the computer where the process runs that uses this aggregator. For example, to use IBM DB2, you must have db2cc.jar on your computer.
This aggregator supports the following databases (type 4 driver only):
• Oracle
• IBM DB2
• PSQL
• Microsoft SQL Server
JDBC WebRowSet Aggregator performs the following actions based on the processes that you define:
• Reads the connection information, loads the drivers, and connects to the target database using the host URL, user ID, and password
• Begins a transaction or batch update process
• Parses the batch request and performs an insert/update/delete on the database. This action is performed for every transaction in the batch.
• Writes the transaction status to the batch response log file
• Ends the transaction or batch update process
• Disconnects from the target database
Tip...  To submit SELECT statements to a database, use JDBC WebRowSet Iterator.
JDBC WebRowSet Aggregator Properties
You can specify the following properties when you create an instance of this aggregator component in the Configuration tab > Message Components section.
Property
Description
Log File Location
Log file location where the transaction status for the entire session is written.
Driver
JDBC driver to load.
For example, com.domain.jdbc.v2.Driver
Host URL
URL connection to the data base.
For example, jdbc:domain://myuser/demodata
User ID
User name for the database session.
Note:  Provide the user name and password for the database session only if it is configured for your database.
Password
Password for the database session.
Note:  Provide the user name and password for the database session only if it is configured for your database.
Jar Location
Location of the folder that contains the JAR files that are required to load the JDBC driver. JDBC WebRowSet Aggregator loads the drivers based on this location.
Note:  You cannot append multiple paths to the value. All the driver JAR files must be located in the same folder.
For example, for PSQL, you must load the pvjdbc2.jar and pvjdbc2x.jar files.
Caution!  Always use the latest .jar files for a particular database version.
Global Transaction
Select this option to set the aggregator instance as global.
Supported Actions
Action
Description
PutMessage
Sends the WebRowSet XML data contained in a DJMessage Object to the database. The maximum size limit for DJMessage is 512 MB.
During a database session, the WebRowSet XML is sent to the specified database that is associated with the step. The WebRowSet XML document contains the following:
• The primary key
• The data to insert, update, or delete in the specified table in the WebRowSet
• The SQL SELECT statement to specify the table
GetMessage
Returns the log contents of the previously executed PutMessage action. This is optional.
Connect
Opens a persistent connection to the database
Disconnect
Closes an existing database connection and performs cleanup actions.
Tip...  Call Connect and Disconnect explicitly in your process flow.
BeginTransaction
Starts an implicit transaction for the session that can be committed or rolled back. This is optional.
CommitTransaction
Commits any SQL statements that have been sent for insert/update/delete operations made on the database using the WebRowSet XML data.
RollBackTransaction
Rolls back any SQL statements that have been sent to the database using the WebRowSet XML format. You must specify the session to associate with the RollBackTransaction action.
Supported Action Parameters
Action
Parameter
Parameter Description
PutMessage
Message
Name of the message to put.
GetMessage
Message
Name of the message to get.
Connect
-
-
Disconnect
-
-
BeginTransaction
-
-
CommitTransaction
-
-
RollBackTransaction
-
-
Supported Action Properties
Action
Property
Property Description
PutMessage
Ignore Update Errors
Indicates whether the batch update operations must be stopped on the occurrence of the first error. The options are:
• TRUE - Batch update operations continue regardless of the SQL errors that may occur during the current batch request.
• FALSE (default) - Batch update operations stop at the occurrence of the first error during the batch request.
• This option applies only to the errors or SQL exceptions that occur while running JDBC statements.
GetMessage
-
-
Connect
-
-
Disconnect
-
-
BeginTransaction
-
-
CommitTransaction
-
-
RollBackTransaction
-
-
Handling Null and Empty Tags in WebRowSet XML
If your WebRowSet XML file contains null values, you must modify it to include them as one of the following:
<columnValue><null/></columnValue>
<<columnValue xsi:nil='true'></columnValue>
If you have specified xsi:nil=true and have included a column value, then xsi:nil takes preference and a null value is inserted.
String Data Type - If your WebRowSet XML data contains an empty string (specified as either <columnValue></columnValue> or <columnValue><emptyString/></columnValue>), then an empty string is inserted in the database for Character, VARCHAR, and CLOB data types.
Numeric, Date, Timestamp, Time, Boolean, Bit Data Types - If your WebRowSet XML data contains the following, then a value of null is inserted for that column.
<columnValue></columnValue>
<columnValue><emptyString/>
</columnValue>
Errors
The following table lists the various error conditions that are specific to JDBC WebRowSet Aggregator.
Code
Name
Description
Reason
8
ERR_WRITERR
Error during the execution of the SQL statements
ERR_WRITERR is returned when Ignore Update Errors= false and an exception (StopOnErrorException) occurred when running the SQL statements.
This error code denotes the error that occurred during the execution of the SQL statements constructed out of a WebRowSet batch request.
This error code is also returned when there is an exception at the time of writing the batch response log to the log file.
The methods returning this error code are GetMessage, Connect, Disconnect, and PutMessage.
99
COMPLETED_WITH_ERROR
Error when a batch request failed
COMPLETED_WITH_ERROR is returned when Ignore Update Errors="true" and an exception occurred when running a SQL statement.
This error indicates that the particular batch request has failed.
The PutMessage method returns this error code.
27
ERR_CLOSERR
Error when disconnecting from the database
ERR_CLOSERR is returned when an exception is encountered at the time of disconnecting from the given database.
The Disconnect method returns this error code.
19
ERR_OPENERR
Error when connecting to the database
ERR_OPENERR is returned when an exception is encountered while connecting to the target database.
The Connect method returns this error.
98
ERR_TRANSACT
Error during transaction sequence
ERR_TRANSACT is returned when an exception occurs while the beginTransaction, CommitTransaction, or RollbackTransaction methods are run by JDBC WebRowSet Aggregator component.
34
ERR_INVALID
Error while parsing the WebRowSet XML document
ERR_INVALID is returned when an exception occurs while parsing of the WebRowSet XML document.
Possible causes:
• Invalid XML element
• Invalid formats.
Examples:
• Specify a date value in a format other than yyyy-mm-dd.
• Enter an alphanumeric value for an integer value.
Use this error code to either commit or rollback that particular transaction depending on your requirements.
The PutMessage method returns this error code.
The batch response log file contains all the records leading up to (but not including) the record that returned ERR_INVALID.
96
ERR_PK_UNDEFINED
Error when a primary key is not defined in the WebRowSet XML document
ERR_PK_UNDEFINED is returned when there are no primary keys defined in the WebRowSet XML request.
The PrimaryKeyNotDefinedException is thrown in this case.
The PutMessage method returns this error code.
44
ERR_NOTSUP
Error when the WebRowSet XML request contains unsupported data types
ERR_NOTSUP is returned when the WebRowSet XML request contains unsupported data types like binary data.
The UnSupportedDataTypeException is thrown in this case.
JDBC WebRowSet Aggregator checks the column type present in the metadata section to confirm if the data type is binary.
The PutMessage method returns this error code.
Batch Response Logging
JDBC WebRowSet Aggregator provides a batch-logging feature.
If the PrimaryKeyNotDefinedException and UnsupportedDataTypeException occur before updating the database, then JDBC WebRowSet Aggregator does not log the transaction status for the batch request.
If no exceptions occur while updating the database, then the component writes the transaction status to the log file. The log file records the entire session regardless of whether you perform a commit or rollback on a transaction or batch.
If you want the log file contents created at runtime for every batch request, call the GetMessage action. The GetMessage action returns the log file contents of the previously executed PutMessage method.
Unsupported Data Types
The following table lists the data types that JDBC WebRowSet Aggregator does not support.
Database
Data Type
Oracle
• Long Raw (Binary)
• Raw (Binary)
• MLSLabel
• Blob (Binary)
• BFile
• URowId
• URIType
DB2
• BLOB (Binary)
SQLServer
• Binary
• Image (Binary)
• VarBinary
PSQL
• Binary
• LongVarBinary
Last modified date: 02/01/2024