Create a Link between an External ODBC Database and ClearSCADA

You can link external ODBC databases with ClearSCADA so that the external databases can also be queried via ViewX or WebX. For example, you can link an SQL Server database to ClearSCADA, and then edit the SQL for a Queries List so that it queries:

• just the ClearSCADA database
• just the external database

or

• both the ClearSCADA database and the external database simultaneously.

You can link one or more external ODBC databases to ClearSCADA by using the Server Configuration Tool’s Linked ODBC Tables settings. These are located in the SQL Query Configuration branch.

To link an external ODBC database with ClearSCADA, you need to

1. Use Windows to configure your operating system to recognize the external database. To do this, you need to use the Data Sources settings that are available via the Control Panel. When configuring the Data Sources settings, you can define various settings for accessing the external table, including passwords and a name for the external table. For more information, please refer to the Microsoft documentation for using the Data Sources tool.
2. Access the ClearSCADA Server Configuration Tool (see Accessing the ClearSCADA Server Configuration Tool)
3. Expand the SQL Query Configuration branch.
4. Select the Linked ODBC Tables branch to display the Linked ODBC Tables section.
5. Right-click on the background of the Linked ODBC Tables section to display a context sensitive menu.
6. Select the Add Linked ODBC Table option to display the Add Linked ODBC Table window.

7. 

7. Use the Add Linked ODBC Table to configure the link to the external database as follows:
   • Table Name—The name that will be used to identify the external database in ClearSCADA. This can be any alphanumeric string of your choosing - it does not have to correspond to any other setting or name, but has to be unique. You should avoid using punctuation characters such as apostrophes in the table name.
   • Connection String—The connection string that allows ClearSCADA to identify and access the external data source. The connection string syntax varies for each driver, with some drivers providing additional connection functionality. For information on the appropriate connection string for your external database, please refer to the documentation provided by the manufacturer of your database product.

     The Connection String you enter has to be appropriate for the type of external database and consistent with the settings you made via the Data Sources settings (see step 1 of this procedure).

   • User Name—Use to define the user name of a user account in the external database. This user account will be used by ClearSCADA to attempt to gain access to the external database, and so should have sufficient access permissions to the database tables.

     The User Name entry is only needed if security authentication is required and the User Name has not been included in the Connection String.

   • NOTE: If a User Name and Password is required, you should only include the User Name and Password once. Either include the User Name and Password in the Connection String or enter them separately in the User Name and Password fields. If you define the User Name and Password more than once, ClearSCADA will notify you of an error in the string.

     NOTE: ClearSCADA usernames and passwords that stored in the registry are encrypted (for example for alarm redirection). You can only decrypt them on the machine from which they were backed up. This means that backing up the registry and then re-applying it to another machine requires you to recreate users in order to use the affected functions. This is also true if windows is re-installed on the machine.

   • Password—Use to define the password for the user account (above). The Password is only needed if security authentication is required and the Password has not been included in the Connection String.
   • Source Catalog—Use to define the catalog element of the external database hierarchy (if required).

     The ODBC standard allows databases to be structured in a three tier hierarchy, with the catalog being the upper level, the schema being the middle level, and the individual tables being the lower level. The way in which third-party drivers use this hierarchy varies, so you should refer to the documentation for your external database application for more information on what is the appropriate Source Catalog entry. For some drivers, a Source Catalog entry may not be required.

   • Source Schema—Use to define the schema element of the external database hierarchy (if required). As with the Source Catalog setting, the Source Schema is used differently by different drivers. For this reason, you should refer to the documentation for your external database application for more information on what is the appropriate Source Schema for your configuration. For some drivers, a Source Schema entry may not be required.
   • Source Table—Use to define the external database table that is to be made available for use with queries in ClearSCADA. Enter the name of the database table you require.
8. Select the OK button to confirm your selections and close the Add Linked ODBC Table window.
   A new ODBC link is created and is added to the list of ODBC links shown in the Linked ODBC Tables settings. If you right-click on the ODBC link, a context sensitive menu is displayed; you can use the menu to Modify an ODBC Table Link or Delete an ODBC Table Link.
9. Right-click on the system icon in the tree-structure, and select the Apply Changes option to apply the changes.