Including data from an external database into a Mongoose-based 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:
Note: To link to a non-Unicode database, use the Process Defaults form to set the Non Unicode Literal process default. This ensures that the database indexes are scanned and accessed properly when performing queries.
  1. In the Outrigger Profiles form, create a profile for the database that has these specifications:
    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.
    Profile Name
    (Required) Provide a name for the outrigger (external) database.
    Database Type
    (Required) Select the type of outrigger database to connect to.
    Server
    (Required for most, but not all database types) Specify the name of the server on which the outrigger database exists.
    Data Source
    • If you selected DB2 for the Database Type, specify the schema name.
    • If you selected Oracle for the Database Type, with the Direct option set to False, specify the Oracle service name as defined in the tnsnames.ora file.
    • If you selected Oracle for the Database Type, with the Direct option set to True, specify the Oracle server's IP address or DNS name. For additional options, see the notes at the end of this topic.
    • If you selected Postgres, Progress, Redshift, or SQL Server for the Database Type, specify the database name.
    Query Timeout
    (Optional) Specify the amount of time, in seconds, that your application is to wait for responses to a query from the outrigger database. If you leave this field blank, the query does not time out.
    User/Password
    (Required) Provide the login credentials to be used to access the outrigger database.
  2. Optionally, to test the connection to the outrigger database, click Test Connection.
  3. 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 an 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
    This field defaults to the database type selected in Step 1, or, if the Profile Name is blank, SQL Server.
    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.
  4. 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.
  5. To enable the buttons on the form, save the table record.
  6. Select the table and click Columns.
    This opens the IDO Linked Tables form, with an empty Columns grid.
  7. To connect to the database and create the default column information for the external table, click Repopulate.
    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 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.
      When the conditions stated here exist, Mongoose designates the NODATE literal string for the RecordDate value, and thus no optimistic locking is performed:
      • 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.
  8. To create a new view in the Mongoose database that defines the necessary columns and data types, click Create View.
    The created 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
  9. Use the IDO Wizard form to create an IDO whose Primary Base Table is the View Name that is created. If applicable, the profile name is automatically populated.
  10. Use the Advanced IDO Attributes form to ensure that the proper primary keys are selected in the correct order.
  11. Save your work.

Notes about the process:

  • The profile that is stored with an IDO definition is used to make an Application Database 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.

    For a code sample that uses this assembly, see Example: Custom Code to Communicate with an Outrigger Database.

  • 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 the Data Source field, specify the Oracle service name (SID).
    • Otherwise, in the Options grid, add an option of Direct with a value of True; and in the Data Source field, specify the Oracle server's IP address or DNS name. Finally, 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) option is cleared.
  • You cannot use asynchronous event handlers when communicating with external databases.
  • You cannot use the external database and tables for any event action where the workflow must be suspended.
  • You cannot use User-Defined Fields (UDFs) 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 option 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) option is cleared.
Related topics