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 » Option classification

Database options Next Page

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
ansinull On
chained Off
continue_after_raiserror On
date_format YYYY-MM-DD
date_order MDY
escape_character Off
float_as_double On
isolation_level 1
on_tsql_error Continue for jConnect connections
quoted_identifier Off
time_format HH:NN:SS.SSS
timestamp_format YYYY-MM-DD HH:NN:SS.SSS
tsql_hex_constant On
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_integer_overflow option [compatibility] On, Off On
ansi_permissions option [compatibility] On, Off On
ansi_substring option [compatibility]On, OffOn
ansi_update_constraints option [compatibility] Off, Cursors, Strict Cursors
ansinull option [compatibility] On, Off On
automatic_timestamp option [compatibility] On, Off Off
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 [compatibility] String YYYY-MM-DD
date_order option [compatibility] MDY, YMD, DMY YMD
divide_by_zero_error option [compatibility] On, Off On
escape_character option [compatibility] Reserved Reserved
fire_triggers option [compatibility] On, Off On
float_as_double option [compatibility] On, Off Off
isolation_level option [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
optimistic_wait_for_commit option [compatibility] On, Off Off
percent_as_comment option [compatibility] On, Off On
query_plan_on_open option [compatibility] On, Off Off
quoted_identifier option [compatibility] On, Off On
ri_trigger_time option [compatibility] Before, After After
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_hex_constant option [compatibility] On, Off On
tsql_outer_joins option [compatibility]On, OffOff
tsql_variables option [compatibility] On, Off Off