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

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - Database Administration » Database configuration » Database options » Scope and duration of database options


How to set database options using the SET OPTION statement

Set database options by using the SET OPTION statement. It has the following general syntax:

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

You can specify a user ID or a role name to set the option for that user or grantees of that role only. You can also specify the PUBLIC role name, which sets the options for grantees of the PUBLIC role. By default, new users are granted the PUBLIC role. If no user ID or role is specified, the option change is applied to the currently logged in 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.

If option-value is omitted, the specified option value reverts to the value of that option in the PUBLIC role. If a TEMPORARY option is removed, the option value reverts to the permanent setting.


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.


In databases that use a Turkish collation or are case-sensitive, executing a query on the SYSOPTION system view or a query similar to the following might not match any rows if the option name is used with the wrong case:

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