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.
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. By default, the option value applies to the currently logged on user ID that executed the SET OPTION statement.
For example, the following statement applies an option change to the user DBA, if DBA is the user executing the SQL statement:
SET OPTION precision = 40;
However the following statement applies the change to the PUBLIC user ID, a group to which all users belong:
SET OPTION PUBLIC.login_mode = 'Standard';
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 user ID, the change is in place for as long as the database is running. When the database is shut down, TEMPORARY options for the PUBLIC group revert back to their permanent value.
Setting temporary options for the PUBLIC user ID 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 Only users with DBA authority have the authority to set an option for the PUBLIC user ID or for other database users.
If you use the EXISTING keyword, option values cannot be set for an individual user ID unless there is already a PUBLIC user ID setting for that option.
Do not change option settings while fetching rows from an open cursor because it can result in ill-defined behavior whose semantics are not guaranteed. For example, changing the date_format setting while fetching from a cursor would lead to different date formats among the rows in the result set.
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.
None required to set your own options.
DBA authority is required to set database options for another user or PUBLIC.
If TEMPORARY is not 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 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=;
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|