SQL Statements Supported by Reality
This section lists the SQL features and statements supported by Reality, both in ODBC and JDBC, and gives details of any features not fully supported. It also describes the SQL TCL command - this can be used to test SQL statements while logged on to Reality.
Reality SQL
The SQL syntax used by Reality is based on the X/Open and SQL Access Group SQL specification (1992) and is broadly compliant with the ODBC core language. Exceptions are listed below.
Data Types
The following table lists the data types defined in the ODBC SQL grammar and the level of support provided by SQL for Reality.
|
Data Type |
Level of support |
|---|---|
|
BIGINT |
Fully supported. |
|
BINARY |
Supported in SQL/ODBC. Not supported in SQL/JDBC. |
|
BIT |
Fully supported. |
|
CHAR |
Supported. Reality does not always follow the rules for manipulating this type. CHAR columns are fixed length, space padded where necessary. Reality is naturally variable length and sometimes fails to correctly space pad CHAR columns. |
|
DATE |
Fully supported. Uses Reality internal date format (days since 31st December 1967). |
|
DECIMAL |
Fully supported. |
|
DOUBLE |
Supported. Reality does not fully adhere to the rules of floating point arithmetic. As a result, very large and very small numbers are expressed to a higher degree of accuracy than would be expected. |
|
FLOAT |
Supported. See DOUBLE. |
|
INTEGER |
Fully supported. |
|
LONGVARBINARY |
Supported in SQL/ODBC. Not supported in SQL/JDBC. |
|
LONGVARCHAR |
Fully supported. |
|
NUMERIC |
Supported. The rules for the behaviour of this data type in arithmetic expressions are very like, but subtly different from those for DECIMAL. In Reality, NUMERIC is exactly the same as DECIMAL. |
|
REAL |
Supported. See DOUBLE. |
|
SMALLINT |
Fully supported. |
|
TIME |
Fully supported by ODBC and JDBC drivers, but not fully supported by SQLM. SQLM cannot automatically recognise TIME fields and generate the correct column definitions. Uses Reality internal time format (seconds since midnight). |
|
TIMESTAMP |
Fully supported by ODBC and JDBC drivers, but not fully supported by SQLM. See TIME. Uses a non-standard representation (seconds since midnight on 31st December 1967). Note that this format is not understood by English or any other Reality processor. |
|
TINYINT |
Fully supported. |
|
VARBINARY |
Supported in SQL/ODBC. Not supported in SQL/JDBC. |
|
VARCHAR |
Fully supported. |
Functions
The SQL STRING, TIME/DATE and CONVERSION scalar functions are fully supported.
SQL for Reality does not support the NUMERIC and SYSTEM scalar functions.
DISTINCT Keyword
SQL for Reality does not support the DISTINCT keyword in set functions (AVG, COUNT, MAX, MIN, SUM).
SQL Views
SQL for Reality does not support SQL views. A limited alternative is offered by the Create Alternative View option in SQLM.
Null Handling
The way in which null data (that is, an empty cell) is handled depends on how the table was created.
In tables created with the SQL CREATE TABLE statement, Reality provides full support for null values.
Tables created with SQLM have the following limitations:
- In columns that are not using Special Null Handling, it is not possible to distinguish between NULL and empty string values.
- In columns in which null data is allowed, empty values are interpreted as NULL.
- In numeric or datetime columns defined as NOT NULL, empty values are interpreted as the number zero.
- In character columns defined as NOT NULL, empty values are interpreted as empty strings.
Binary Data Types
The binary data types (BINARY, LONGVARBINARY and VARBINARY) are only supported in tables created using the SQL CREATE TABLE statement. They cannot be used in tables created using the SQLM utility.