REST Queries - Notes & Examples

REST stands for REpresentational State Transfer. It allows web applications to query data using either HTTP GET or POST 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 application that can process JSON data. It is up to you to create that 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 (TLS, X.509, SSL) Use "http://" otherwise.)

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

Examples of several common queries are provided in the file REST Interface Demo.html, distributed with your copy of VTScada. Assuming that you installed to C:\VTScada, you will find the file within the folder: C:\VTScada\Examples\REST Interface Example.
To use the examples file, you must have an application configured for Remote Data Access and a user account in that application with the Remote Data privilege. Full instructions are provided within the HTML file.

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.

For GET requests, the query parameter should always be fully URL encoded. For example, '%' must be encoded as '%25', and spaces should be escaped as '+' or '%20'. This applies to all special characters. (Further information is available at https://en.wikipedia.org/wiki/URL_encoding and https://developer.mozilla.org/en-US/docs/Glossary/percent-encoding)

If using JavaScript you might do this with encodeURIComponent:

const encodedQuery = encodeURIComponent("GET Tables");

If using .NET, you might do this with URLEncode:

System.Web.HTTPUtility.URLEncode

Notes for using POST requests

For POST requests, the query parameter should never be URL encoded.

If using POST requests, the body of the request cannot be empty and must be a JSON encoded string that contains the query and an optional pageToken parameter. The pageToken parameter may be empty if not using pagination. For example:

       {
         "parameters":{
           "query": "...",
           "pageToken": "..."
         }
       }

The only accepted "Content-Type" header value is "application/json".

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

Limitations

The default limit for the POST request body size is 32768 bytes (32KB), which is same as the limit for GET requests. You can send POST requests longer than 32KB by adjusting the setting SQLQueryMaxRequestLength in SETUP.INI.

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).

If your query uses the wildcard "Select * from...", it is likely that the interface will return an error message due to the result containing more than 64 columns, unless the application has very few tags.

URL encoding will be required for some characters. An example follows.

When making queries with GET requests, if your query includes a LIKE clause you must encode the % wildcard character as %25. For example:

...WHERE Area LIKE 'East%'

Will fail because the browser will not encode the % properly. Instead, use:

...WHERE Area LIKE 'East%25'

Returned data

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

 

select timestamp,'Station 1\PLC1\Level:value' from history

 

would be placed in the URL and encoded as follows:

.../REST/SQLQuery?query=select%20timestamp%2C'Station%201%5CPLC1%5CLevel%3Avalue'%20from%20history

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\\PLC1\\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%20timestamp%2C'Station%201%5CPLC1%5CLevel%3Avalue'%20from%20history&PageToken=eyJ ...

REST Interface Authentication

Two authentication methods are supported by the REST interface. Regardless of the header used, the credentials must be sent in every request made to VTScada's REST interface.

1. The custom X-VTScadaAuth header. This exists as an alternative to the Authorization header to prevent browsers from retaining and automatically sending credentials, as can happen with the Authorization header. The value of this header follows the format of the HTTP basic authentication header where the header value is calculated by Base64-encoding the username followed by ":", which is in turn followed by the password. This method is recommended when queries are being made from a web browser. (See the following caution note.)

In VTScada script code this can be constructed as follows:

    HeaderValue = Base64Encoder(Concat(Username, ":", Password));
    Header = Concat("X-VTScadaAuth: ", HeaderValue);

2. HTTP basic authentication which uses the Authorization HTTP header. While this is widely supported by third party platforms and tools it should be used with caution and only over a secure connection. This method is recommended when queries are not being made from a web browser.

Basic Authorization (username and password) is sent in clear text and must only be used over a secure connection such as HTTPS.

Examples of REST queries

1) The following GET query, copied into a browser's address field, will retrieve data from Trihedral's online demo application

On first running the query, you will be prompted for a user name and password. Reply with demo and demo

 

Query:

Select Timestamp, 'WaterTreatmentPlantDemo\TCPIP Port\WaterTreatPlantSim\Tanks\Tank0\TankLevel:value' from history order by timestamp desc limit 10

 

URL:

https://vts.trihedral.com/waterdemo/REST/SQLQuery?query=Select%20Timestamp%2C%20'WaterTreatmentPlantDemo%5CTCPIP%20Port%5CWaterTreatPlantSim%5CTanks%5CTank0%5CTankLevel%3Avalue'%20from%20history%20order%20by%20timestamp%20desc%20limit%2010

 

2) For this example, you can use either Command Prompt or a Windows PowerShell window. It will not work from a web browser. This is GET request using basic authentication:

curl.exe -u demo:demo https://vts.trihedral.com/waterdemo/REST/SQLQuery?query=GET+Tables 

 

3) This next example works only in PowerShell due to quoting being PowerShell-specific. This is a POST request using basic authentication.

curl.exe "https://vts.trihedral.com/waterdemo/REST/SQLQuery" -X POST -u demo:demo -H "Content-Type: application/json" --data-raw """{""""parameters"""": {""""query"""": """"GET Tables""""}}"""

 

4) The final POST example stores the query in a JSON file, "query.json". To run this, create a file named query.json and within it store the following:

{
  "parameters": {
    "query": "GET Tables"  
  }
}

You can now run the following query:

curl.exe "https://vts.trihedral.com/waterdemo/REST/SQLQuery" -X POST -u demo:demo -H "Content-Type: application/json" --data "@query.json"