Databases

Add Database

With the “Databases” navigation node selected, click on the “Add” button to add a database.

../../_images/odbc-add-database.png

This will open a dialog to enter database connection parameters.

../../_images/odbc-add-database-dlg.png

Database Settings

Database configuration options are:

  • Name

    The name of the database as it will appear elsewhere in the application.

  • Connection String

    The connection string can either be a DSN (Data Source Name) configured for the system in the form “DSN=DataSourceName” or any other valid connection string for the target database.

    Use the Windows system’s ODBC Data Source Administrator tool to preconfigure ODBC data sources for use of “DSN” data source references, or define the connection string in the form, for example, “Driver={SQL Server}; Server=myhost\SQLEXRESS; Database=mydatabase;”. Adjust settings for different database drivers, database engines, and databases.

    Note that it is the ODBC Connector background service that needs access to the database and the data source should therefore be a System data source. Alternatively, configure the Rensen ODBC Connector Service to run as a user with sufficient rights.

    Note

    Using the latest available ODBC drivers is recommended. It is worthwhile checking whether a newer version of your database ODBC driver is available because preinstalled ODBC drivers may be outdated.

  • Identifiers

    This settings determines how identifiers are constructed for use in database queries; such identifiers may be table and tag names configured within topics of the ODBC connector itself or tag names sent to the ODBC connector by other connectors.

    • Substitute invalid characters with underscore
      In this case any character in a tag name that is not alphanumeric is replaced by the underscore _ character. For example, a tag name containing a space as in Motor RPM is replaced with identifier Motor_RPM in database queries. This setting is suitable if a database is only written to or if database tables columns read from the database already have alphanumeric names. If names have other characters then select one of the other options.
    • Enclose in double quotation marks
      Table and column names are enclosed in double quotation marks for database queries. This setting retains the original tag name. For example, a tag name Motor RPM is replaced with "Motor RPM" for database queries. Most databases support quoted identifiers, although this option may not be enabled by default.
    • Enclose in square brackets
      Tag names are enclosed in square brackets. This setting retains the original tag name. For example, a tag name Motor RPM is replaced with [Motor RPM] for database queries. Microsoft SQL Server and Microsoft Access support this syntax by default.
    • Enclose in backtick characters
      Tag names are enclosed in backtick characters. This setting retains the original tag name. For example, a tag name Motor RPM is replaced with `Motor RPM` for database queries. MySQL supports this syntax by default.

    Tip

    To use different identifier settings with the same database, add another database pointing to the same underlying ODBC data source.

  • Create a ‘Generic’ topic

    ‘Generic’ topics are covered in the next chapter.

  • Using integrated security

    With this setting no further database login information is required or provided when the ODBC Connector service attempts to connect to the database. Note that, by default, the ODBC Connector service runs under the Local Service account and the database must either be configured to grant access to NT_AUTHORITYLocalService or the ODBC Connector service needs to be configured to run as a user with sufficient access rights.

  • Prompt for credentials

    In this case the application will prompt the user for credentials when a task is started that needs to connect to the database.

  • Save credentials for future use.

    You can allow the ODBC Connector to store credentials for future use. Credentials are stored in encrypted form and are retrieved whenever a task requires a connection to the database.

Use the “Test Connection” button to verify that a connection can be made before clicking “Add” to add the database.

You can change settings later in the database settings panel that is shown when you select the database in the navigation panel.

Remove Database

If a database is no longer needed then click on “Databases” in the main navigation panel and select the database in the content panel; click on the “Remove” button to remove the database.

Warning

Note that all topics configured for the database will be also be removed. This affects all tasks using these topics.