Characteristics of Sybase Open Client and jConnect connections

When SAP Sybase IQ is serving applications over TDS, it automatically sets relevant database options to values compatible with Adaptive Server default behavior. These options are set temporarily, for the duration of the connection only. The client application can override them at any time.

Default settings

The database options set on connection using TDS include:

Option Set to
allow_nulls_by_default Off
ansinull Off
chained Off
close_on_endtrans Off
date_format YYYY-MM-DD
date_order MDY
escape_character Off
isolation_level 1
on_tsql_error Continue
quoted_identifier Off
time_format HH:NN:SS.SSS
timestamp_format YYYY-MM-DD HH:NN:SS.SSS
tsql_variables On

How the startup options are set

The default database options are set for TDS connections using a system procedure named sp_tsql_environment. This procedure sets the following options: 

SET TEMPORARY OPTION allow_nulls_by_default='Off';
SET TEMPORARY OPTION ansinull='Off';
SET TEMPORARY OPTION chained='Off';
SET TEMPORARY OPTION close_on_endtrans='Off';
SET TEMPORARY OPTION date_format='YYYY-MM-DD';
SET TEMPORARY OPTION date_order='MDY';
SET TEMPORARY OPTION escape_character='Off';
SET TEMPORARY OPTION isolation_level='1';
SET TEMPORARY OPTION on_tsql_error='Continue';
SET TEMPORARY OPTION quoted_identifier='Off';
SET TEMPORARY OPTION time_format='HH:NN:SS.SSS';
SET TEMPORARY OPTION timestamp_format='YYYY-MM-DD HH:NN:SS.SSS';
SET TEMPORARY OPTION tsql_variables='On';
Note: Do not alter the sp_tsql_environment procedure. It is for system use only.

The procedure sets options only for connections that use the TDS communications protocol. This includes Sybase Open Client and JDBC connections using jConnect. Other connections (ODBC and embedded SQL) have the default settings for the database.

Although SAP Sybase IQ allows longer user names and passwords, TDS client names and passwords cannot exceed 30 bytes. If your password or user ID is longer than 30 bytes, attempts to connect over TDS (for example, using jConnect) return an invalid user or password error.

Parent topic: SAP Sybase IQ and Remote Data