REST Queries

REST stands for REpresentational State Transfer. It allows web applications to query data using HTTP GET requests and a subset of SQL. Results are returned in the JSON format of name and value pairs. Result sets are limited in size and pagination codes are used to allow you to retrieve subsequent portions of data returned from the original query. This greatly reduces the time required if querying a large data set.

Information is returned as a (potentially large) set of name / value pairs, suitable for a web application that can process JSON data. It is up to you to create that web application in order to organize and format the results. Part of this should include a convenient user-interface to build the queries and to request the next page of a large data set.

Given a VTScada server configured for ODBC (Configure an ODBC Server) and a user account that has the Remote Data privilege, you can query data using a URL in the following form:

https://Your.ServerName.com/RealmName/REST/SQLQuery?query=Select Something...

(Example assumes that you have configured security as recommended in Internet Security (SSL, TLS, X509) Use "http://" otherwise.)

Do not name any realm, "Rest" or "SQLQuery". Doing so will interfere with remote access to VTScada data.

Samples of queries are provided in SQL: Reference and Examples.

 

If your query uses the wildcard "Select * from...", it is likely that the interface will return an error message unless the application has very few tags.

 

By default, a maximum of 64 columns and 10,000 rows will be returned, and a pagination token added to the return structure if there are more rows available. You can disable pagination by setting the property SQLPaginationEnabled to 0 (FALSE).

Returned data

Examples in this discussion use data returned from the following query:

.../REST/SQLQuery?query=select timestamp,'Station 1\CommChannel\Level:value' from history

(Spaces are replaced by the HTML code, %20)

Header

The header describes the names and types of the returned data. Values follow immediately after. For the query shown, this will look like the following. Note that timestamps are always returned as UTC values.

{"results":{"fieldNames":["timestamp","Station 1\\CommChannel\\Level:value"],
"fieldTypeNames":["TIMESTAMP","DOUBLE"],"values":[[1562084981.054,12.603042138914],
[1562085057.977,12.503052503053], ...

Body

The body of the returned data consists of the comma-separated values of the returned data where each record is enclosed in square brackets. Two records are shown in the preceding example.

Footer

The footer provides summary information describing the query including error messages, number of results and a timestamp describing when the data was retrieved. If more data is available, a page token is included in the footer as shown in this example:

"returnCode":0,"errorMessage":null,"nextPageToken":"eyJ0aW1lc3RhbXAiOjE1NjIwODg3NDUuNDQxLCJudW1SZXRya
WV2ZWQiOjEwMDAsIm51bVJlc3VsdHMiOjEwMDAsIm9mZnNldCI6MCwiZmlyc3RUaW1lc3RhbXAiOm51bGx9","paginationStats":
{"numRetrieved":1000,"numResults":1000,"timestampRetrieved":1562088735.43}}

Retrieve the next page

To continue pulling data from a query, resubmit the same query with the page token. Note that the URL parameter for the token takes the form, "PageToken" rather than the name used in the footer of "nextPageToken". For example:

.../REST/SQLQuery?query=select timestamp,'Station 1\CommChannel\Level:value' from history&PageToken=eyJ ...

 

REST Interface Authentication

VTScada supports standard HTTP basic authentication on the REST interface. In conjunction with HTTPS this should be considered the standard case for deployments. If the REST queries are being made directly from a browser to VTScada (for example, with JavaScript) the X-VTScadaAuth header can be used to prevent the browser from offering to save or temporarily retain credentials in a difficult to control manner.

 

There are two methods of authentication, or locations where the credentials could be placed. The two locations are the standard Authorization header used for HTTP basic authentication, and the X-VTScadaAuth header. The X-VTScadaAuth header was introduced as an alternative to the Authorization header to prevent browsers from retaining and automatically sending credentials, as can happen with the Authorization header. The standard Authorization header is recommended unless the REST queries are being made from a web browser. For basic authentication the Credentials are sent in every request.

Basic Authorization is sent in clear text and must only be used over a secure connection, e.g. HTTPS.