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 |
|---|---|
| Aggregate |
An aggregate—an extension of a class in the database. This data type is only supported for use with the de-reference operator. |
| Binary | Varying length bit string. |
| Boolean | A single bit value, either TRUE or FALSE. |
| DateTime |
A date, time, or timestamp value. For information about the required format, see the following topics: For information about the required format for Date and Time constants, see Expressions.expr-primary-constant. |
| Integer | Signed 32-bit integer or signed 64-bit integer. |
| Interval |
A duration. For information about the required format, see Expressions.expr-primary-interval. |
| Location |
For use on systems that use Geo SCADA Expert's Geographical Location features. For information about the required format, see Expressions.expr-primary-location. |
| Real | Double precision floating point number. |
| Reference |
This data type is only supported for use with the de-reference operator. |
| Region |
For use on systems that use Geo SCADA Expert's Geographical Location features. For information about the required format, see Expressions.expr-primary-region. |
| String | Varying length character string. |
When writing an SQL statement, you have to declare constant values of a particular data type. To declare constant values for some data types, such as Binary, String, Integer, and Real, you use Scalar Literals. Refer to Scalar Literals for more information. Except for Boolean, to declare constant values for other data types, such as DateTime, Interval, Location, and Region, you have to precede the literals with the appropriate Keywords. Refer to the following topics 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 Geo SCADA Expert 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 Geo SCADA Expert database supports a wider range of types than the SQL language. For example the Geo SCADA Expert database supports different integer types such as Signed Byte, and Unsigned Word. Where possible Geo SCADA Expert 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.