The Database LookUp filter enables information to be retrieved from JDBC accessible data sources and inserts that information into the message in accordance with its message properties.
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 Lookup 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.
The Database LookUp filter is generally used to retrieve individual values or a group of values from a database, based on values from the incoming message. The retrieved values are then inserted directly into the message. For example, a Database Lookup filter may be used to retrieve the full name and address of a patient from a database, when the incoming message contains only the patient identifier. 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.
Testing Filter Configurations
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 |
Database |
The type of database to connect to:
|
Host |
The hostname/IP address of the database to which to connect. This property is available only if Database is not set to set to |
Port |
The port number to connect to on the database. |
Database Name |
The name of the database. If This property is available only if Database is not set to |
Username |
The username to use to log in to the database. This property is available only if Database is not set to |
Password |
The password to use to log in to the database. This property is available only if Database is not set to |
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 Database is set to |
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 Type |
The input message type when no definition is specified. |
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 property not required to be set 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 |
Used 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. When a query timeout occurs, the underlying connection is re-tried three times. 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 result from one of the conditions:
|
Retry Delay |
The delay (in milliseconds) between each retry attempt when a deadlock occurs. The maximum is |
| 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. When a socket timeout occurs, the underlying connection is re-tried three times. A value of zero indicates the timeout is not configured. |