SQL Queries

You can generate reports of VTScada data using Structured Query Language (SQL) queries in third-party programs such as XLReporter™, Dream Report™, a REST or JODBC interface, Microsoft Access or Excel™. These queries are handled by VTScada's Remote Data Access feature, an optional component that must be purchased with your license agreement. An ODBC driver is provided free of charge, for installation on any workstation that is to send queries to your VTScada server.

A significant advantage of using ODBC is the ability to query VTScada from computers that do not have VTScada installed. Managers, engineers, operators and others can view up-to-date reports from any location that has network access to your VTScada server.

Using this interface, you can treat a VTScada application as if it were a relational database containing logged tag values, aggregate tag data, and alarm data. After the connection is configured, your reporting program can send SQL queries to VTScada to retrieve tag values that are being logged. (Tags that are not being logged have no stored history to query.)

VTScada stores history using only it's own proprietary format, which is not a relational database.
The SQL interface allows a limited subset of the SQL language to be used as if querying SQL tables.

Requirements:

Check your license

If you are querying from another program, you will need a license key that includes Remote Data Access.
Check from the VAM menu, click "License Management".

VTScada Modules that use SQL functions don't need special licensing.

Install the VTScada ODBC Driver

This comes included with your installation of VTScada, just run the program VTScadaODBCDriverInstall.exe

Setup a Remote Connect account

Create a dedicated account (perhaps, "RemoteConnect"). You will be providing these login credentials to your ODBC Data Source Administrator. By creating an account just for this purpose, you can use isolated login credentials, select only the necessary privileges and specify a realm area (if you use realm area filtering).

Give privileges & assign a security realm (if you're using realms)

The privileges required depend on the level of access to be granted. Refer to the table below for more information.
If you are using Realm Filtering, the accessing account will need to be a member of the appropriate realm.

In VTScada, "realm" is used to identify two completely separate concepts:
• Security Realms - controls what a user can see and use.
• Internet Realms - controls how a client connects.

Thin Client/Server Setup

Realm configuration (optional)

Setup a User DSN and Test your connection!

• A system DSN, configured using the Microsoft Windows™ ODBC Administrator program, or knowledge of the configuration so that you can build your own connection string.

The results of an SQL query are sorted by ASCII value as opposed to an alphabetical or lexicographical order.

Realm filtering (if configured) will limit the tags that can be queried within a realm. This may be useful to limit access due to security concerns or to avoid overwhelming your query viewer with an excessive number of tags.

Security and Remote Data Access

At a minimum, you will need access to a VTScada user account that has the Remote Data Access privilege. A common configuration is to create one or more accounts with only that privilege and one or more of the following privileges as required. Such a user will not have any access to the system other what is required to run reports.

Technically, you don't have to create a dedicated account. This will still work as long as there is an account with privileges. But this is a poor practice and discouraged.

For greater security, consider using one or more of the SQL View Tag, each of which can be assigned a custom privilege.
The Remote Tag Value / History Retrieve privilege is necessary in order to run custom SQL queries. Accounts with only the Remote Data Access privilege can see only their assigned SQL views or AlarmHistory, and cannot run their own SQL queries or browse the tag parameter tables.

Access to...	Requires the additional privilege...
The History table and time-based summary tables	Remote Data Access.
The Alarm history and time-based summary tables of the same.	Remote Data Access.
SQL View tables	Remote Data Access. Any custom privilege assigned to the view tag.
Control locks, tokens and token request tables.	Remote Data Access.
Tag parameter tables	Tag Parameter View
Other custom tables	Custom privileges specified when registering the table. (This feature is for advanced programmers.)