ASA SQL Reference
  SQL Statements

SET OPTION statement

Description 

Use this statement to change the values of database options.

Syntax 

SET [ EXISTING ] [ TEMPORARY ] OPTION
 [ userid.| PUBLIC.]option-name = [ option-value ]

userid :    identifier | string | hostvar

option-name :  identifier | string | hostvar

option-value :  hostvar (indicator allowed)
| string
| identifier
| number

Usage 

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.

You can set options at three levels of scope: public, user, and temporary. A temporary option takes precedence over other options, and user options take precedence over public options. If you set a user level option for the current user, the corresponding temporary option gets set as well.

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.

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.

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 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 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 reverts 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.

Permissions 

None required to set your own options.

DBA authority is required to set database options for another user or PUBLIC.

Side effects 

If TEMPORARY is not specified, an automatic commit is performed.

See also 

Database options

Compatibility options

Replication options

SET OPTION statement [Interactive SQL]

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.

• Sybase    Not supported by Adaptive Server Enterprise. Adaptive Server Anywhere does support some Adaptive Server Enterprise options using the SET statement.

Example 

Set the date format option to on:

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

Set the date format option to off:

SET OPTION public.date_format =;

Set the wait_for_commit option to on:

SET OPTION wait_for_commit = 'on';

Following are two Embedded SQL examples.

1. EXEC SQL SET OPTION :user.:option_name = :value;
2. EXEC SQL SET TEMPORARY OPTION Date_format = 'mm/dd/yyyy';