Connections to external databases for

This section tells you how to set to use an application connection to an external database. To use the legacy database connection, see the section "Using the Legacy Database Connection for ".

Prerequisites

Take note of this important information before you start the setup:

  • Before you set up an application connection to an external database, you must have an external object type connector installed and enabled.
    • The connector must support the application connection. You can use External Object Type Connector, which is normally installed to the vault but is disabled.
    • If you use the service, the connector must be enabled on the proxy. For instructions, see Configuring external object types over. If you use a local service, the connector must be enabled in the vault.
  • If you use replication and application connection to an external database service, you must configure the connection separately for each vault in the replication scheme. Make sure that the necessary configuration changes are also made to each vault. We also recommend that you read the section Replication of External Objects of the document Replication and Archiving - User's Guide.
  • It is not possible to include the configuration for External Object Type Connector to replication packages.
  • In SELECT FROM statements, the columns are selected in order of appearance. There is no relation between column and property names, which is why the order must be the same in the SELECT FROM and INSERT INTO statements, and SELECT FROM and UPDATE statements. Also, columns not used in the INSERT INTO and UPDATE statements must always be listed last in the SELECT FROM statement.

To use a connection to external database and to open the service configuration:


  2. Expand Metadata Structure (Flat View).
  3. Click .
    Result:The listing is opened in the right pane.
  4. In the right-pane listing, double-click the .
    Result:The dialog is opened.
  5. Go to the Connection to External Database tab and enable the option Use a connection to an external database to import and modify objects that reside in the external database.
  6. Select Application connection.
  7. In Service, select the service.
    OptionDescription
    at vault <name of the vault> Select this option to use the local service.
    from proxy <name of the Ground Link proxy> Select this option to establish the connection with a remote service through .

    The services that have the (OK) suffix, have a configuration for the .

    Result:Information of the service configuration with possible errors is shown.
  8. Click Configure.
    Result:The External Object Type Connector dialog is opened.
To specify the connection settings and to get the source columns:
  1. Expand Service-Specific Settings > Connection to External Database.
  2. In Provider, select the provider for the external database connection.
    Example:Microsoft OLE DB Driver for SQL Server.
    Note: The list of providers shows all the available providers on the server machine that runs the external object type connector. Thus, it can include providers that cannot be used in external database connections.
    The syntax of the connection string is different for each Object Linking and Embedding Database () supplier used for establishing the connection to the external database. If Open Database Connectivity (ODBC) is necessary to create the connection, the data store must be accessed over and ODBC. For a list of recommended providers, see Provider Recommendations for External Database Connections.
  3. Optional: If you selected Custom provider (manual configuration), in Custom provider, specify the provider.
  4. Under Connection to External Database, complete one of these steps:
    OptionDescription
    Specify the other settings.

    The correct values are different for each provider and external database.

    For more information, select a setting and see the Info tab.
    In Advanced Options, enter the connection string. Use this option if it is not possible to use the other settings with the selected provider. When you enter the connection string, make sure that all values are correctly enclosed and the connection string has the necessary formatting.
  5. Optional: Under Optional SELECT Statements, define the settings.
    Note: The application connection does not support the Connections to External Databases settings in the Advanced Vault Settings section in . To use these settings, define them here.
  6. Under Service-Specific Settings, in SELECT Statement, write the SELECT statement for getting source columns from the external database.
    Examples of SELECT statements:

    SELECT CustomerNumber, CustomerName FROM Customer

    SELECT ID, Name + ' ' + Department FROM Company

    SELECT ID, Name, CustomerID FROM Contacts

    SELECT * FROM Customer

    Tip: You can write a multi-line statement.
  7. Click Save.
  8. Optional: To configure a remote service:
    1. Click Apply.
      Result:The Enter Password dialog is opened.
    2. Enter the password that is defined in the Ground Link proxy configuration.
    3. Click OK.
  9. To authenticate the common user, open the Dashboard tab and click Authenticate.
    Result:The Log In dialog is opened.
  10. Enter the common user credentials and click Log In.
    Note: The ODBC driver does not support all special characters. If your connection uses ODBC, the values that contain special characters must be enclosed in curly brackets. The correct format is {username or password}.
  11. In the dialog that is opened, click OK.
  12. Click Save to get the source columns.
    1. Optional: To configure a remote service, repeat step 16.
    Note: You cannot save the configuration if there are no changes.
    Result:The dialog closes and the configuration refreshes. On the Configuration tab, the Column Mappings section shows the source columns that your SELECT statement returned from the external database.
To map the source columns with properties:
  1. On the Configuration tab, go to Column Mappings and expand a source column node.


  4. Repeat the steps from 21 to 23 for all the necessary source columns.
To take the configuration into use:
  1. Under General Settings, set Enabled to Yes.
    Tip: You can enable and disable the external database connection also with the Disabled check box on the Connection to External Database tab of the dialog.

    If the connection is disabled, information between the vault and the external database is not synchronized.

  2. Optional: To configure a remote service:
    1. Click Apply.
      Result:The Enter Password dialog is opened.
    2. Enter the password that is defined in the Ground Link proxy configuration.
    3. Click OK.
  3. Click OK to close the dialog.
The is now updated to and from the external database.