Database Insertion

The Database Insertion communication point is used for adding information into a database table or updating existing rows in a database table. It can be used in any situation where the user wants to insert or update one row into a database table for every message it receives, effectively fulfilling an archiving role.

Refer to Database Components Best Practice Guide for guidance on the usage of database queries.

Supported Operational Modes: Output.

Output Mode Properties

Property	Description
Definition	The name of the message definition file to use to extract the field values from the message to be inserted into the database. The message definition to use when parsing.
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 Database is not set to `Manual Setting`.
Port	The port number to connect to on the database. If you leave this field blank or set this to `0` (zero), the default port for that database type is used. This property is available only if Database is not set to `Manual Setting`.
Database Name	The name of the database. If Database is set to `Microsoft Access`, the name of the data source should be provided. This property is available only if Database is not set to `Manual Setting`.
JDBC Driver	The name of the JDBC driver class. This property is available only if Database is set to `Manual Setting`.
Database URL	The URL of the database to which to connect. This property is available only if Database is set to `Manual Setting`.
Username	The username for database access.
Password	The password for database access.
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 the Database property is set to `Manual Setting`.
Database Table	The name of the database table to insert information from each message into.
Column Values	The columns in the database table to insert into (when running in insertion mode), or the columns to be updated (when running in update mode). It also specifies the type and value of the data to insert into those columns. There are three columns in the column values dialog: Column Name, Value, and Type. Specify the column name in the database table you want to insert information into in the Column Name column. In the Type column, specify the type of information to be inserted. There are six information type choices: `Null` - inserts "null" into the database column; do not use the Value column. `Body` - inserts the full message body as a byte array into the database column. For Oracle® databases, the type of this column should be BLOB, and for Microsoft SQL Server databases it should be IMAGE. Do not use the Value column. `Timestamp` - inserts the current date/time as a timestamp; do not use the Value column. `Literal` - inserts the literal value specified in the Value column as a string; for example: `fred`. `Field` - inserts the value of the field from the message specified by the Value column. `Property` - inserts the value of the message property specified by the Value column. CLOB is not supported on this communication point.
Mode	Determines whether the communication point should run in insertion or update mode: `Insert` (default). `Update`.
Update Where Clause	When running in update mode this parameter specifies the column names and values used to build up the WHERE clause of the SQL UPDATE statement. If no columns are specified here, all rows in the database are updated. There are four columns for each parameter: Column Name, Operator, Value and Type. Specify the column name in the database table you want to insert information into in the Column Name column. In the Type column, specify the type of information to be inserted. There are five information type choices: `Null` uses "null" for the type comparison; do not use the Value column. `Timestamp` uses the current date/time as a timestamp; do not use the Value column. `Literal` uses the literal value specified in the Value column as a string; for example: `fred`. `Field` uses the value of the field from the message specified by the Value column. `Property` uses the value of the message property specified by the Value column. Four operators are currently available for comparisons: `Equal`, `Is`, `Like`, and `Not Equal`.
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.
Treat Deadlock as Connection Error	Identifies whether a database deadlock is treated as a connection error or as a message error. Enabled - 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 Queue.
Treat Query Timeout as Connection Error	Identifies whether a database query timeout is treated as a connection error or as a message error. Enabled - 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 Queue.