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, using the IDO Linked Database form and the IDO Linked Tables form.

Note: To link to a non-Unicode database, use the Process Defaults form to set the Non Unicode Literal process default. This helps ensure that the database indexes are scanned and accessed properly when performing queries.
  1. Open the IDO Linked Database form and specify these values:
    Link Database
    Specify the name that is to identify the database in the application. This is an internal designation only and need not be the same as the actual database name.
    Database Name
    Specify the name of the SQL Server 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, if necessary, 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 required.
    • Optionally, select the column to use for optimistic locking in the non-Mongoose database.

      When the conditions listed here exist, Mongoose designates the NODATE literal string for the RecordDate value, and no optimistic locking is performed:

      • 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 these properties:
    • 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.