SQL statements operate on tables of data organized into rows and columns. The basic building block of SQL data is the scalar value, for example the object (database item) at a row and column position in a database table is a scalar value. Each such value is of some particular data type. For each such data type there is a particular format and behavior.
The following data types are supported:
| Data Type | Description |
|---|---|
| Boolean | A single bit value, either TRUE or FALSE |
| Integer | Signed 32-bit integer or signed 64-bit integer |
| Real | Double precision floating point number |
| String | Varying length character string |
| Binary | Varying length bit string |
| DateTime |
Date and optional time of day For information about the required format, see Expressions.expr-primary-datetime, and see Expressions.expr-primary-odbc-datetime. |
| Interval |
A duration For information about the required format, see Expressions.expr-primary-interval. |
| Reference |
A reference field This data type is only supported for use with the de-reference operator. |
| Aggregate |
An aggregate—an extension of a class in the database This data type is only supported for use with the de-reference operator. |
| Location |
A geographical location For information about the required format, see Expressions.expr-primary-location. |
SQL statement writers need to declare constant values of a particular data type. Constant values for some data types, such as Binary, String, Integer and Real, are declared using Scalar Literals. Refer to Literals for more information. Other data types, such as DateTime, Boolean and Interval need Keywords to declare constant values. Refer to expr-primary-constant and expr-primary-interval for more information.
To reference a database field in SQL, you reference the column that represents that field in the relevant table or class (see Understanding the Database). (You can use the Database Schema to ascertain the names of fields, tables, and classes in the ClearSCADA database.) The column reference comprises the data type of the database field that it represents. You can use column-ref to reference a column (database field). Most of the fields that you retrieve from the database are presented as columns in the resulting query.
The ClearSCADA database supports a wider range of types than the SQL language. For example the ClearSCADA database supports different integer types such as Signed Byte, and Unsigned Word. Where possible ClearSCADA database types are converted to the appropriate SQL type, as listed below.
- Byte, Word and Long (both signed and unsigned) fields are promoted to Integer.
- Float and Double fields are promoted to Real.
- Reference fields are converted to Integer, where the value of the integer is the object id of the reference target.
(The exception to this is when reference fields are used with the de-reference operator.) - Aggregates are converted to Boolean, with the value of the Boolean indicating whether the aggregate is enabled (TRUE) or disabled (FALSE).
(The exception to this is when aggregates are used with the de-reference operator.) - Time fields are converted to DateTime.
- Unsigned Byte array fields are converted to Binary.
- Boolean fields are converted to Boolean.
- String fields are converted to String.
- All other field types are converted to String.