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

SQL Anywhere 10.0.1 » SQL Anywhere Server - Database Administration » Database Options » Introduction to database options

Introduction to database options Next Page

Setting options

You set options with the SET OPTION statement. It has the following general syntax:

[ userid. | PUBLIC. ]option-name = [ option-value ]

Specify a user ID or group name to set the option for that user or group only. Every user belongs to the PUBLIC group. If no user ID or group is specified, the option change is applied to the currently logged on user ID that issued the SET OPTION statement.

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, the following statement applies an option change to the user DBA, if DBA is the user that issues it:

SET OPTION login_mode = Integrated;

The following statement applies a change to the PUBLIC user ID, a user group to which all users belong.

SET OPTION Public.login_mode = Standard;

If option-value is omitted, the specified option setting is deleted from the database. If it was a personal 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.

See SET OPTION statement.


Changing option settings while fetching rows from a cursor is not supported because it can lead to unreliable results. 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.


In databases that use a Turkish collation or are case sensitive, executing a query on SYSOPTION or a query like the following may not match any rows if the option name is used with the wrong case:

SELECT * FROM sa_conn_properties() WHERE propname = 'BLOCKING';

For information about the proper case for option names, see Alphabetical list of options.

Scope and duration of database options