Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

SET OPTION statement

Changes the values of database and connection options.

Syntax
  • Specify an option
    SET [ EXISTING ] [ TEMPORARY ] OPTION
     [ userid.| PUBLIC.]option-name = [ option-value ]
  • Specify an identifier (deprecated)
    SET [ EXISTING ] [ TEMPORARY ] OPTION
     [ userid.| PUBLIC.]option-name = [ identifier ]
    userid : identifier 
    option-name : identifier
    option-value : ON, OFF, NULL, string literal, number, hostvar, or @variable-name
Parameters
  • option-value

    When using the syntax for specifying an option, option-value can be one of:

    • the keywords ON, OFF, or NULL
    • a string literal value, within single quotation marks
    • a number of any valid format, including NUMERIC
    • within an Embedded SQL program, the value of a host variable hostvar
    • the value of a SQL variable with a variable name that must begin with an @ sign

    When using the syntax for specifying an identifier, you can specify any valid identifier as an option value. Also, the database server treats the name of the identifier as if it were a string literal enclosed within single quotes. For example, the following two statements are equivalent:

    SET TEMPORARY OPTION ansi_update_constraints = 'strict';
    SET TEMPORARY OPTION ansi_update_constraints = strict;
Remarks

The SET OPTION statement is used to change options that affect the behavior of the database server. Setting the value of an option can change the behavior for all users (public), for an individual user (including user-extended roles), or for the current connection. The new setting can be made either temporary or permanent.

Setting an option for a role is only meaningful for user-extended roles, and only applies to that user when they are logged in. A user who inherits a role does not inherit option settings.

The classes of options that can be set with the SET OPTION statement are:

  • Transact-SQL compatibility options
  • connection and database options
  • synchronization options
  • user-defined options
  • Option scope

    With most options, you can set their value at three levels of scope: public, user, and connection. Some specific options, such as login_mode, are restricted to the public level only. A connection option takes precedence over the other two levels, and user options take precedence over public options. You set a connection-level option by using the TEMPORARY keyword. If you set a user-level option for the current user, the corresponding connection-level option is set at the same time.

    By default, the option value applies to the currently logged on user ID that executed the SET OPTION statement. If you specify a user ID, the option value applies to that user. If you specify PUBLIC, the option value applies to all users who do not have an individual setting for the option.

  • TEMPORARY options

    By default, a new option value is made permanent unless the TEMPORARY keyword is specified. Adding the TEMPORARY keyword to the SET OPTION statement affects the duration of the change.

    When the SET TEMPORARY OPTION statement is not qualified with a user ID, the new option value is in effect only for the current connection.

    When SET TEMPORARY OPTION is used for the PUBLIC role, the change is in place for as long as the database is running. When the database is shut down, TEMPORARY options for the PUBLIC role revert back to their permanent value.

    Setting temporary options for the PUBLIC role offers a security benefit. For example, when the login_mode option is enabled, the database relies on the login security of the system on which it is running. Enabling it temporarily means that a database relying on the security of a Windows domain is not compromised if the database is shut down and copied to a local computer. In that case, the temporary enabling of the login_mode option reverts to its permanent value, which could be Standard, a mode where integrated logins are not permitted.

  • Removing option settings

    If option-value is omitted, the specified option setting is deleted from the database. If it was a user-level option setting, the value reverts back to the PUBLIC setting. If a TEMPORARY option is deleted, the option setting reverts back to the permanent setting for that user.

  • Option data types

    Options can have Boolean, numeric, or string values, but are always stored as strings in the database. Option settings are always returned as strings as the result of a property function or when returned as a result of a function or system stored procedure. Option values cannot be larger than the database page size.

  • User-defined options

    Any option, whether user-defined or not, must have a public setting before a user-specific value can be assigned. The database server does not support setting TEMPORARY values for user-defined options. For example, to create a user-defined option named ApplicationControl, you first execute the statement:

    SET OPTION PUBLIC.ApplicationControl = 'Default';

    This statement sets the ApplicationControl option to Default for all users, and takes effect with each new connection to the server. Subsequently, an individual user may establish their own setting for this option by executing a separate SET OPTION statement.

  • Restrictions

    If you use the EXISTING keyword, option values cannot be set for an individual user ID unless there is already a PUBLIC setting for that option.

    Caution

    Do not change option values while a cursor is open. Changing the option values while a cursor is open can lead to inconsistent results within the cursor. For example, changing the date_format option while a cursor is open can result in some rows being returned in the old format and some rows returned in the new format. To ensure that the rows in the result set are computed consistently using the new option value, open the cursor after the option value is changed.

There are several ways you can query the value of specific options for a connection or user.

The SET OPTION statement is ignored by the SQL Flagger.

Privileges

Any user can set their own options.

To set database options for other users or roles, including the PUBLIC role, you must have one of the following system privileges, depending upon which privilege the option requires:

  • SET ANY SYSTEM OPTION
  • SET ANY PUBLIC OPTION
  • SET ANY SECURITY OPTION
  • SET ANY USER DEFINED OPTION
Side effects

Unless TEMPORARY is specified, an automatic commit is performed.

Standards
  • ANSI/ISO SQL Standard

    Not in the standard.

Example

Set the date format option for all users without an individual setting:

SET OPTION PUBLIC.date_format = 'Mmm dd yyyy';

Set the wait_for_commit option to On:

SET OPTION wait_for_commit = 'On';

The following fragment is an Embedded SQL example:

EXEC SQL SET TEMPORARY OPTION date_format = :value;

Set the date_format option for the user that is currently connected. Future connections for the same user ID use this option value.

SET OPTION date_format = 'yyyy/mm/dd';

The following statement removes the setting of the date_format option for the current user ID. After executing this statement, the date_format setting for PUBLIC is used instead.

SET OPTION date_format=;

The following statement changes the login_mode option to Standard for all users.

SET OPTION PUBLIC.login_mode = 'Standard';