The following options allow Sybase IQ behavior to be compatible with Adaptive Server Enterprise, or to both support old behavior and allow ISO SQL92 behavior.
For further compatibility with Adaptive Server Enterprise, you can set some of these options set for the duration of the current connection using the Transact-SQL SET statement instead of the Sybase IQ SET OPTION statement. For a listing of such options, see the SET statement [ESQL].
The default setting for some of these options differs from the Adaptive Server Enterprise default setting. To ensure compatible behavior, you should explicitly set the options.
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 Table 2-2.
For information on how the settings are made, see Reference: Building Blocks, Tables, and Procedures.
Option |
ASE-compatible setting |
|---|---|
ALLOW_NULLS_BY_DEFAULT |
OFF |
ANSINULL |
OFF |
CHAINED |
OFF |
CONTINUE_AFTER_RAISERROR |
ON |
DATE_FORMAT |
YYYY-MM-DD |
DATE_ORDER |
MDY |
ESCAPE_CHARACTER |
OFF |
ISOLATION_LEVEL |
1 |
ON_TSQL_ERROR |
CONDITIONAL |
QUOTED_IDENTIFIER |
OFF |
TIME_FORMAT |
HH:NN:SS.SSS |
TIMESTAMP_FORMAT |
YYYY-MM-DD HH:NN:SS.SSS |
TSQL_VARIABLES |
OFF |
Table 2-3 lists the compatibility options, their allowed values, and their default settings.
See “General database options” and “DBISQL options” for lists of the other classes of options.
Option |
Values |
Default |
|---|---|---|
ALLOW_NULLS_BY_DEFAULT |
ON, OFF |
ON |
ANSI_BLANKS* |
ON, OFF |
OFF |
ANSI_CLOSE_CURSORS_ON_ROLLBACK |
ON |
ON |
ANSI_INTEGER_OVERFLOW* |
||
ANSI_PERMISSIONS |
ON, OFF |
ON |
ANSINULL |
ON, OFF |
ON |
ANSI_SUBSTRING |
ON, OFF |
ON |
ANSI_UPDATE_CONSTRAINTS |
OFF, CURSORS, STRICT |
CURSORS |
ASE_BINARY_DISPLAY |
ON, OFF |
OFF |
ASE_FUNCTION_BEHAVIOR |
ON, OFF |
OFF |
CHAINED |
ON, OFF |
ON |
CLOSE_ON_ENDTRANS |
ON |
ON |
CONTINUE_AFTER_RAISERROR |
ON, OFF |
ON |
CONVERSION_ERROR |
ON, OFF |
ON |
DIVIDE_BY_ZERO_ERROR |
ON, OFF |
ON |
ESCAPE_CHARACTER* |
Reserved |
Reserved |
FIRE_TRIGGERS* |
ON, OFF |
ON |
NEAREST_CENTURY |
0 – 100 |
50 |
NON_KEYWORDS |
Comma-separated keywords list |
No keywords turned off |
ON_TSQL_ERROR |
STOP, CONTINUE, CONDITIONAL |
CONDITIONAL |
QUERY_PLAN_ON_OPEN* |
||
QUOTED_IDENTIFIER |
ON, OFF |
ON |
RI_TRIGGER_TIME* |
||
SQL_FLAGGER_ERROR_LEVEL |
E, I, F, W, OFF, SQL:1992/Entry, SQL:1992/Intermediate, SQL:1992/Full, SQL:1999/Core, SQL:1999/Package, SQL:2003/Core, SQL:2003/Package |
OFF |
SQL_FLAGGER_WARNING_LEVEL |
E, I, F, W, OFF, SQL:1992/Entry, SQL:1992/Intermediate, SQL:1992/Full, SQL:1999/Core, SQL:1999/Package, SQL:2003/Core, SQL:2003/Package |
OFF |
STRING_RTRUNCATION |
ON, OFF |
ON |
TEXTSIZE* |
||
TSQL_HEX_CONSTANT* |
||
TSQL_VARIABLES |
ON, OFF |
OFF |
Notes
An asterisk (*) next to the option name in Table 2-3 indicates an option currently
not supported by Sybase IQ.
