Including Data from a Different SQL Database into Your Application

To incorporate data from a non-Mongoose linked SQL Server database into a Mongoose-based application, you must specify information about the other database in the IDO Linked Database form and the IDO Linked Tables form.

Note: 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 helps ensure that the database indexes are scanned and accessed properly when performing queries.

To incorporate data from a non-Mongoose SQL database for use in a Mongoose-based application:

  1. Open the IDO Linked Database form and specify 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.
    Database Name
    Specify the SQL Server name of the database to which you want to link. If this database resides in the same location as the Mongoose databases, you can provide just the name of the database. If this database resides in a location other than the Mongoose databases, you must also provide the location of the database. Use this format: databaseServer . databaseName
    Note: The database administrator must first create a linked server relationship using the SQL Server configuration tools.
    Optimistic Lock Column Name
    Optionally, specify the name of a column that might be available for optimistic locking 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.
  2. Save your changes.
  3. Open the IDO Linked Tables form to verify and adjust the column settings for the non-Mongoose table:
    • Verify that the columns listed in the Column Name column match those of the non-Mongoose table.
    • Optionally, rename the View Column Names as you want them to display in your Mongoose application.
    • Verify that the primary keys for the non-Mongoose table are correct. Modify the choices for keys as necessary.
    • Optionally, select the column to use for optimistic locking in the non-Mongoose database. Mongoose designates the NODATE literal string for the "RecordDate" value, and no optimistic locking is performed when these conditions exist:
      • No "RecordDate" column exists in the non-Mongoose table.
      • The column specified in the Optimistic Lock Column Name field of the IDO Linked Databases form does not exist in the non-Mongoose table.
      • No column is designated for optimistic locking on the IDO Linked Tables form.
  4. To create the Mongoose view, click Create View.

    Mongoose creates a view that includes the columns from the linked table, along with columns and values for:

    • "RecordDate", used for optimistic locking
    • "RowPointer", required to be a value that is unique for the entire table
    • "AddMongooseFields", with a literal value of 1, which is used internally

You can now use the linked database and tables in the same way that you use any database created within Mongoose. You can create IDOs and forms, and perform read-write operations on them like any other Mongoose database. However, you cannot use the non-Mongoose SQL database and tables for any event action where the workflow must be suspended.