Some implementation details of the SQL language and 602SQL Server operation can be set with the compatibility options. This involves settings that are not specified in the SQL language standard or that had a different behaviour in previous 602SQL versions. To maintain the functionality of older applications and developing new application according to the SQL standard, the 602SQL Server allows you to set the extent of function compatibility with previous versions.
For example, when a type conversion is requested when evaluating a query that cannot be done, older 602SQL versions would return the NULL value, while the operation should not complete successfully according to the SQL standard.
Compatibility options may be changed any time when an application is running and different settings may be applied for different clients at the same time. Settings can be changed by calling the Set_sql_option API function. Settings can be viewed by calling the Get_sql_option function. You can view and change the actual setting on the server with the @@SQLOPTIONS server property.
| Option |
Value |
Applies to |
State if Set (602SQL syntax) |
State if not Set (SQL syntax) |
| SQLOPT_NULLEQNULL |
1 |
two NULL values comparison (*), affects the evaluation of some queries |
result is TRUE |
result is UNKNOWN |
| SQLOPT_NULLCOMP |
2 |
operation between NULL and another value (*) |
NULL is lesser in relations, and ignored in other operations |
relation result is UNKNOWN, other operation result is NULL |
| SQLOPT_RD_PRIVIL_VIOL |
4 |
trying to use a value that cannot be read when constructing a cursor |
NULL is used instead of the secret value |
NO_RIGHTS error occurs |
| SQLOPT_MASK_NUM_RANGE |
8 |
number type range overflow when converting |
result is NULL |
SQ_NUM_VAL_OUT_OF_RANGE error occurs |
| SQLOPT_MASK_INV_CHAR |
16 |
character string cannot be converted to the specified type |
result is NULL |
SQ_INV_CHAR_VAL_FOR_CAST error occurs |
| SQLOPT_MASK_RIGHT_TRUNC |
32 |
string type value is truncated from the right when converting |
characters are lost |
SQ_STRING_DATA_RIGHT_TRU error occurs |
| SQLOPT_EXPLIC_FREE |
64 |
deleting and dropping a record |
deleted records are dropped using the Free_deleted function |
deleted records are dropped immediately |
| SQLOPT_OLD_ALTER_TABLE |
128 |
ALTER TABLE syntax |
original form of ALTER TABLE is used (same as the Wizard) |
SQL standard form of ALTER TABLE is used |
| SQLOPT_DUPLIC_COLNAMES |
256 |
two columns in a query result have the same names |
names are maintained and can be distinguished by their prefix |
columns are automatically renamed |
| SQLOPT_USER_AS_SCHEMA |
512 |
using user names as a prefix for table names |
schema always used as a prefix |
if the prefix is the same the logged in user name, it is ignored |
| SQLOPT_DISABLE_SCALED |
1024 |
occurrence of non-integer number types in a query result (NUMERIC) |
if the MONEY type cannot be considered obsolete, it is automatically converted to the REAL type |
type is preserved |
| SQLOPT_ERROR_STOPS_TRANS |
2048 |
transaction reaction when an error occurs |
transaction is terminated |
transaction continues until COMMIT or ROLLBACK |
| SQLOPT_NO_REFINT_TRIGGERS |
4096 |
trigger execution when active referential integrity actions are running |
triggers are not executed |
triggers are executed |
| SQLOPT_USE_SYS_COLS |
8192 |
selection of system columns (_W5_ ...) in the SELECT clause * |
all columns are selected |
system columns are not selected |
| SQLOPT_CONSTRS_DEFERRED |
16384 |
check time of table constraints (except for UNIQUE) that are not set explicitly |
error occurs on commit (DEFERRED) |
error occurred immediately (IMMEDIATE) |
| SQLOPT_COL_LIST_EQUAL |
32768 |
when the UPDATE trigger with column list is executed |
when the same set of columns is updated |
when the set of updated columns has a non-empty intersection with the column list |
| SQLOPT_QUOTED_IDENT |
65536 |
method of interpreting quotation marks in SQL |
quotation marks specify a character string, just like the apostrophe |
quotation marks specify an identifier, just like the grave accent |
| SQLOPT_GLOBAL_REF_RIGHTS |
131072 |
utilizing privileges when evaluating a query |
global read privilege for columns used in the query conditions are required |
global read privilege for columns used are not required, columns that cannot be read are not used for evaluation |
| SQLOPT_REPEATABLE_RETURN |
262144 |
RETURN function behaviour in the function body |
RETURN statement defines the return value, but does not abort statement execution |
RETURN statement ends the function immediately |
| SQLOPT_OLD_STRING_QUOTES |
524288 |
string selection |
quotation mark and apostrophe are different characters for selecting a string (you may not use SET string = "aaa') -- since version 9.0 |
quotation mark and apostrophe are considered identical characters for selecting a string (you may write SET string = "aaa') -- used in versions 8.x |
(*) For strings with NULL value (= empty string) the 602SQL syntax is always used, regardless of the compatibility option (two NULL strings comparison is TRUE, etc.).
