![SET DESCRIPTOR statement [ESQL]](gif/backward.gif)
![SET OPTION statement [ISQL]](gif/forward.gif)
Reference Manual
CHAPTER 9. SQL Statements
To change database options.
SET [ TEMPORARY ] OPTION
... [ userid.| PUBLIC.]option-name = [ option-value ]
userid: { identifier | string | host-variable }
option-name: { identifier | string | host-variable }
option-value: { host-variable (indicator allowed)
| string
| identifier
| number }
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.
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 or only for an individual user. The scope of the change can be either temporary or permanent.
The classes of options are:
General database options
Transact-SQL compatibility
Replication database options
For a listing and description of all available options, see Database Options.
If you specify a user ID, the option value applies to that user (or, for a group user ID, the members of that group). If you specify PUBLIC, the option value applies to all users who don't have an individual setting for the option. By default, the option value applies to the currently logged on user ID that issued the SET OPTION statement..
For example, the following statement applies an option change to the user DBA, if DBA is the user issuing the SQL statement:
SET OPTION login_mode = mixed
However the following statement applies the change to the PUBLIC user ID, a user group to which all users belong.
SET OPTION Public.login_mode = standard
Only users with DBA privileges have the authority to set an option for the PUBLIC user ID.
In Embedded SQL, database options can be set only temporarily.
Changing the value of an option for the PUBLIC user ID sets the value of the option for any user which has not SET their own value. Option values cannot be set for an individual user ID unless there is already a PUBLIC user ID setting for that option.
Users can use the SET OPTION statement to change the values for their own user ID. Setting the value of an option for a user id other then your own is permitted only if you have DBA authority.
Adding the TEMPORARY keyword to the SET OPTION statement changes the duration that the change takes effect. By default, the option value is permanent: it will not change until it is explicitly changed using the SET OPTION statement.
When the SET TEMPORARY OPTION statement is applied using an individual user ID, the new option value is in effect as long as that user is logged in to the database.
When the SET TEMPORARY OPTION is used with 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 user ID revert back to their permanent value.
Setting an option for the PUBLIC user ID temporarily as opposed to permanently, offers a security advantage. For example, when the LOGIN_MODE option is enabled, the database relies on the log in security of the system on which it is running. Enabling it temporarily means that a database relying on the security of a Windows NT domain will not be compromised if the database is shut down and copied to a local machine. In that case, the temporary enabling of the LOGIN_MODE option will revert to its permanent value, which could be Standard, a mode where integrated logins are not permitted.
If option-value is omitted, the specified option setting will be deleted from the database. If it was a personal option setting, the value will revert back to the PUBLIC setting. If a TEMPORARY option is deleted, the option setting will revert back to the permanent setting.
Caution
Changing option settings while fetching rows from a cursor is not supported, as it can lead to ill-defined behavior. 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. Do not change option settings while fetching rows.
SQL/92 Vendor extension.
Sybase Not supported by Adaptive Server Enterprise. Adaptive Server Anywhere does support some Adaptive Server Enterprise options using the SET statement.
Set the date format option.
SET OPTION public.date_format = 'Mmm dd yyyy' ;
Set the 'wait for commit' option to on.
SET OPTION waitefor_commit = 'on' ;
1. EXEC SQL SET OPTION :user.:option_name = :value; 2. EXEC SQL SET TEMPORARY OPTION Date_format = 'mm/dd/yyyy';