SET OPTION

COMPILEMODE

The COMPILEMODE option sets the compile mode to DEFERRED, IMMEDIATE, INSTALL, or NOCHECK for the current namespace. The default is IMMEDIATE. Changing from DEFERRED to IMMEDIATE compile mode causes any classes in the Deferred Compile Queue to be compiled immediately. If all class compilations are successful, Caché sets SQLCODE to 0. If there are any errors, SQLCODE is set to -400. Class compilation errors are logged in the ^mtemp2 ("Deferred Compile Mode","Error"). If SQLCODE is set to -400, you should view this global structure for more precise error messages. The INSTALL compile mode is similar to the DEFERRED compile mode, but it should only be used for DDL installations where there is no data in the tables.

The NOCHECK compile mode is similar to IMMEDIATE, except that it skips checking of the following constraints when compiling: If a table is dropped, Caché does not check foreign key constraints in other tables that reference the dropped table. If a foreign key constraint is added, Caché does not check existing data to ensure that it is valid for this foreign key. If a NOT NULL constraint is added, Caché does not check existing data for NULLs or assign the field’s default value. If a UNIQUE or Primary Key constraint is deleted, Caché does not check if a foreign key in this table or another table references the dropped key.

LOCK_TIMEOUT

The LOCK_TIMEOUT numeric option lets you set the default lock timeout for the current process. The LOCK_TIMEOUT value is the number of seconds to wait when trying to establish a lock during SQL execution. This lock timeout is used when a locking conflict prevents the current process from immediately locking a record, table, or other entity for a LOCK, INSERT, UPDATE, DELETE, or SELECT operation. Caché SQL continue to try to establish the lock until the timeout expires, at which point an SQLCODE -110 or -114 error is generated.

Available values are positive integers and zero. The timeout setting is per process. You can determine the lock timeout setting for the current process using the GetProcessLockTimeout()Opens in a new tab method.

If you do not set the lock timeout for the current process, it defaults to the current system-wide lock timeout setting. If your ODBC connection disconnects and reconnects, the reconnected process uses the current system-wide lock timeout setting. The default system-wide lock timeout is 10 seconds.

For further details on locking conflicts and per-process and system-wide SQL lock timeout settings, refer to the LOCK command.

PKEY_IS_IDKEY

The PKEY_IS_IDKEY boolean option specifies whether primary keys are also ID keys system-wide. Available values are TRUE and FALSE. If TRUE, the primary key is created as an ID key. (That is, the primary key of the table also becomes the IDKey index in the class definition.) This may improve performance, but has the limitation that a primary key thus created cannot be subsequently modified. Once set, you cannot change the value assigned to a primary key, nor can you assign a different key as the primary key. Use of this option also changes the primary key collation default; primary key string values default to EXACT collation. If FALSE, the primary key and ID key are defined as independent, primary key values are changeable, and primary key string values default to the current collation type default, which is SQLUPPER by default.

To set the PKEY_IS_IDKEY option, you must have the %Admin_Manage:USE privilege. Otherwise, you receive an SQLCODE -99 error (Privilege Violation). Once set, this option takes effect system-wide for all processes. The system-wide default for this option can be set using:

• The $SYSTEM.SQL.SetDDLPKeyNotIDKey()Opens in a new tab method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.

• Go to the Management Portal, select System, Configuration, General SQL Settings. View the current setting of Are Primary Keys Created through DDL not ID Keys. If set to “Yes” (1), when a Primary Key constraint is specified through DDL it does not automatically become the IDKey index in the class definition. If “No” (0), it does become the IDKey index. Setting this value to “No” can give better performance, but means that the Primary Key fields cannot be updated. “Yes” is the default.

The PKEY_IS_IDKEY setting remains in effect until reset through another SET OPTION PKEY_IS_IDKEY or until the Caché Configuration is reactivated, which resets this parameter to the Caché System Configuration setting.

SUPPORT_DELIMITED_IDENTIFIERS

The SUPPORT_DELIMITED_IDENTIFIERS boolean option specifies whether delimited identifiers are supported system-wide. Available values are TRUE and FALSE. If TRUE, a string delimited by double quotation marks is considered an identifier within an SQL statement. If FALSE, a string delimited by double quotation marks is considered a string literal within an SQL statement.

To set the SUPPORT_DELIMITED_IDENTIFIERS option, you must have the %Admin_Manage:USE privilege. Otherwise, you receive an SQLCODE -99 error (Privilege Violation). Once set, this option takes effect system-wide for all processes. The SUPPORT_DELIMITED_IDENTIFIERS setting remains in effect until reset through another SET OPTION SUPPORT_DELIMITED_IDENTIFIERS or until the Caché Configuration is reactivated, which will reset this parameter to the System Configuration setting in the Management Portal.

The system-wide default for this option can be set as follows:

• The $SYSTEM.SQL.SetDelimitedIdentifiers()Opens in a new tab method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.

• Go to the Management Portal, select System, Configuration, General SQL Settings. View the current setting of Support Delimited Identifiers.

The default is “Yes” (1). If set to “Yes”, delimited identifiers are supported system-wide. For further details on delimited identifiers, see the “Identifiers” chapter of Using Caché SQL.

Locale Options

Locale options are keyword options used to set your Caché Locale settings for date, time, and numeric conventions for the current process. The available keyword options are AM, DATE_FORMAT, DATE_MAXIMUM, DATE_MINIMUM, DATE_SEPARATOR, DECIMAL_SEPARATOR, MIDNIGHT, MINUS_SIGN, MONTH_ABBR, MONTH_NAME, NOON, NUMERIC_GROUP_SEPARATOR, NUMERIC_GROUP_SIZE, PM, PLUS_SIGN, TIME_FORMAT, TIME_PRECISION, TIME_SEPARATOR, WEEKDAY_ABBR, WEEKDAY_NAME, and YEAR_OPTION. All of these options can be set to a literal, and all take a default (American English conventions). The TIME_PRECISION option is configurable (see below). If you set any of these options to an invalid value, Caché issues an SQLCODE -129 error (Illegal value for SET OPTION locale property). See the ObjectScript $ZDATETIME function for an explanation of date and time formats and options.

To configure TIME_PRECISION system-wide, go to the Management Portal, select System, Configuration, General SQL Settings. View and edit the current setting of Default time precision for GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP. This specifies the number of digits of precision for fractional seconds. The default is 0. The range of allowed values is 0 through 9 digits of precision. The actual number of meaningful digits of fractional seconds is platform-dependent.