Including Data from an External Database into a Mongoose Application

Mongoose-based applications can communicate with external databases hosted on various types of servers through the IDO layer. To set up communication, use these steps.

If you want to link to a non-Unicode database, use the Process Defaults form to set the process default for Non Unicode Literal. This ensures that the database indexes are scanned and accessed properly when performing queries.

In the Outrigger Profiles form, create a profile for the database with these values:

Note: This step is not necessary for a SQL Server database hosted on the same server as the Mongoose database, or for a SQL Server database hosted on a linked server.

Database Type

Specify the type of outrigger database to connect to.

Data Source

If you select DB2 database type, specify the schema name.

If you select Oracle database type with option Direct=False, specify the Oracle service name as defined in the tnsnames.ora file.

If you select Oracle database type with option Direct=True, specify the Oracle server's IP address or DNS name. See Notes below for additional options.

If you select Postgres, Progress, Redshift, or SQL Server as the database type, specify the database name.
In the IDO Linked Databases form, create a new linked database record with these values:

Link Database

Specify the name that is to identify the database in Mongoose. This is a Mongoose internal designation only and need not be the same as the actual database name.

Profile Name

Specify the profile created in step 1.

For a SQL Server database hosted on the same server as the Mongoose database, or for a SQL Server database hosted on a linked server, leave this field blank.

Database Type

Defaults to the database type selected in Step 1, or SQL Server if the Profile Name is blank.

Database Name

For a SQL Server database hosted on the same server as the Mongoose database, specify the name of the database to which you want to link.

For a SQL Server database hosted on a linked server, you must also provide the name of the server using this format: databaseServer.databaseName.

Optimistic Lock Column Name

Optionally, specify the name of a column for optimistic locking that is present in some or all tables in the non-Mongoose database. If no specific column is designated on the IDO Linked Tables form, this value is used as the default optimistic lock setting.
In the Tables grid, specify or select an existing table name in the external database.
Optionally, rename the View Name to avoid conflict with any tables or views in your Mongoose application or with other external tables being linked.
Save the table record to enable the buttons on the form.
Select the table and click Columns. This opens the IDO Linked Tables form, with an empty Columns grid.
Click Repopulate to connect to the database and create the default column information for the external table.
The results can be adjusted by following these steps:
1. Verify that the columns listed in the Column Name column match those of the external table.
2. Optionally, rename the View Column Names as you want them to be shown in the New IDO Wizard form.
3. Verify that the primary keys for the external table are correct, as specified in the Keys column. Add or modify the choices for keys as necessary.
  
  Note: Keys are not initialized for Redshift databases, so you must specify them manually.
4. Optionally, select the column to use for optimistic locking in the external table. Mongoose designates the NODATE literal string for the "RecordDate" value, and thus no optimistic locking is performed, when these conditions exist:
  - No "RecordDate" column exists in the external table.
  - The column specified in the Optimistic Lock Column Name field of the IDO Linked Databases form does not exist in the external table.
  - No column is designated for optimistic locking on the IDO Linked Tables form.
Click Create View to create a new view in the Mongoose database that defines the necessary columns and data types. The Mongoose view includes the columns from the external table, along with these columns and values:
- "RecordDate", used for optimistic locking
- "RowPointer", required to be a value that is unique for the entire table
Use the New IDO Wizard form to create an IDO whose Primary Base Table is the View Name that is created. The profile name is automatically populated, if applicable.
Use the Advanced IDO Attributes form to ensure that the proper Primary Keys are selected in the correct order.

Notes about the process:

The profile that is stored with an IDO definition is used to make an ApplicationDB object within the IDO layer for read and write operations on the table, as well as for method calls to that database.
As an alternative to building an IDO through the wizard, which allows basic read-write functionality to the external database, you can use IDORuntime.Context.CreateOutriggerApplicationDB in a custom program to provide direct access to the outrigger database.
See Example: Custom Code to Communicate with an Outrigger Database for a code sample that uses this assembly.
For DB2 databases, a third party driver package must be installed and configured. Contact Support for details.
For Oracle and Progress databases, a license to a third party driver package must be purchased. Contact Support for details.
There are two modes for connecting to Oracle databases:
- If the application server is the Oracle server or Oracle Client software is installed on the application server, in the Options grid, specify an option of Direct with a value of False, and in Data Source, specify the Oracle service name (SID).
- Otherwise, in the Options grid, add an option of Direct with a value of True, in Data Source, specify the Oracle server's IP address or DNS name, and add an additional option of SID with a value of the Oracle service name on the Oracle server. You can also add an option of Port with the port number to connect to. The default value is 1521. In this mode, you can perform write operations on a linked table in an Oracle database only through a configuration where the Use Distributed Transactions (DTC) flag is cleared.
Asynchronous event handlers cannot be used when communicating with external databases.
You cannot use the external database and tables for any event action where the workflow must be suspended.
User-defined fields cannot be used when communicating with external databases.
All external tables referenced in an IDO must exist in the same database.
RowPointer and RecordDate properties are used by Mongoose but do not exist in external tables, so those IDO properties are derived instead of pointing to base table columns.
A called Oracle IDO method must be a function with a return type of integer.
Due to driver limitations, these data-types are not supported for linked tables in a Progress database: LVARBINARY, BLOB, LVARCHAR, CLOB, BINARY, VARBINARY, RECID, TINYINT, BIGINT.
For Postgres databases, the BIT data-type is not supported because IDO Properties have no equivalent data-type.
Due to security requirements, you can perform read and write operations on a linked table in a DB2, Oracle, Postgres, or Progress database only through a configuration, where the IDO Extension Classes Run In Partial Trust flag is cleared.
Due to driver limitations, you can perform write operations on a linked table in a DB2 or Progress database only through a configuration, where the Use Distributed Transactions (DTC) flag is cleared.