Changes the values of database and connection options.
SET [ EXISTING ] [ TEMPORARY ] OPTION [ userid.| PUBLIC.]option-name = [ option-value ]
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
option-value With Syntax 1, option-value can be one of:
With Syntax 2, 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;
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, or for the current connection. The new setting can be made either temporary or permanent.
The classes of options that can be set with the SET OPTION statement are:
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.
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.
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:
Unless TEMPORARY is specified, an automatic commit is performed.
SQL/2008 Vendor extension.
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 grantees of the PUBLIC role:
SET OPTION PUBLIC.login_mode = 'Standard';
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|