Characteristics of Open Client and jConnect connections

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

The database options that are set on connection using TDS are as follows:

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 TSQL_VARIABLES='ON';
SET TEMPORARY OPTION ANSI_BLANKS='ON';
SET TEMPORARY OPTION TSQL_HEX_CONSTANT='ON';
SET TEMPORARY OPTION CHAINED='OFF';
SET TEMPORARY OPTION QUOTED_IDENTIFIER='OFF';
SET TEMPORARY OPTION ALLOW_NULLS_BY_DEFAULT='OFF';
SET TEMPORARY OPTION AUTOMATIC_TIMESTAMP='ON';
SET TEMPORARY OPTION ANSINULL='OFF';
SET TEMPORARY OPTION CONTINUE_AFTER_RAISERROR='ON';
SET TEMPORARY OPTION FLOAT_AS_DOUBLE='ON';
SET TEMPORARY OPTION ISOLATION_LEVEL='1';
SET TEMPORARY OPTION DATE_FORMAT='YYYY-MM-DD';
SET TEMPORARY OPTION TIMESTAMP_FORMAT='YYYY-MM-DD HH:NN:SS.SSS';
SET TEMPORARY OPTION TIME_FORMAT='HH:NN:SS.SSS';
SET TEMPORARY OPTION DATE_ORDER='MDY';
SET TEMPORARY OPTION ESCAPE_CHARACTER='OFF' 

The procedure only sets options for connections that use the TDS communications protocol. This includes Open Client and JDBC connections using jConnect. (Note that jConnect sets QUOTED_IDENTIFIER to ON during its login sequence.) Other connections (ODBC and Embedded SQL) have the default settings for the database.

The sp_iq_process_login system procedure, which is the default setting for the LOGIN_PROCEDURE option, performs Sybase IQ User Administration and then calls sp_login_environment, which in turn calls sp_tsql_environment for TDS connections.

1. Create a procedure that sets the database options you want. For example, you could use a procedure such as the following:

   CREATE PROCEDURE my_startup_procedure()
   BEGIN
     IF connection_property('CommProtocol')='TDS' THEN
       SET TEMPORARY OPTION QUOTED_IDENTIFIER='OFF';
     END IF
   END
   

   This procedure changes only the QUOTED_IDENTIFIER option from the default settings.

2. To use the Sybase IQ User Administration feature, make a copy of the sp_iq_process_login procedure, which is found in your $ASDIR/scripts directory. Then edit sp_iq_process_login to call your new procedure instead of sp_login_environment. The text of sp_iq_process_login is found in your $ASDIR/scripts directory. The line you must edit is:

   call sp_login_environment();
   

   Never edit the original, installed system procedures.

3. Future connections will use the procedure.

If you do not wish to use the Sybase IQ User Administration facility, you can skip step 2 and instead set the LOGIN_PROCEDURE option to the name of a new procedure:

SET OPTION LOGIN_PROCEDURE= 'dba.my_startup_procedure'

If you are developing Open Client applications, you should be aware of mappings between the data types supported by Sybase IQ and those expected by Open Client. For more information about these data type mappings, see the chapter entitled “The Open Client Interface” in Adaptive Server Anywhere Programming Interfaces Guide.