Database Message Extraction

The Database Message Extraction filter provides the ability to replace the body of a message with an XML message generated from some database queries.

Because the configuration of a database filter is flexible and potentially very complex, the Database Configuration is used to configure how it interacts with the database.

After you have added a new Database Message Extraction filter to your route:

Read the Caveats related to using this filter.
Configure the database connection using its Configuration Properties.
Configure the message definition (if any) to use for messages processed by this filter.
Launch the Database Configuration by clicking the Create Configuration link.

This filter operates in a similar manner to the Database communication point when it is running in input mode. The difference lies in the fact that the Database communication point polls a database regularly to detect changes using a static query, whereas the database message extraction filter can perform a dynamic query using values from the incoming message. Refer to Database Components Best Practice Guide for guidance on the usage of database queries.

Caveats

Losing Database Connection

When the database connection used by a database filter is lost, messages that the filter was attempting to process during the outage are sent to the Error Queue. The database connection is re-attempted three times for every incoming message. When the database connection is re-established, the filter recovers automatically and begins to process new messages. Consequently, when you manually reprocess the messages in the Error Queue, FIFO ordering of the messages is likely to be lost.

In Rhapsody versions previous to Rhapsody 6.2.3, the database filters are not able to recover automatically if the connection is lost during an insert or update query. In these circumstances, a manual route restart is required and users have the opportunity to reprocess the messages in the Error Queue before restarting the route, thereby preserving FIFO ordering. The database filters automatically recover if the connection is lost during select queries or before the insert or update queries are started.

If FIFO ordering of messages is critical for your purposes, it is recommended you use the Database communication point.

CLOB Support

The Database Message Extraction filter supports CLOB, which only supports ASCII character sets, not multi-byte character sets.

Result Substitution Not Supported

The Database Message Extraction filter does not support Result Substitution .

Empty Query Results

The Database Message Extraction filter replaces the body of the incoming message with the result of the query executed by the filter. When the query returns no result, the filter sends the message to the Error Queue. To prevent the filter from sending messages with empty query results to the Error Queue, the <allowEmptyResult/> tag should be added manually to XML file in the Configuration File property in the filter's configuration properties.

Testing Filter Configuration

When using third party JDBC drivers (such as MySql or SQLite), you cannot test the configuration of database filters using the in-built filter testing functionality, because it cannot create the respective database driver classes. To test your filter configuration, start the relevant route and generate the test message using an appropriate input communication point, for example: the Directory or Timer communication point.

Configuration Properties

Refer to Database Configuration and Database Component Configuration Reference for details on configuring the filter.

Property	Description
Maximum Concurrency	The maximum level of concurrency for this filter. A setting of `0` (zero) means unlimited. Limiting the level of concurrency limits memory usage. Refer to Maximum Concurrency for details.
Database	The type of database to connect to: `Microsoft SQL Server - Microsoft Driver` - the com.microsoft.sqlserver.jdbc.SQLServerDriver is used to enable access. `Microsoft SQL Server - jTDS` - the net.sourceforge.jtds.jdbc.Driver is used to enable access. `Oracle` - the oracle.jdbc.OracleDriver is used to enable access. `MySQL` - you must download the MySQL JDBC driver (com.mysql.jdbc.Driver) and attach it to the communication point as an auxiliary file. If you want to use the MySQL Connector/J driver (com.mysql.cj.jdbc.Driver), then you must select `Manual Setting`. `Microsoft Access` - the sun.jdbc.odbc.JdbcOdbcDriver is used to enable access. `PostgreSQL` - the org.postgresql.Driver is used to enable access. `Manual Setting` - the JDBC driver and database URL must be included in the configuration file. Refer to Database Drivers and URLs for details.
Host	The hostname/IP address of the database to connect to. This property is available only if the Database property is not set to `Manual Setting`.
Port	The port number to connect to on the database.
Database Name	The name of the database. If `Microsoft Access` is selected for the Database property, the name of the data source should be provided. This property is available only if the Database property is set to `Manual Setting`.
Username	The username with which to log in to the database. This property is available only if the Database property is not set to `Manual Setting`.
Password	The password with which to log in to the database. This property is available only if the Database property is not set to `Manual Setting`.
Additional Connection Properties	A list of name/value pairs of message properties to be set on database connection. The listed properties are not used when the Database property is set to `Manual Setting`.
Configuration File	This is an XML file that controls how the filter interacts with the database. While the XML can be edited manually, the recommended approach is to click the Create Configuration or Edit Configuration links to launch the Database Configuration Editor.
Message Body Column	The Message Body Column Overrides the normal XML message format and uses the specified column as the message body: If the message body is contained in a single field in the database, enter the name of the database column that contains the message body. Rhapsody will load the value in this column into the message body instead of creating an XML document based on the query. Fields defined in the query file are added to the new message as message properties. If the column specified does not exist, an error is generated. If the configuration returns multiple rows, only the first row is retrieved and used for message properties. If this property is empty, the message body is returned as an XML document constructed from the query result set.
Message Type	The message type when no definition is specified: `Plain Text` (default). `XML`.
Definition	The message definition file that describes the structure of the incoming and outgoing messages. Specifying a message definition allows the filter to access fields inside the message. This can be used to read or edit information in the message. The message definition is required if the database configuration references specific fields on the message. This is not required if no content extraction/insertion is to be performed (property-only lookups).
Retry Attempts	The number of retry attempts if a database deadlock occurs.
Query Timeout	Use the query timeout option to specify how long, in seconds, a remote operation can take before the procedure times out. The default value is 0. The timeout should be applied to all SQL/procedure queries in the configuration file. It is important to set a suitable timeout to ensure that queries do not block indefinitely as this would manifest as a hang in Rhapsody, which could block either a communication point thread or a route executor thread. Long queries could occur due to one of the following: Performance issues within the database. Poor database configuration such as lack of indexing. Poor database query design. Locks on database resources by another application or deadlocks within the Database.
Retry Delay	The delay (ms) between each retry attempt when deadlock occurs. The maximum is `5000ms`.
Socket Timeout	Specifies how long, in seconds, the underlying connection should remain open before timing out. The socket timeout prevents Rhapsody from waiting indefinitely for results from a database in the event Rhapsody loses its connection to the database. A value of zero indicates the timeout is not configured.