Database

The Database communication point enables information to be accessed from or saved to a database. It allows complex database queries to be arbitrarily executed by Rhapsody to insert, update, or modify an existing database.

Supported Operational Modes: Input, Output, Out->In (In->Out is not supported).

The Database communication point only supports one connection when in Input mode. Increasing the number of connections through the Connection tab of the communication point's Properties dialog does not have any effect when using Input mode.

Configuration Properties

The configuration of a Database communication point is flexible and therefore potentially complex. Refer to:

Database Configuration and Database Component Configuration Reference for details on configuring this communication point's interaction with the database.
Database Components Best Practice Guide for guidance on the usage of database queries.

To create a new Database communication point:

Create the Database communication point connection.
Configure the message definition to use for messages processed by this communication point, if required.

Message definitions cannot be used with input Database communication points. Message definitions are only required for output database communication points if the database configuration needs to extract fields within an incoming message.
Create the database configuration files used within the Rhapsody Engine from the Database Configuration Editor. To launch the Database Configuration Editor, click the Create Configuration link.

Input Mode

A database communication point in input mode is used to poll a database table to detect changes. When changes are detected, the communication point generates one or more XML messages based on the SQL queries configured. These messages are passed into the route for processing. Refer to Properties for XML Message Generation for details.

Rhapsody does not take on responsibility for the query results until they are written to a route. It is recommended that you design queries that return a row at a time (or a small number of rows at a time) with an appropriately structured key value. If the input query returns a large number of rows, the query may be impacted by external factors. Ideally, the query to relate to a single message.

The input polling options for a Database communication point are set using the Database Polling Options dialogue in the Database Configuration Editor. Refer to Database Polling for details.

Additional options which allow some customization of the generated XML message are also available by setting the Configuration Options in the editor.

Input Mode Properties

The properties for the Database communication point in Input mode are as follows:

Property	Description
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 only available if Database is not set to `Manual Setting`.
Port	The port number to connect to the database. This property is only available if Database is not set to `Manual Setting` .
Database Name	The name of the database. If the database selected is `Microsoft Access`, the name of the data source should be provided. This property is only available if Database is not set to `Manual Setting` .
Username	The username for database access. This property is only available if Database is not set to `Manual Setting` .
Password	The password for database access. This property is only available if Database is not set to `Manual Setting` .
Additional Connection Properties	A list of name/value pairs of message properties to be set on the database connection. The listed properties are not used when Database is set to `Manual Setting`.
Configuration File	This is an XML file that controls how the communication point interacts with the database. While the XML can be edited manually, the recommended approach is to use the Database Configuration Editor. To launch the Database Configuration Editor, click the Create Configuration link (if a configuration has not yet been created) or the Edit Configuration link (if a configuration exists) in the Configuration File field. Alternatively, to add an existing configuration to a database: Select the Browse button in the Configuration File field to display the Windows® Open dialog. Search for and add the configuration file to the communication point. To update the configuration file, click the Edit Configuration link. The Database Configuration Editor is then displayed, which enables you to edit the configuration file.
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 reasons: 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.
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.
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.

Output Mode

A database communication point running in Output mode is used to run arbitrary database queries or operations which can be based on data from the incoming message. It is frequently used to insert or update data retrieved from a message.

The properties for the Database communication point in Output mode are similar to those for Input mode, with the following differences:

Property	Description
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 only available if Database is not set to `Manual Setting`.
Port	The port number to connect to the database. This property is only available if Database is not set to `Manual Setting`.
Database Name	The name of the database. If the database selected is `Microsoft Access`, the name of the data source should be provided. This property is only available if the Database is not set to `Manual Setting`.
Username	The username for database access. This property is only available if Database is not set to `Manual Setting`.
Password	The password for database access. This property is only available if Database is not set to `Manual Setting`.
Additional connection properties	A list of name/value pairs of message properties to be set on the database connection. The listed properties are not used when Database is set to `Manual Setting`.
Configuration File	An XML file that controls how the communication point interacts with the database. While the XML can be edited manually, the recommended approach is to use the Database Configuration Editor. To launch the Database Configuration Editor, click the Create Configuration link (if a configuration has not yet been created) or the Edit Configuration link (if a configuration exists) in the Configuration File field. Alternatively, to add an existing configuration to a database: Select the Browse button in the Configuration File field to display the Windows® Open dialog. Search for and add the configuration file to the communication point. To update the configuration file, click the Edit Configuration link. The Database Configuration Editor is then displayed, which enables you to edit the configuration file.
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. 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.
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.
Message Type	Identifies the output message type when no definition is specified: `Plain Text` (default) - the communication point should not try to parse the message at all. `XML` - the communication point should try parsing the incoming message using standard XML (and allow XPaths to access the fields).
Definition	Identifies the message definition used to parse a message. The Database communication point can be configured to build SQL queries based on values retrieved from a message. If this is done, a message definition is required so the message can be parsed.
Message Errors	Enables the Database communication point to treat message errors as connection errors: `Treat as Message Error` (default) - message errors are treated as message errors, resulting in the message being re-routed via the communication point's error output connector, or if the error output connector is not connected to other components, to the Error Queue. `Treat as Connection Error` - message errors are treated as if they were connection errors. As a result, the communication point connection retries and retries per message functionality is used to determine the consequent action, as per the settings in Connection Retries of the Connection tab of the communication point's properties.
Treat Deadlock as Connection Error	Determines whether a database deadlock is treated as a connection error or as a message error. Enabled (default) - a deadlock is treated as a connection error, the retry mechanism is invoked and the message is resent. Disabled - a deadlock is treated as a message error and results in the message being sent to the error connector of the communication point (if connected) or the Error Queue. This property is disabled if Message Errors is set to `Treat As Connection Error`.
Treat Query Timeout as Connection Error	Determines whether a query timeout is treated as a connection error or as a message error. Enabled (default) - a query timeout is treated as a connection error, the retry mechanism is invoked and the message is resent. Disabled - a query timeout is treated as a message error and results in the message being sent to the error connector of the communication point (if connected) or the Error Queue. This property is disabled if Message Errors is set to `Treat As Connection Error`.

Out->In Mode

The Database communication point in Out->In mode functions in a similar way to Output mode, except the message is returned to the communication point once all queries have completed. When in this mode, the Database communication point operates in a similar manner to the Database Lookup filter. However, it is preferable to use the Database communication point if you are changing the database.

The properties for the Database communication point in Out->In mode are a combination of those described above. Refer to Out->In Properties for general details on a communication point's Out->In modes.