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

SQL Anywhere 11.0.1 » SQL Anywhere Server - Database Administration » Configuring Your Database » Database options » Introduction to database options » Option classification

 

Compatibility options

The following options allow you to make SQL Anywhere behavior compatible with Adaptive Server Enterprise, or to support both old behavior and allow ISO SQL/2003 behavior.

For further compatibility with Adaptive Server Enterprise, some of these options can be set for the duration of the current connection using the Transact-SQL SET statement instead of the SQL Anywhere SET OPTION statement. See SET statement [T-SQL].

Default settings

The default setting for some of these options differs from the Adaptive Server Enterprise default setting. To ensure compatibility across your SQL Anywhere and Adaptive Server Enterprise databases, you should explicitly set each of the compatibility options listed in this section.

When a connection is made using the Open Client or JDBC interfaces, some option settings are explicitly set for the current connection to be compatible with Adaptive Server Enterprise. These options are listed in the following table.

Options for Open Client and JDBC connection compatibility with Adaptive Server Enterprise
Option Setting
allow_nulls_by_default Off
ansi_blanks Off
ansi_substring On
ansinull On
chained Off
continue_after_raiserror On
escape_character Off
on_tsql_error Continue for jConnect connections
time_format HH:NN:SS.SSS
timestamp_format YYYY-MM-DD HH:NN:SS.SSS
tsql_outer_joins Off
tsql_variables On
Transact-SQL and SQL/2003 compatibility options

The following table lists the compatibility options, their allowed values, and their default settings.

Option Values Default
allow_nulls_by_default option [compatibility] On, Off On
ansi_blanks option [compatibility] On, Off Off
ansi_close_cursors_on_rollback option [compatibility] On, Off Off
ansi_permissions option [compatibility] On, Off On
ansi_update_constraints option [compatibility] Off, Cursors, Strict Cursors
ansinull option [compatibility] On, Off On
chained option [compatibility] On, Off On
close_on_endtrans option [compatibility] On, Off On
continue_after_raiserror option [compatibility] On, Off On
conversion_error option [compatibility] On, Off On
date_format option [database] String YYYY-MM-DD
date_order option [database] MDY, YMD, DMY YMD
escape_character option [compatibility] Reserved Reserved
fire_triggers option [compatibility] On, Off On
isolation_level option [database] [compatibility] 0, 1, 2, 3 0
nearest_century option [compatibility] Integer (between 0 and 100 inclusive) 50
non_keywords option [compatibility] String (Comma-separated keywords list) Empty string (No keywords turned off)
on_tsql_error option [compatibility] Stop, Conditional, Continue Conditional
quoted_identifier option [compatibility] On, Off On
sql_flagger_error_level option [compatibility] Off, SQL:1992/Entry, SQL:1992/Intermediate, SQL:1992/Full, SQL:1999/Core, SQL:1999/Package, SQL:2003/Core, SQL:2003/Package, Ultralite Off
sql_flagger_warning_level option [compatibility] Off, SQL:1992/Entry, SQL:1992/Intermediate, SQL:1992/Full, SQL:1999/Core, SQL:1999/Package, SQL:2003/Core, SQL:2003/Package, Ultralite Off
string_rtruncation option [compatibility] On, Off On
time_format option [compatibility] String HH:NN:SS.SSS
timestamp_format option [compatibility] String YYYY-MM-DD HH:NN:SS.SSS
tsql_outer_joins option [compatibility] On, Off Off
tsql_variables option [compatibility] On, Off Off