You can use Geo SCADA Expert to query any ODBC database. This is useful as it allows Geo SCADA Expert 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 Geo SCADA Expert.
To query a third party database from Geo SCADA Expert, 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 Geo SCADA Expert Server Configuration Tool’s Linked ODBC Tables settings to create a link between Geo SCADA Expert and each individual table that you want to query (see SQL Query Configuration Settings in the Geo SCADA Expert Guide to Server Administration). As long as Geo SCADA Expert 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 Geo SCADA Expert 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.
Access to the Edit Query option might be restricted on your system. You will only have access to the Edit Query option if the Geo SCADA Expert server allows SQL queries to be edited from its clients, and your User Account allows you to edit SQL queries. For more information, see Specify Whether SQL Queries can be Edited using the Clients on this Server
- Delete the current SQL and replace it with an SQL statement that instructs Geo SCADA Expert 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 Geo SCADA Expert 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 Geo SCADA Expert 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 Geo SCADA Expert database table (see Editing an Embedded Query Listin the Geo SCADA Expert 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, Geo SCADA Expert 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 Geo SCADA Expert database table (see Creating a Hyperlink to a List in the Geo SCADA Expert Guide to Mimics).
If you want to show the results of a third-party ODBC database table query to Virtual ViewX users, embedded Lists and hyperlinks are especially useful. Virtual ViewX 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 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 Geo SCADA Expert 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 Geo SCADA Expert, 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 Geo SCADA Expert 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 Geo SCADA Expert.
To achieve this, the CDBPoint table in Geo SCADA Expert 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 Geo SCADA Expert 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 Geo SCADA Expert server itself has to be configured so that it can connect to the ASSETINFO_PUMPS table in the SQL Server database.
Using the Geo SCADA Expert Server Configuration Tool, the user configures a Linked ODBC Table for each table in the SQL Server database that is to be queried by Geo SCADA Expert. In this case, Geo SCADA Expert 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 Geo SCADA Expert needs to query.
The settings are applied to the Geo SCADA Expert server configuration.
In ViewX, the user displays any of Geo SCADA Expert’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.
Access to the Edit Query option might be restricted on your system. You will only have access to the Edit Query option if the Geo SCADA Expert server allows SQL queries to be edited from its clients, and your User Account allows you to edit SQL queries. For more information, see Specify Whether SQL Queries can be Edited using the Clients on this Server
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 Geo SCADA Expert to display a single result set that is taken from both of the tables. In this case, the SQL has been defined so that Geo SCADA Expert 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).
