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:

• If querying from another program, a license key that includes the Remote Data Access option is required. Check by clicking the License Management button in the VAM.
  VTScada modules that use the SQL functions need no special licensing.
• Install the VTScada ODBC Driver on any computer that is to generate the queries. To do so, run the program VTScadaODBCDriverInstall.exe.
• Secure your application, and grant the required data access privileges to at least one account. We recommend that a dedicated account be created for remote data access, possessing no other privileges.
  The privileges required depend on the level of access to be granted. Refer to the table later in this topic.
• If you plan to query tag parameters, ensure that the account has the Tag Parameter View privilege.
• If using Realm Filtering, A realm, in which the application has been selected.
• 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.

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 Tag Value / History Retrieve
The Alarm history and time-based summary tables of the same.	None
SQL View tables	Any custom privilege assigned to the view tag.
Tag parameter tables	Parameter View
Other custom tables	Custom privileges specified when registering the table. (This feature is for advanced programmers.)