SQL query¶

Introduction

The Embedded Database and ODBC Database objects support the ANSI SQL-92 standard for related database queries. A DELETE query can also be run to delete a record. All other operations, such as adding/removing/renaming, are performed using the OPC UA methods exposed by the same objects.

Note

for details on SQL statements and clauses refer to the standard.

Supported statements

SELECT
DELETE
UPDATE

Clause supported by the SELECT statement

FROM
JOIN
AS (alias for tables and columns, allows regular and delimited identifiers)
WHERE
GROUP BY
ORDER BY
Set function (COUNT, MAX, MIN, AVG, SUM)
HAVING
LIMIT

Note

the LIMIT OFFSET variant to limit the number of records returned is supported, e.g. SELECT * FROM Table1 LIMIT 1000 OFFSET 10 returns 1000 records starting from record number 10. The offset is optional and its default value is 0.

Subqueries allowed for the FROM clause and for the IN predicate are also supported

Important

the FROM clause is obligatory, the others are optional.

Important

the clauses must always observe the following order: FROM, WHERE, GROUP, HAVING, ORDER, LIMIT.

JOIN clause

All JOIN types are supported:

cross
inner
outer (left outer, oright outer, full outer)

Note

The DBMS on which the query is performed may involve limitations.

WHERE clause

The WHERE clause supports the following operators:

<, <=, >, >=, =, <> comparison operators
AND, OR and NOT logical operators
IN, LIKE, BETWEEN, IS NULL operators

EXTRACT clause

The EXTRACT clause extracts a Date/Time component from a DateTime type column. Below are some examples:

SELECT EXTRACT(DAY FROM Time) AS "Day" FROM ...

SELECT EXTRACT(YEAR FROM Time) AS "Year" FROM ...

SELECT EXTRACT(MONTH FROM Time) AS "Month" FROM ...

SELECT EXTRACT(HOUR FROM Time) AS "Hour" FROM ...

SELECT EXTRACT(MINUTE FROM Time) AS "Minute" FROM ...

SELECT EXTRACT(SECOND FROM Time) AS "Second" FROM ...

CHAR_LENGTH clause

The CHAR_LENGTH clause obtains the length of a String type column. Below is an example:

SELECT CHAR_LENGTH(Column1) AS Column1Length FROM ...

Literal values allowed in a query

The following literal values are allowed:

true and false Boolean expressions
whole numbers, decimals (the decimal separator is the point ‘.’) and decimals in scientific notation (e.g.: -1.034E3)
text strings delimited by quotation marks (e.g.: 'string')

Note

to insert the quotation mark ’ in a text string, it must be doubled (for example, to write 'string’ it is necessary to write ''string'')
date and time in ISO 8601 notation delimited by double quotes (e.g.: "2004-05-23T14:25:10.487").

Note

the date and time notation is non-standard ANSI SQL and makes it possible to avoid the keywords DATE, TIME and TIMESTAMP without ambiguity.

Conventions on data types in a query

The following conventions are valid:

Whole numbers are of the Int32 type.
Decimal numbers are of the Double type.
The true and false literals are interpreted as Boolean type data.

Identifiers

The following identifiers are allowed:

regular: do not contain spaces, start with an uppercase or lowercase letter and can contain numeric digits, letters and the underscore character ‘_’.
delimited: are delimited double quotes ‘"’ and can contain any character (except the character ‘"’). Any spaces at the end are ignored.