User Guide > Process Steps and Components > Iterator Step > JDBC WebRowSet Iterator
Was this helpful?
JDBC WebRowSet Iterator
The JDBC WebRowSet Iterator connects to the specified database, runs the provided SQL SELECT statement, recurses through the row sets, and returns the WebRowSet XML in a DJMessage object. The maximum size limit for DJMessage is 512 MB.
Note:  Use JDBC WebRowSet Iterator only for reading from a table and not for running INSERT/UPDATE/DELETE statements on a database. Use JDBC WebRowSet Aggregator to perform update/insert/delete operations on a database. For more information, see JDBC WebRowSet Aggregator.
To connect to a database using JDBC WebRowSet Iterator, you must provide information about the required type 4 JDBC drivers, database connection values, and the number of records per rowset.
The component performs the following actions:
1. Reads the connection information, loads the drivers, and connects to the target database using the Host URL, User ID, and password.
2. Connects to the target database and runs the provided SQL SELECT query.
3. Returns the specified number of records per rowset as specified in the transfer batch size.
4. Disconnects from the target database.
The main features of JDBC WebRowSet Iterator are as follows:
• Supports the following databases (type 4 drivers only) when used with the appropriate local libraries:
– Oracle
– IBM DB2
– PSQL
– Microsoft SQL Server
• Queries the database based on the SELECT statement provided.
• Recurses through the result set and outputs it in the WebRowSet XML format.
You must have the following skill set to use JDBC WebRowSet Iterator:
• Database administration
• Java Database Connectivity (JDBC) knowledge
• Scripting
JDBC WebRowSet Iterator Properties
 
Property
Default Value
Description
Transfer Batch Size
1000
Number of records returned per rowset.
Decimal values are rounded off to the nearest integer.
Note:  If you specify the value as 0, then XPath returns all the records in the rowset.
For example, if you set 500 as the Transfer Batch Size for a 1000 record rowset, then the number of WebRowSet XML responses returned is 2.
Null Support
None
Handles null and empty string values.
If you set Null Support to None, then empty strings and null values are written as <columnValue></columnValue>.
If you set Null Support to "null tag", then the following actions apply:
• Null values are written as <columnValue><null/></columnValue>
• Empty strings are written as <columnValue><emptyString/></columnValue>
If you set Null Support to "nil attribute", then the following actions apply:
• Null values are written as <columnValue xsi:nil='true'></columnValue>
• Empty strings are written as <columnValue></columnValue>
Driver
 
Name of JDBC driver to load
For example: com.ibm.db2.jcc.DB2Driver
Host URL
 
URL connection to the database
For example: jdbc:db2://localhost/mydbname
User ID
 
User name for the database session
For example: db2user
Note:  Specify the user name and password for the database session only if it is configured for your database.
Password
 
Password for the database session
For example: mydb2pwd
Jar Location
 
Location of the folder that contains the JAR files that are required to load the JDBC driver.
Note:  You cannot append multiple paths to the value. All the driver JAR files must be located in the same folder.
For example: For DB2, you must load the db2cc.jar (type 4 driver).
Caution!  Always use the latest .jar files for a particular database version.
Supported Actions
Action
Description
Connect
Opens a persistent connection to the database.
Tip...  Call Connect explicitly in your process flow.
Disconnect
Closes an existing database connection and performs any clean up actions if required.
Tip...  Call Disconnect explicitly in your process flow.
GetMessage
Iterates through the records in the result set, and returns the specified number of records (Transfer Batch Size) in the WebRowSet XML format.
The GetMessage requires the DJMessage object that holds the WebRowSet XML output. Call GetMessage immediately after PutMessage to iterate over the record set.
The maximum size limit for DJMessage is 512 MB.
PutMessage
Runs the SQL Select statements, processes the values, and stores the result set.
The Put Message requires the DJMessage object that holds the SQL SELECT statement. The maximum size limit for DJMessage is 512 MB.
Supported Action Parameters
Action
Parameter
Description
GetMessage
Message
Name of the message to get.
PutMessage
Message
Name of the message to put.
Connect
-
-
Disconnect
-
-
Supported Action Properties
There are no supported properties.
Errors
Error Code
Error Name
Description
Reason
4
ERR_READERR
Error while reading messages from a DJMessage body or iterating through the result set
ERR_READERR is returned in the following cases:
• Problem reading messages from a DJMessage body
• Problem iterating through the result set
8
ERR_WRITERR
Error while writing messages to a DJMessage body or when running the SELECT query
ERR_WRITERR is returned in the following cases:
• Problem writing messages to a DJMessage body
• Problem running the SELECT query
19
ERR_OPENERR
Error while connecting to the database
Exception is encountered when connecting to the specified database.
27
ERR_CLOSERR
Error while disconnecting from the database
Exception is encountered when disconnecting from the specified database
86
ERR_MSG_END
Error indicating the end of a given set of messages
Returned when there are no more messages to be returned by the "getMessage" method
Supported Data Types
Database
Data Types
DB2
• bigint
• character
• clob
• date
• decimal
• double
• integer
• real
• smallint
• time
• timestamp
• varchar
SQL Server 2000
• bigint
• bit
• char
• datetime
• decimal
• float
• int
• money
• nchar
• ntext
• numeric
• nvarchar
• real
• smalldatetime
• smallint
• smallmoney
• sql_variant
• text
• tinyint
• uniqueidentifier
• varchar
Oracle 9i
• Varchar2
• Char
• Number
• Integer
• Date
• Long
• NVarchar2
• RowId
• NChar
• Clob (Use the latest drivers)
• Nclob (Use the latest drivers)
• Float
• Char Varying
• Character
• Character varying
• Decimal
• Double Precision
• Int
• National char
• National char varying
• National character
• National character varying
• Nchar varying
• Numeric
• Real
• SmallInt
• Varchar
Unsupported Data Types
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