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

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

SET OPTION statement [Interactive SQL]

Changes the values of Interactive SQL options.

  • Set an Interactive SQL option
    SET OPTION option-name = [ option-value ] | SET TEMPORARY OPTION option-name = [ option-value ]
    option-name : identifier | string | hostvar
    option-value : string | identifier | number
  • Save current Interactive SQL options permanently
  • List current database option settings

When you set an option using the SET OPTION syntax, the option setting is stored permanently and does not change unless another SET OPTION statement changes it.

Using the SET TEMPORARY OPTION syntax allows you to temporarily change an option setting. The temporary setting remains in effect until you close Interactive SQL. The next time you start Interactive SQL, the option reverts to its permanent setting.

Use the SET PERMANENT syntax to permanently save all current Interactive SQL option settings (any temporary settings become permanent).

If option-value is omitted, the specified option is set to its default value.

When you list the current database option settings, if there are temporary options settings for the database server, they are displayed instead of the permanent settings.

Interactive SQL option settings are stored on the client computer, not in the database.

The following table lists the Interactive SQL options.

Option Values Default
auto_commit option [Interactive SQL] On, Off Off
auto_refetch option [Interactive SQL] On, Off On
bell option [Interactive SQL] On, Off On
command_delimiter option [Interactive SQL] String ' ; '
commit_on_exit option [Interactive SQL] On, Off On
default_isql_encoding option [Interactive SQL] String Empty string
echo option [Interactive SQL] On, Off On
input_format option [Interactive SQL] TEXT, FIXED TEXT
isql_allow_read_client_file option [Interactive SQL] On, Off, Prompt Prompt
isql_allow_write_client_file option [Interactive SQL] On, Off, Prompt Prompt
isql_command_timing option [Interactive SQL] On, Off On
isql_escape_character option [Interactive SQL] Character ' \ '
isql_field_separator option [Interactive SQL] String ' , '
isql_maximum_displayed_rows option [Interactive SQL] All or a non-negative integer 500
isql_print_result_set option [Interactive SQL] Last, All, None Last
isql_quote option [Interactive SQL] String '
isql_show_multiple_result_sets option [Interactive SQL] On, Off Off
nulls option [Interactive SQL] String '(NULL)'
on_error option [Interactive SQL] Stop, Continue, Prompt, Exit, Notify_Continue, Notify_Stop, Notify_Exit Prompt
output_format option [Interactive SQL] TEXT, FIXED, HTML, SQL, XML TEXT
output_length option [Interactive SQL] Integer 0
output_nulls option [Interactive SQL] String Empty string
truncation_length option [Interactive SQL] Integer 256


  • ANSI/ISO SQL Standard

    Not in the standard.


The following example changes the value of the on_error option to continue:

SET OPTION on_error='continue';