How ClearSCADA can access an SQL database across the network from a remote computer that does not have ClearSCADA’s ODBC driver installed?

Issue

Access and control an SQL database exists on a remote computer that does not have ClearSCADA's ODBC driver installed on it.

Product Line

ClearSCADA 2015R2

Environment

Microsoft Windows Operating System, ClearSCADA 2015R2 ViewX, SQL

Resolution

This resolution discusses how ClearSCADA can access and control SQL Database using the ODBC driver.

ClearSCADA can access an SQL Database using another method called Linked Tables, which is not covered in this article.

ODBC Driver features and functions detailed information can be found in ClearSCADA Help under the directory "Driver Reference > ODBC Driver > ODBC Driver Guide - Introduction to the ODBC Driver"

Step (1): Data Source Name "DSN" Configuration

When it is required to establish a connection between a ClearSCADA system and a Third Party ODBC Client, a name for the data source to be defined.
To define the Data Source Name (DSN), display the ODBC Setup window, then enter the required DSN in the Name field as shown in the below steps.

• Open Data Sources (ODBC) from the control Panel

• Click on the Second Tab System DSN
• Click on Add

• Select the SQL Server Driver from the scroll down menu
• Click on Finish

Location of the ODBC Data Source Administrator Tool varies according to the Third Party application you are running and whether it is 32-bit or 64-bit. Check  Access the ODBC Data Source Administrator Tool in ClearSCADA Help under the directory "System Administrator > Client Administration > Third Party ODBC Client - System Connections > Client Administration Guide - Access the ODBC Data Source Administrator Tool"

• Type a name for the DSN, for example SQLDB
• Type the remote computer name followed by the SQL server name that exists on the remote computer, or select it from the drop down list (it might take few moments to search for the available SQL servers)
• Click on Next

If ClearSCADA shall connect to a remote computer which is out of the ClearSCADA server domain, select the second option that will allow you to log in to the SQL server using a User name and a Password.

• Select the Authenticity that will allow ClearSCADA to log in to the SQL server.
• In this example the existing user on SQL is utilized Login ID: sa
• Click on Next

If the SQL server is not accessible after clicking on Next, and an error message appears, the SQL security setting on the third party system has to be checked as follows

• On the remote computer open Microsoft SQL Management Studio
• Right Click on the server list and select Properties
• Click on Security
• Select SQL Server and Windows Authentication Mode

• After logging on to the SQL server, select which database will shall be controlled
• Activate the Check Box "Change the default Database to:"
• Select the SQL database name from the menu, for example call the database CS on SQL
• Click on Next

• Click on Test Data Source to confirm the connection is successfully established

• Finally the DSN is configured and ready to be linked to the ODBC connection on ClearSCADA

Refer to the attached Video "DSN.swf" that shows the previously mentioned Step-1. It can be shown on an internet browser like Internet Explorer or Google Chrome.

​
Step (2A): ClearSCADA, ViewX configuration to read data from the SQL database

• Open ViewX
• Create a new Group on ViewX for example "ODBC_Example"

Creation of a new ODBC Connection

• Right Click on the new group "ODBC_Example", Create New > ODBC > Connection
• Open the new connection properties window
• Type name of the DSN, or select the DSN that was configured in Step-1 from the menu
• Type User name "sa", and Password of the SQL Server User

• To confirm the new ODBC connection is working properly, check its state by right clicking on the New Connection and select "View Status" that should be showing "Online"

If the created DSN in the previous step-1 could not be found in the DSN menu, it means that DSN should be configured using Data sources ODBC 32-bit, since most probably the 64-bit version is used in Step-1. This is clearly explained in ClearSCADA Help under the directory "System Administrator > Client Administration > Third Party ODBC Client - System Connections > Client Administration Guide - Access the ODBC Data Source Administrator Tool". DSN 32-bit version can be found under the directory "C:\Windows\System32\ odbcad32.exe"

Creation of a new ODBC Query

• Right Click on the new group ODBC_Example > Create New > ODBC > Query
• Type the ODBC connection name "New Connection", or select it from the menu
• Write a query for example a query to read all data exists in the SQL database "SELECT * FROM CSDB"

The default scan rate is 30 seconds, that can be modified as per the application requirement




Creation of a new Analog Input Point
To show a value from the SQL database on a ClearSCADA mimic

• Right Click on the new group ODBC_Example > Create New > ODBC >Analog Point
• Open the New Analog Point properties
• Type the query name "New Query", or select it from the menu
• Select one of two options to poll data from the SQL database either Row & Column, or Value Lookup

Row & Column, and Value Lookup methods details can be found in ClearSCADA Help under the directories Row & Column: "Driver Reference > ODBC Driver > Data Retrieval Examples > Query an Oracle Database using the Row & Column Lookup Method" Value Lookup: "Driver Reference > ODBC Driver > Data Retrieval Examples > Query an Oracle Database using the Value Lookup Method"

Reading Analog values from the SQL database

• Assume that Row & Column method shall be defined to read data from the SQL database
• Select "Row & Column" from the "Lookup Method"
• Type "3" in Row cell, and "2" in Column cell

• Show the Analog Input point on the mimic to see its value online

• Snapshot from the SQL server, shows value of cell (3,2)

Step (2B): ClearSCADA, ViewX configuration to write data on the SQL database

Modifying the SQL database
Using a query that would modify the value of the same cell (3,2), which is being read in the previous Step-2A

• The query can be written like "UPDATE CSDB SET Age=85 WHERE ID=3;"

• Mimic screenshot:

• SQL Server Screenshot:

Refer to the attached Video "ViewX.swf" that shows the previously mentioned Steps 2A & 2B. It can be shown on an internet browser like Internet Explorer or Google Chrome.

Released for:Schneider Electric Malaysia