You can use ClearSCADA to query any ODBC database. This is useful as it allows ClearSCADA users to view data stored in other ODBC database systems. For example, if you have an SQL Server database, you can use Queries Lists to view the SQL Server’s data in ClearSCADA.
To query a third party database from ClearSCADA, you need to:
- 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.
- Use the ClearSCADA Server Configuration Tool’s Linked ODBC Tables settings to create a link between ClearSCADA and each individual table that you want to query (see SQL Query Configuration Settings in the ClearSCADA Guide to Server Administration). As long as ClearSCADA has a valid link to your Third Party ODBC database table, it can query the table in the same way as any table in the ClearSCADA database.
- In ViewX, log on and display any Queries List (see Display and Use Lists).
- With the Queries List on display, right-click on the List to display a context-sensitive menu. Select the Edit Query option from the menu. The SQL window is displayed, showing the Queries List’s current SQL.
- Delete the current SQL and replace it with an SQL statement that instructs ClearSCADA to query your Third Party ODBC database. There is no special syntax required to query your Third Party ODBC table, you should just construct an SQL statement in the same way as you would for any ClearSCADA database table.
- Select the OK button to confirm your entry.
- If the settings for the System DSN and Linked Database Tables are correct, and the SQL Query you entered is valid, the Queries List that you displayed in step 3 will now show the results of a query on the Third Party ODBC database table.
You can also use embedded Lists on Mimics to query Third Party ODBC database tables. As long as ClearSCADA has a link to the table that is to be queried, the SQL of the embedded List can be configured to query a Third Party ODBC table. This works in the same way as querying a ClearSCADA database table (see Editing an Embedded Query Listin the ClearSCADA Guide to Mimics).
With Mimics, you can also create a hyperlink to a List, with the configuration of the hyperlink’s SQL statement being used to query a Third Party ODBC table. For such a hyperlink to work, ClearSCADA has to have a link to the table being queried, and the SQL has to reference the Third Party ODBC table in the same way that it would reference a ClearSCADA database table (see Creating a Hyperlink to a List in the ClearSCADA Guide to Mimics).
If you want to show the results of a Third Party ODBC database table query to WebX users, embedded Lists and hyperlinks are especially useful. WebX users cannot alter the SQL of a List, but they can display Mimics that are pre-configured to query a Third Party ODBC database table.
This is also applies to Original WebX users.
Example: Querying a Third Party ODBC Database Table in ViewX
A common requirement is for users to be able to query a third party database and combine the result set with data that exists in the ClearSCADA database. In this example, we are going to use the Metadata feature to show how this requirement can be achieved.
Let’s say we have a third party ODBC database, in this case, an SQL Server database. The database contains a table named ‘ASSETINFO_PUMPS’ which is used to store information about the pumps used on part of a system including Pump_Reference, Serial Number, Purchase Date and Warranty.
In ClearSCADA, each pump is represented by two database points (one to power the pump on, and one to power it off). The various values of the points can be viewed on the Points List which works by querying the CDBPoint table in the ClearSCADA database.
For the purpose of this example, we are going to assume that a user wants to perform a query that returns the information in the ‘ASSETINFO_PUMPS’ table in the SQL Server database and combines it with the Points List data for the corresponding points in ClearSCADA.
To achieve this, the CDBPoint table in ClearSCADA has to be joined with the ‘ASSETINFO_PUMPS’ table in the SQL Server database, and a Join requires the two tables to have a matching column that can be used as a reference.
As the CDBPoint table does not contain the same fields as the ‘ASSETINFO_PUMPS’ table, there is no common column to use for the join. To work around this, a custom field (metadata field) is added to the database. The metadata field is added to all Point Forms by entering the following information in the configuration for a new metadata field:
Fields:
- Field Name—Pump Reference
- Section—Configuration
- Show on Form—Checked
- Table—CDBPoint
- Row—1
- Column—2
- List Priority—None
- Type—String
- Size—30
- Permission—Configure
- Target Type—CDBObject (grayed out)
- View Info Dialog Title—Pump_Reference
Labels:
- Label—Pump_Reference
- Table—CDBPoint
- Row—1
- Column—1
This adds a Pump_Reference field to the Point tab on Point Forms.
Each point is then configured so that its entry for the Pump_Reference field corresponds to the Pump_Reference number of the pump that the point represents. For example, if a digital input point represents a pump that has the reference number SA-REG1-PUMP1, the point will have SA-REG1-PUMP1 as its entry for the Pump_Reference field on the Point Form.
On the ClearSCADA Server, a System DSN is configured (via Windows Control Panel) so that the server’s operating system can detect the SQL Server. For the purpose of this example, the System DSN that is created is named SQLSERVERDB.
Next, the ClearSCADA server itself has to be configured so that it can connect to the ASSETINFO_PUMPS table in the SQL Server database.
Using the ClearSCADA Server Configuration Tool, the user configures a Linked ODBC Table for each table in the SQL Server database that is to be queried by ClearSCADA. In this case, ClearSCADA only needs to query one table in the SQL Server database (the ASSETINFO_PUMPS table), so a link is configured with the following settings:
- Table Name—ASSETINFO_PUMPS
- Connection String—DSN=SQLSERVERDB
- User Name—Null (Not Specified)
- Password—(Not Specified)
- Source Catalog—Master
- Source Schema—dbo
- Source Table—ASSETINFO_PUMPS
In this example the DSN system name is SQLSERVERDB and the SQL ‘source catalog’ has the default name of ‘Master’. The Table Name and Source Table settings have to match the name of the table that ClearSCADA needs to query.
The settings are applied to the ClearSCADA server configuration.
In ViewX, the user displays any of ClearSCADA’s built-in Queries Lists, for example, the Points List. The user chooses to display the List with no filters applied.
The user right-clicks on the List to display a context-sensitive menu. He selects the Edit Query option from the menu. This displays the SQL window, which shows the SQL query that is used to return the List’s current result set.
The current SQL is deleted and replaced with the following:
SELECT
"FullName" AS "~FullName", "CurrentValueFormatted", "CurrentStateDesc", "CurrentQualityDesc", "Manufacturer", "Serial_Number", "Purchase_Date", "Warranty"
FROM
CDBPOINT JOIN ASSETINFO_PUMPS USING ( PUMP_REFERENCE )
This SQL Query uses the Pump Reference property that is found in both the CDBPoint table (as it was added using the Metadata feature) and the ASSETINFO_PUMPS table, to join the two tables. This allows ClearSCADA to display a single result set that is taken from both of the tables. In this case, the SQL has been defined so that ClearSCADA displays a List that contains the Name, Value, State, Quality, Manufacturer, Serial_Number, Purchase_Date, and Warranty columns (selected columns taken from both the CDBPoint table and the ASSETINFO_PUMPS table).
