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. Check by clicking the About 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 Remote Data Access privilege to at least one account. We recommend that a dedicated account be created for remote data access, possessing no other privileges.
  • 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.

You will need access to a VTScada user account that has the Remote Data Access privilege.

To prevent free browsing of the main history tables, consider using one or more of the SQL View Tag, each of which can be assigned a custom privilege. In addition, deny access to the Remote Tag Value / History Retrieve privilege so that accounts with Remote Data Access can view only their assigned SQL views.