DataDirect Connect ODBC for Sybase

Copyright 1998 INTERSOLV Inc. All rights reserved. INTERSOLV and DataDirect are registered trademarks of INTERSOLV, Inc. Connect ODBC is a trademark of INTERSOLV, Inc. Other company or product names mentioned herein may be trademarks or registered trademarks of their respective companies.


About Connect ODBC for Sybase

Connect ODBC for Sybase (the “Sybase driver”) supports the SQL Server 4.9.2 and the SQL Server System 10, System 11, and Adaptive Server 11.5 and 11.9 database systems from Sybase.

The driver file name is IVSYBnn.DLL, where nn is the revision level.


Sybase System 10/11 System Requirements
System Requirements

You must install the Sybase Open Client Library (version 10.0.4 or higher for Intel systems, and version 11.1.1 for Alpha systems) and the appropriate Sybase Net-Library to gain access to the Sybase server.

SYBPING is a tool provided with Sybase net-libraries to test connectivity from your client workstation to the database server (servers that are added using SQLEdit). Use this tool to test your connection.

SQLEdit is a tool that allows you to define servers and add them to SQL.INI.

Set the environment variable SYBASE to the directory where you have installed the SYBASE client. You set this environment variable in the Control Panel under System. For example:

SET SYBASE=C:\SQL10


Configuring Sybase System 10/11 Data Sources
Configuring Data Sources

To configure a Sybase data source, do the following:

1	Start the OLE DB Administrator to display list of data sources.

2	If you are configuring an existing data source, select the data source name and click Configure to display the ODBC Sybase Driver Setup dialog box.

If you are configuring a new data source, click Add to display a list of installed drivers. Select Sybase and click Finish to display the ODBC Sybase Driver Setup dialog box.

3	Specify a data source name, a server name, a database name and optionally, a description. Click Apply.

4	Click the Advanced tab to configure optional data source settings, such as server list and database name. Click Apply.

5	Click Translate to display the Select Translator dialog box, which lists the translators specified in the ODBC Translators section of the system information. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set.

Select a translator, then click OK to close this dialog box and perform the translation.

6	Click the Connection tab to configure optional data source settings, such as server list. Click Apply.

7	Click the Performance tab to configure advanced performance options. Click Apply.

8	Click OK or Cancel. If you click OK, the values you have specified become the defaults when you connect to the data source. You can change these defaults by using this procedure to reconfigure your data source. You can override these defaults by connecting to the data source using a connection string with alternate values.


Connecting to System 10/11 Using a Logon Dialog Box
Connecting to a Data Source Using a Logon Dialog Box

Some ODBC applications display a logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified.

In the Logon dialog box, do the following:

1	Type the case-sensitive name of the server containing the System 10 or System 11 database tables you want to access, or select the name from the Server Name drop-down list, which displays the server names you specified in the ODBC Sybase Driver Setup dialog box.

2	If required, type your case-sensitive login ID.

3	If required, type your case-sensitive password for the system.

4	Type the name of the database you want to access (case-sensitive) or select the name from the Database drop-down list, which displays the names you specified in the ODBC Sybase Driver Setup dialog box.

5	Click OK to complete the logon and to update the values in the system information.


Connecting to a Data Source Using a Connection String

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

You can specify either long or short names in the connection string. The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for Sybase is:

DSN=SYS10 TABLES;SRVR=IVSRVR;DB=PAYROLL;UID=JOHN;PWD=XYZZY

The paragraphs that follow  give the long and short names for each attribute, as well as a description. The defaults listed are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is your default.

ApplicationName (APP): The name used by Sybase to identify your application.

ApplicationUsingThreads (AUT): ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications. The default is 1, which makes the driver thread-safe. When using the driver with single-threaded applications, you may set this option to 0 to avoid additional processing required for ODBC thread safety standards.

ArraySize (AS): The number of rows the driver retrieves from the server for a fetch. This is not the number of rows given to the user. This increases performance by reducing network traffic. The initial default is 50 rows.

Charset (CS): The name of a character set corresponding to a subdirectory in $SYBASE/charsets.

CursorCacheSize (CCS): A value that determines the number of connections that the connection cache can hold. The default CursorCacheSize setting is 1. To set the connection cache, you must set the SelectMethod option to 1. Increasing the connection cache may increase performance of some applications, but requires additional database resources.

Database (DB): The name of the database to which you want to connect.

DataSourceName (DSN): A string that identifies a single connection to a Sybase database. Examples include “Accounting” or “Sys10-Serv1.”

DefaultLongDataBuffLen (DLDBL): An integer value that specifies, in 1024-byte multiples, the maximum length of data fetched from a TEXT or IMAGE column. The default is DefaultLongDataBuffLen=1024. You will need to increase this value if the total size of any long data exceeds 1 megabyte.

DirectoryServiceProvider (DSP): A string that indicates which Directory Service Provider the Sybase Open Client uses when connecting with this data source. The available Directory Service Providers can be found using the OpenClient/OpenServer Configuration Utility that is installed with Sybase Open Client version 11.1 or higher. If the client is not using Open Client version 11.1 or higher, this option is ignored.

EnableQuotedIdentifiers (EQI): EnableQuotedIdentifiers={0 | 1}. Specify 1 to allow support of quoted identifiers. The default is 0. This attribute is available for System 10 and System 11 servers.

InitializationString (IS): InitializationString={<Sybase set command>;...}. Supports the running of Sybase commands at connect time. Multiple commands must be separated by semicolons.

InterfacesFile (IFILE): The pathname to the interfaces file.

Language (LANG): The national language corresponding to a subdirectory in $SYBASE/locales.

LogonID (UID): The default logon ID used to connect to your Sybase database. This ID is case-sensitive. A logon ID is required only if security is enabled on your database. If so, contact your system administrator to get your logon ID.

OptimizePrepare (OP): OptimizePrepare={0 | 1 | 2}. A value that determines whether stored procedures are created on the server for every call to SQLPrepare.

When set to 0, stored procedures are created for every call to SQLPrepare. This setting can result in poor performance when processing static statements.

When set to 1, the initial default, the driver creates stored procedures only if the statement contains parameters. Otherwise, the statement is cached and run directly at SQLExecute time.

When set to 2, the driver never creates stored procedures.

Note: This attribute is ignored for Sybase 4.9.2 servers.

PacketSize (PS): PacketSize={-1 | 0 | x}. A number that determines the number of bytes per network packet transferred from the database server to the client. The correct setting of this attribute can improve performance.

When set to 0, the initial default, the driver uses the default packet size as specified in the Sybase server configuration.

When set to -1, the driver computes the maximum allowable packet size on the first connect to the data source and saves the value in the system information.

When set to x, an integer from 1 to 10, which indicates a multiple of 512 bytes (for example, PacketSize=6 means to set the packet size to 6 * 512 = 3072 bytes).

For you to take advantage of this connection attribute, you must configure the Sybase server for a maximum network packet size greater than or equal to the value you specified for PacketSize. For example:

sp_configure “maximum network packet size”, 5120
reconfigure
Restart Sybase Server

Note that the ODBC specification specifies a connect option, SQL_PACKET_SIZE, that offers this same functionality. To avoid conflicts with applications that may set both the connection string attribute and the ODBC connect option, they have been defined as mutually exclusive. If PacketSize is specified, you will receive a message “Driver Not Capable” if you attempt to call SQL_PACKET_SIZE. If you do not set PacketSize, then application calls to SQL_PACKET_SIZE are accepted by the driver

Password (PWD): A case-sensitive password.

Password Encryption (PE): Password Encryption={0 | 1}. A value that determines whether password encryption can be performed from the Open Client Library to the server. When set to 0, the default, this cannot be done. When set to 1, password encryption is enabled.

RaiseErrorPositionBehavior (REPB): RaiseErrorPositionBehavior={0 | 1}. A value that specifies when the error is returned and where the cursor is positioned when raiserror is encountered.

When set to 0 (the default), raiserror is handled separately from surrounding statements. The error is returned when raiserror is processed via SQLExecute, SQLExecDirect, or SQLMoreResults. The result set is empty.

When set to 1 (MS compatible), raiserror is handled with the next statement. The error is returned when the next statement is processed; the cursor is positioned on the first row of subsequent result set. This could result in multiple raiserrors being returned on a single execute.

SecurityServiceProvider (SSP): A string that indicates which Security Service Provider the Sybase Open Client uses when connecting with this data source. The available Security Service Providers can be found using the OpenClient/OpenServer Configuration Utility that is installed with Sybase Open Client version 11.1 or higher. If the client is not using Open Client version 11.1 or higher, this option is ignored.

SelectMethod (SM): SelectMethod={0 | 1}. A value that determines whether database cursors are used for Select statements. When set to 0, the initial default, database cursors are used. In some cases performance degradation can occur when performing large numbers of sequential Select statements because of the amount of overhead associated with creating database cursors.

When set to 1, Select statements are executed directly without using database cursors. When set to 1, the data source is limited to one active statement.

Note: This attribute is ignored for Sybase 4.9.2 servers.

ServerName (SRVR): The name of the server containing the Sybase tables you want to access. If not supplied, the initial default is the server name in the DSQUERY environment variable.

WorkstationID (WKID): The workstation ID used by the client.


Sybase System 10/11 Data Types
Data Types

The Sybase data types are mapped to the standard ODBC data types as follows:

Sybase	ODBC
binary	SQL_BINARY
bit	SQL_BIT
char	SQL_CHAR
datetime	SQL_TYPE_TIMESTAMP
* decimal	SQL_DECIMAL
float	SQL_FLOAT
image	SQL_LONGVARBINARY
int	SQL_INTEGER
money	SQL_DECIMAL
* numeric	SQL_NUMERIC
real	SQL_REAL
smalldatetime	SQL_TYPE_TIMESTAMP
smallint	SQL_SMALLINT
smallmoney	SQL_DECIMAL
sysname	SQL_VARCHAR
text	SQL_LONGVARCHAR
timestamp	SQL_VARBINARY
tinyint	SQL_TINYINT
varbinary	SQL_VARBINARY
varchar	SQL_VARCHAR
* Not supported with Sybase 4.9.2 servers.


Isolation and Lock Levels Supported by Sybase System 10/11
Isolation and Lock Levels Supported

Sybase supports isolation levels 0 (if the server version is 11 or higher), 1 (read committed, the default), and 3 (serializable). It supports page-level locking.


Sybase System 10/11 ODBC Conformance Level
ODBC Conformance Level

The API functions supported are listed in Supported ODBC Functions, found in the General Help on DataDirect ODBC Drivers. In addition, the following Level 2 functions are supported:

SQLColumnPrivileges

SQLForeignKeys

SQLPrimaryKeys

SQLProcedureColumns

SQLProcedures

SQLTablePrivileges

The driver supports the minimum SQL grammar.


Number of Connections and Statements Supported by Sybase System 10/11
Number of Connections and Statements Supported

The Sybase database system supports multiple connections and multiple statements per connection. If SelectMethod=1, Sybase data sources are limited to one active statement in manual commit mode.


Sybase System 10/11 ODBC Setup
General Tab, ODBC Sybase Driver Setup Dialog Box

Use the ODBC Sybase Driver Setup dialog to create new Sybase data sources or configure existing data sources.

Data Source Name: A string that identifies this Sybase data source configuration in the system information. Examples include "Accounting" or "Sys10-Serv1."

Description: An optional long description of a data source name. For example, “My Accounting Database” or “System 10 on Server number 1.”

Server Name: The name of the server that contains the Sybase tables you want to access. If not supplied, the server name in the DSQUERY environment variable is used.

Database Name: The name of the database to which you want to connect by default. If you do not specify a value, the default is the database defined by the system administrator for each user.

Advanced Tab

Displays the Advanced tab, where you can configure optional data source settings, such as initialization string.

Connection Tab

Displays the Connection tab, where you can configure optional data source settings, such as server list.

Performance tab

Displays the Performance tab, where you can configure advanced performance options, such as select method.

OK

Cancel

Apply


Advanced Options for Configuring Data Sources
Advanced Tab, ODBC Sybase Driver Setup Dialog Box

Use the Advanced tab on the ODBC Sybase Driver Setup dialog box to specify optional settings when you create new Sybase data sources or configure existing data sources.

Initialization String: Supports the running of Sybase commands at connect time. Multiple commands must be separated by semicolons.

Default Buffer Size for Long Columns: An integer value that specifies, in 1024-byte multiples, the maximum length of data fetched from a TEXT or IMAGE column. The default is 1,024 kilobytes. You will need to increase this value if the total size of any long data exceeds 1 megabyte.

Enable Quoted Identifiers: A setting that allows support of quoted identifiers in System 10 or System 11 servers.

Application Using Threads: A setting that ensures that the driver works with multi-threaded applications. You can clear this check box when using the driver with single-threaded applications. Turning off this setting avoids additional processing required for ODBC thread safety standards.

Cursor Positioning for raiserror: A value of 0 or 1 that specifies when the error is returned and where the cursor is positioned when raiserror is encountered.

When set to 0 (the default), raiserror is handled separately from surrounding statements. The error is returned when raiserror is processed via SQLExecute, SQLExecDirect, or SQLMoreResults. The result set is empty.

When set to 1 (MS compatible), raiserror is handled with the next statement. The error is returned when the next statement is processed; the cursor is positioned on the first row of subsequent result set. This could result in multiple raiserrors being returned on a single execute.

Translate Button

Displays the Select Translator dialog box, where you can translate your data from one character set to another. Choose the INTERSOLV OEM ANSI translator to translate your data from the IBM PC character set to the ANSI character set.

OK

Cancel

Apply


Connection Tab
Connection Tab, ODBC Sybase Driver Setup Dialog Box

Use the Connection tab on the ODBC Sybase Driver Setup dialog box to specify optional settings when you create new Sybase data sources or configure existing data sources.

Server List: The list of servers that appear in the Logon dialog box. Separate the server names with commas.

Database List: The databases that appear in the logon dialog box. Separate the names with commas.

Default Logon ID: The default logon ID used to connect to your Sybase database. This ID is case-sensitive. A logon ID is required only if security is enabled for the database you are connecting to. Your ODBC application may override this value or you may override this value in the logon dialog box or connection string.

Interfaces File: The pathname of the interfaces file. The default is the normal Sybase interfaces file.

Workstation ID: The workstation ID used by the client.

Charset: The name of a character set corresponding to a subdirectory in $SYBASE/charsets. The default is the setting on the Sybase server.

Application Name: The name used by Sybase to identify your application.

Language: The national language corresponding to a subdirectory in $SYBASE/locales. The default is English.

Directory Service Provider: A string that indicates which Directory Service Provider the Sybase Open Client uses when connecting with this data source. The available Directory Service Providers can be found using the OpenClient/OpenServer Configuration Utility that is installed with Sybase Open Client version 11.1 or higher. If the client is not using Open Client version 11.1 or higher, this option is ignored.

Security Service Provider: A string that indicates which Security Service Provider the Sybase Open Client uses when connecting with this data source. The available Security Service Providers can be found using the OpenClient/OpenServer Configuration Utility that is installed with Sybase Open Client version 11.1 or higher. If the client is not using Open Client version 11.1 or higher, this option is ignored.

Password Encryption: A value that determines whether password encryption can be performed from the Open Client Library to the server. Checking this box enables password encryption.

OK

Cancel

Apply


ODBC Sybase System 10/11 Performance Setup Dialog Box
Performance Tab, ODBC Sybase Driver Setup Dialog Box

Use the Performance tab on the ODBC Sybase Driver Setup dialog box to specify optional settings when you create new Sybase data sources or configure existing data sources.

Prepare Method: A value that determines whether stored procedures are created on the server for every call to SQLPrepare. When set to 0, stored procedures are created for every call to SQLPrepare, which can decrease performance when processing static statements.

When set to 1, the initial default, the driver creates stored procedures only if the statement contains parameters. Otherwise, the statement is cached and executed directly at SQLExecute time.

When set to 2, the driver never creates stored procedures. Note: This setting is ignored when connected to Sybase 4.9.2 servers.

Fetch Array Size: The number of rows the driver retrieves from the server for each fetch. This is not the number of rows given to the user.  The default is 50 rows.

Select Method: A value that determines whether database cursors are used for Select statements. When set to 0, the initial default, database cursors are used.

When set to 1, Select statements are executed directly without using database cursors. When set to 1, the data source is limited to one active statement. Note: This setting is ignored when connected to Sybase 4.9.2 servers.

Packet Size: A value that determines the number of bytes per network packet transferred from the database server to the client. The correct setting of this attribute can improve performance.

When set to 0, the initial default, the driver uses the default packet size as specified in the Sybase server configuration.

When set to -1, the driver computes the maximum allowable packet size on the first connect to the data source and saves the value in the system information.

When set to x, an integer from 1 to 10, which indicates a multiple of 512 bytes (for example, Packet Size=6 means to set the packet size to 6 * 512 = 3072 bytes).

For you to take advantage of this connection attribute, you must configure the System 10 server for a maximum network packet size greater than or equal to the value you specified for Packet Size. For example,

sp_configure “maximum network packet size”, 5120
reconfigure
Restart Sybase Server

Note that the ODBC specification specifies a connect option, SQL_PACKET_SIZE, that offers this same functionality. To avoid conflicts with applications that may set both the connection string attribute and the ODBC connect option, they have been defined as mutually exclusive. If PacketSize is specified, you will receive a message “Driver Not Capable” if you attempt to call SQL_PACKET_SIZE. If you do not set Packet Size, then application calls to SQL_PACKET_SIZE are accepted by the driver.

Connection Cache: A value that determines the number of connections that the connection cache can hold. The default Connection Cache setting is 1. To set the connection cache, you must set the Select Method option to 1 - Direct. Increasing the connection cache may increase performance of some applications, but requires additional database resources.

OK

Cancel

Apply


Logon to Sybase System 10/11
Logon to Sybase Dialog Box

Server Name: Type the case-sensitive name of the server containing the System 10 or System 11 database tables you want to access, or select the name from the Server Name drop-down list, which displays the server names you specified in the ODBC Sybase Driver Setup dialog box.

Login ID: If required, type your login ID (case-sensitive).

Password: If required, type your password for the system (case-sensitive).

Database: Type the name of the database you want to access (case-sensitive) or select the name from Database drop-down list, which displays the database names you specified in the ODBC Sybase Driver Setup dialog box.


Apply Button

Writes the settings you have specified to the system information. These settings remain in effect until you change them in this dialog box. Clicking Cancel does not affect settings that have been applied.


OK Button

Writes the settings you have specified to the system information and closes the dialog box.


Cancel Button

Closes the dialog box without saving settings that have not been applied.