SQLQuery

(VTSSQLInterface library)

Description: A launched module that executes an SQL query on data in a VTS application.

For queries of external databases, refer to the ODBC Manager Service
Returns: Nothing
(The parameters will be populated with pointers to the returned information)
Usage: Script Only.
Function Groups: Database and Data Source, ODBC
Related to:

RegisterCustomTable | GetTagHistory
Format: \VTSSQLInterface.SQLQuery(QueryString, &Results, &FieldNames, &FieldTypes, &ReturnCode, &ErrorMsg[, &ParamInfo, &PageInfo, &PageStatsInfo])
Parameters:  
QueryString
Required. Any text expression containing a valid SQL select statement. Not all selection clauses are supported.
Results
Required. A pointer to a variable which will hold the results array.
FieldNames
Required. A pointer to a variable that will hold the field names array.
Field names are typically "Value" for most tag values.
FieldTypes

Required. A pointer to a variable that will hold the field types. The types will be returned as SQL data types according to the following table:

Field Type SQL Data Type
1 SQL_CHAR
4 SQL_INTEGER
5 SQL_SMALLINT
8 SQL_DOUBLE
9 SQL_DATETIME
12 SQL_VARCHAR
ReturnCode

Required. A pointer to a variable that will hold the return code. The code will be one of the following:

Return Code Meaning
0 #SUCCESS
1 #SYNTAX_ERROR
2 #TABLE_NOT_FOUND
3 #COLUMN_NOT_FOUND
4 #ILLEGAL_JOIN
ErrorMsg
Required. A pointer to a variable that will hold a textual error message. Valid only if ReturnCode is not zero.
ParmInfo
Optional. A pointer to a variable that will hold parameter information.
PageInfo
Optional. A pointer to a variable that will hold a PageInfoStruct structure, used to support pagination.
PageStatsInfo
Optional. A pointer to a variable that will hold a PageStatsInfoStruct structure, used to support pagination.
Comments:

Only selection queries are supported. SQL statements for data manipulation will do nothing. If tag filtering or realm-area filter is in effect, this function will retrieve data only from tags the currently logged-on user is permitted to access.

When retrieving historical data, SQLQuery is essentially a wrapper for GetTagHistory. It takes an incoming SQL query, and makes one or more calls to GetTagHistory to retrieve the results. SQLQuery may also be used to retrieve current tag values and other custom tables (such as alarm data).

Legacy tables made it look like tag values were stored in separate tables and used the time stamp of the current server. These legacy tables still exist but are hidden by default, using the property: SQLQueryHideLegacyTables. Newer code should query all tag values from the table, "History", or from a ":TPP"derivative such as "History:1d". See the links for further information.

Supported SQL syntax is as follows:

SELECT [DISTINCT | ALL] columnspecifier-1, columnspecifier-2, ...

FROM tablename-1, tablename-2, ...

[WHERE where-expression]

[ORDER BY columnspecifier-1 [ASC | DESC], columnspecifier-2, ...]

[LIMIT [offset,] row_count]

  • Columnspecifier is either [table-name.]'*' to indicate all columns in a table or a specific column-name in the form: [table-name.]column-name
  • Tablename is either the table name or 'table-name [[AS] alias-name']
  • Quotes may be used around table or column names, and must be used if the names contain special characters. When an alias-name is specified it may be used in place of the table-name in the column specifier.
  • When more than one table is specified, the tables are automatically joined based on their Timestamp columns and MUST have matching "TPP"s. A join expression may be used in place of a table list, but it is only parsed to extract the tables specified. The actual join expression is ignored.
  • Where-expression is an expression to be used to filter the result data.
  • It may contain references to columns, use comparison operators, use functions ABS, LENGTH, UPPER, LOWER, CONCAT, CASE, SQRT, INTEGER, and use keywords AND, NOT, and OR.
  • Use the LIMIT clause to specify a limit to the number of rows that will be returned, and optionally an offset (0-based) which allows pagination. LIMIT is applied after WHERE filtering and after sorting.
  • If there is no explicit LIMIT clause in the query there is an implicit "LIMIT SQLQueryMaxResultRows" (Settings.Dynamic setting) added.

Each logged tag corresponds to one table in the VTS database schema.

Every logged variable within the tag is one column.

In addition, a "TPP specifier" may be appended to a tag name to utilize GetTagHistory's ability to retrieve data over time periods. The TPP specifier is a colon followed by a number and an abbreviation for various time periods. The recognized time period abbreviations are (case-insensitive):

  • MS - milliseconds
  • S - seconds (this is the default, therefore the S may be left out)
  • M - minutes
  • H - hours
  • D - days
  • W - weeks
  • Y - years

For example, the table 'ai1:2D' can be used to retrieve data from tag ai1 with a TPP of 2 days.

A TPP may only be specified for a tag that has at least one numeric logged variable. When a TPP is specified, the available columns are:

  • Timestamp
  • VarName:Average
  • VarName:Minimum
  • Varname:Maximum
  • VarName:Delta
  • VarName:ValueAtStart
  • VarName:TimeOfMin
  • VarName:TimeOfMax
  • VarName:ZToNZCount
  • VarName:NonZeroTime
  • VarName:Total
  • VarName:Interpolate

... where VarName may be replaced by any numeric logged variable from the tag.

The current values of a tag may be retrieved using a table name with the format "TagName_Current". This table will have one row whose columns are Timestamp (the current time) and the current values of each logged value in the tag.

Bit-Wise queries can be created, similar to modes 12 and 13 of calls to GetTagHistory(). To specify a bit-wise summary, use either:

  • Tag Name:Value:BitwiseOR
  • Tag Name:Value:BitwiseAND

For example:

Select Timestamp, as1:value:average, as1:value:BitwiseOR from History_1h where Timestamp >= '2017-10-02 00:00' and Timestamp <= '2017-10-02 23:59'

Related information:

VTScada SQLInterface Module

SQL: Reference and Examples