602SQL Documentation Index  

Compatibility Options

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.

All compatibility options are set according to the DefaultSQLOption server property after a new client connects to the server. If this property value has not changed, compatibility with older 602SQL version is turned ON for all options. This property can be set interactively in the Runtime Parameters window, SQL compatibility tab that is accessible from the System folder on the Control Panel.

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.

Compatibility options Overview:

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.).