Title | Contents | Previous | Next | Index

Chapter 5

Connect ADO for Sybase

Connect ADO for Sybase (the "Sybase data provider") supports SQL Server System 11, Sybase Adaptive Server 11.5 and higher, and Sybase Adaptive Server Enterprise 12.0 and 12.5.

The Sybase data provider can be installed in the Windows environments (see "Support for Windows").

This chapter describes how to set up Connect ADO for Sybase and provides data provider-specific information. For more information on developing applications for the Sybase data provider, see the following sections:

Creating Data Sources

You create or define a data source using the DataDirect Technologies Configuration Manager. To create a data source for Connect ADO for Sybase using the Configuration Manager, see "Defining a Data Source".

After you create a data source using the Configuration Manager, you specify connection values in the data provider's Setup dialog box. For more information, see "Configuring Data Sources".

Configuring Data Sources

You use the provider Setup dialog box to specify connection values for the provider and a data source. The data source can be the default data source that comes with the provider or a data source that you have created with the Configuration Manager.

The values that you specify in the Setup dialog box become the defaults when you connect to the data source. You can change these defaults by reconfiguring your data source.

To configure a provider data source:

  1. Start the DataDirect Technologies Configuration Manager by double-clicking its icon in the Connect ADO program group.

  2. Double-click the Data Sources folder to display a list of data sources.

  3. Select the data source name and review the settings in the right pane of the Configuration Manager.

  4. Perform one of the following actions to display the DataDirect Sybase ADO Provider Setup dialog box:

NOTE: The General tab displays only fields that are required for creating a data source. The fields on all other tabs are optional, unless noted otherwise.

  1. On the General tab, provide the following information; then, click Apply.

    Data Source Name: The data source name is supplied automatically in this noneditable field.

    Description: Type an optional long description of a data source name. For example, "My Accounting Database" or "System 12 on Server number 1."

    Server Name: Type the host name of the server that contains the Sybase Server you want to access.

    HA Server Name: (Optional) Type the name of the server that provides the Sybase connection failover capability. For information on the connection failover feature, refer to your Sybase documentation.

    Database Name: Type 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.

    Network Protocol: Select the name of the Sybase netlib to use for application communication. The options are Winsock and Named Pipes. The default is Winsock.

    Server Port or Name Pipes Address: Type the address of the Sybase server. If the Sybase server uses TCP/IP, the default value for the Server Port is 5000. If you selected Named Pipes in the Network Protocol field, type the string address of the server after the server name, for example, \\machine_name\pipe\sybase\query.

    HA Server Port or Name Pipes Address: (Optional) Type the address of the failover server. If the Sybase server uses TCP/IP, the default value for the Server Port is 5000. If you selected Named Pipes in the Network Protocol field, type the string address of the server after the server name, for example, \\machine_name\pipe\sybase\query.

  2. Optionally, click the Advanced tab to specify data source settings.

    The Advanced tab of the Sybase ADO Provider Setup dialog box

    On this tab, provide any of the following optional information; then, click Apply.

    Default Length for Long Data (in KB): Type an integer value that specifies, in 1024-byte multiples, the maximum length of data fetched from a TEXT or IMAGE column. The default is 1024 kilobytes. You will need to increase this value if the total size of any long data exceeds 1 megabyte.

    Enable Quoted Identifiers: Select this check box to allow support of quoted identifiers. By default, the check box is not selected; quoted identifiers are not enabled.

    Initialization String: Type a string that supports the running of Sybase commands at connect time. Separate multiple commands by commas.

    Raise Errors: Select when the error is returned and where the cursor is positioned when raise error is encountered. The default is MS Compatible.

    Print Statements: Select whether print statements are sent back to the user as separate result sets. The default is MS Compatible.

    Extended Error Info: Select whether to return extended Sybase Server error information. The default is FALSE; only the Native error code and error message text is returned. If you select TRUE, additional Sybase Server error information is returned.

    Stored Proc Row Count: Select the row count for SQL statements in the stored procedure.

    Tightly Coupled Distributed Transactions: Select this check box to use tightly coupled distributed transactions to ensure that multiple connections within the same distributed transaction do not obey each other's locks when connected to a Sybase ASE version 12 database.

    When this check box is not selected, the default, the overall performance of the data provider is better. However, multiple connections within the same distributed transaction may hang each other because the connections do not obey each other's locks.

    This option is valid only when the data provider is enlisted in a distributed transaction and when it is connected to a Sybase ASE version 12 database. Otherwise, this option is ignored.

  3. Optionally, click the Connection tab to specify data source settings.

    The Connection tab of the Sybase ADO Provider Setup dialog box

    On this tab, provide any of the following optional information; then, click Apply.

    Default Logon ID: Type the default logon ID used to connect to your Sybase database. This ID is case-sensitive. IMPORTANT: A value entered here overrides values passed in a connection string. Leave this field blank if you intend to pass the Logon ID in a connection string.

    Workstation ID: Type the workstation ID used by the client.

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

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

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

    Connect Timeout(Sec): Type an integer value for the time, in seconds, to wait for initialization to complete. The default is 15 seconds.

    Interfaces File: Type the path name of the Interfaces file. If you do not specify value in the Interfaces File Server Name field, then the provider looks for the path name of the Interfaces file in the Registry under HKEY_LOCAL_MACHINE\SOFTWARE\DataDirect\
    InterfacesFile. If this Registry value is empty, then the provider attempts to open the SQL.INI file found in the same directory as the provider and use it as the Interfaces file.

    Server Name: Type the name of the section in the Interfaces file that contains the network connection information for the Sybase server you want to access. The section name typically is the host name of the server that contains the Sybase server you want to access.

  4. Optionally, click the Performance tab to specify data source performance settings.

    The Performance tab of the Sybase ADO Provider Setup dialog box

    On this tab, provide any of the following optional information; then, click Apply.

    Select Method: Select a value that determines whether database cursors are used for Select statements. The initial default is Direct; Select statements are executed directly without using database cursors. With this setting, the data source is limited to one active statement. When set to Cursor, database cursors are used.

    Prepare Method: Select a value that determines whether stored procedures are created on the server for every call to ICommand::Prepare.

    Row Cache Size: Type the number of rows the data provider retrieves from the server for each fetch. The initial default is 50 rows. To optimize performance, set this value according to the size of the rowsets that you expect to be returned. This allows multiple statements to be executed on a connection without using database cursors.

    Packet Size: Type 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 x, an integer from 1 to 10, which indicates a multiple of 512 bytes (for example, if you enter 6, you set the packet size to 6 * 512 = 3072 bytes). The initial default is 1.

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

    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

  5. Optionally, click the Options tab to add connection values specific to the Sybase data provider. See the online help for the latest authorized values.

    The Option tab of the Sybase ADO Provider Setup dialog box

    On this tab, enter authorized values; then, click Apply.

    IMPORTANT: Use only authorized attributes. The properties that you type in the Options field override settings in the Setup dialog box, and can have serious effects on the operation of the Sybase data provider.

  6. Optionally, click the Trace tab to enable tracing options for the data provider.

    NOTE: Settings selected on this tab apply to all data sources for the data provider.

    The Trace tab of the Sybase ADO Provider Setup dialog box

    On this tab, provide any of the following optional information; then, click Apply.

    Enable: Select this check box to enable tracing support; this enables the other options on the Trace tab. By default, the check box is not selected.

    Trace IUknown Methods: Select this check box to enable tracing support for IUknown methods. By default, the check box is not selected.

    Append to Existing Output File: Select this check box to append tracing results to an output file. By default, the check box is selected. However, the setting takes effect only if the Enable check box is selected. When this check box is not selected, the new log file overrides the existing log file.

    Output File: Type the name of the file to which tracing results will be appended. This file contains the tracing results for all data sources for the data provider.

  7. Click Test Connect to verify that the data source can connect to the data store through this data provider, using the connection properties specified in the Setup dialog box. The Sybase ADO Provider Connection dialog box is displayed.

    1. Type a user name, and password, if required.

    2. Click Connect. The Sybase data provider attempts to make the connection.

      • If the Sybase data provider can connect, it promptly releases the connection and displays a Successful Completion message.

      • If the Sybase data provider cannot connect, a message describing the problem is displayed. For example, the password might be invalid, or the required client software is not installed.

  8. Click OK or Cancel. If you click OK, the values you have specified become the defaults when you connect to the data source.

Sybase ADO Provider Connection Dialog Box

The Sybase ADO Connection dialog box is displayed when you click the Test Connect button. Applications can be configured to display this dialog box if you need to enter a user name and password.

To configure advanced, optional properties, click Options. The dialog box expands to include additional configuration properties, as shown in the following figure.

Sybase ADO Provider Connection dialog box. The data source name is supplied automatically and cannot be edited.

NOTE: The data source name is supplied automatically and cannot be changed.

Follow these steps to complete the Sybase ADO Provider Connection dialog box:

  1. In the Server field, type the name of the server. If not supplied, the server name in the DSQUERY environment variable is used.

  2. If required, type the user name and password. Use the names specified when you defined the data source on the Connect ADO data source setup dialog box.

  3. From the Network Protocol drop-down list, select the name of the Sybase netlib to use for application communication. The options are Winsock and Named Pipes. The default is Winsock.

  4. Type the address of the Sybase server. If the Sybase server uses TCP/IP, the default value for the Server Port is 5000. If you selected Named Pipes in the Network Protocol field, type the string address of the server after the server name, for example, \\machine_name\pipe\sybase\query.

  5. To configure advanced, optional properties, click Options if it is enabled. Otherwise, continue at Step 9.

  6. Type the name of the database to which you want to connect. If you do not specify a value, the provider connects to the default database as defined by the System Administrator.

  7. In the HA Server field, type the name of the server that provides the Sybase connection failover capability.

  8. In the HA Server Port or Name Pipes Address field, type the address of the failover server.

  9. Click Connect to test the connection.

NOTE: See "Connecting to a Data Source Using a Connection String" for information on connecting to a data source using a provider string.

Connecting to a Data Source Using a Connection String

Once a data source is set up through the Configuration Manager, your application can connect directly to that data source by specifying the name of the data source for the DBPROP_INIT_DATASOURCE property.

However, if you do not have a DataDirect data source, you may still be able to connect using a connection string. Perform the following actions:

  1. Specify any connection properties using the appropriate OLE DB initialization property. For example, specify the "User ID" using DBPROP_INIT_USERID.

  2. Specify any backend connection properties that do not have obvious OLE DB analogs in the "provider string" property. The provider string, which is similar to the ODBC connection string, is represented by the DBPROP_INIT_PROVIDERSTRING property, described in the following paragraphs.

A provider string contains attribute=value pairs that control various aspects of the data provider's connection and interaction with the database. When an application names a specific data source to connect to, the application can also pass the data provider a provider string of attribute=value pairs. The data provider uses the values in the provider string instead of any default values that already exist for the data source in the system information.

This feature allows application developers to configure connections for users programmatically and ensures that users have the optimum settings for working with the provider and database. Any values a user has set for a data source through the Configuration Manager override corresponding values in the provider string. The provider string values are not written to the system information. The provider string values apply only to the current session.

The provider string sets the DBPROP_INIT_PROVIDERSTRING initialization property and has the form:

"attribute=value;attribute=value;"

Table 5-1 gives the name and a description for each provider string attribute specific to Sybase data provider.

The defaults listed in the table are initial defaults that apply when no value is specified in either the provider string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source in the Setup dialog box, that value is your default.

Table 5-1. Sybase Provider String Attributes 

Attribute

Description and Default Value

Application Name

Specifies the name used by Sybase to identify your application.

Character Set

Specifies the name of a character set corresponding to a subdirectory in $SYBASE/charsets.

Connection Timeout

Specifies an integer value that specifies the time, in seconds, to wait for initialization to complete.

The initial default is 15 seconds.

Default Length For Long Data

Specifies an integer value, in 1024-byte multiples, for the maximum length of data fetched from a TEXT or IMAGE column.

The initial 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

EnableQuotedIdentifiers={0 | 1}. Specifies whether quoted identifiers are supported. Specify 1 to allow support of quoted identifiers.

The initial default is 0; quoted identifiers are not supported.

ExtendedErrorInfo

ExtendedErrorInfo={FALSE | TRUE}. Specifies the amount of error information to be returned.

When set to TRUE, all extended Sybase Server error information is returned.

When set to FALSE (the initial default), only the Native error code and error message text is returned.

HA ServerName

Specifies the name of the server that provides the Sybase connection failover capability. For information on the connection failover feature, refer to your Sybase documentation.

HA ServerPort Address

Specifies the address of the Sybase server.

If the Sybase server uses TCP/IP, the initial default value for the Server Port is 5000. If you selected Named Pipes in the Network Protocol field, type the string address of the server after the server name, for example, \\machine_name\pipe\sybase\query.

Initial Catalog

Specifies the name of the database to which you want to connect.

InitializationString

InitializationString={<Sybase set commands>;...}. Supports the execution of Sybase commands at connect time. Multiple commands must be separated by commas.

Interfaces File

Specifies the path name of the Interfaces file. If you do not specify a value for this attribute, but specify a value for the Interfaces File Server Name attribute, the provider looks for the path name of the Interfaces file in the Registry under HKEY_LOCAL_MACHINE\SOFTWARE\DataDirect\InterfacesFile. If this Registry value is empty, then the provider attempts to open the SQL.INI file found in the same directory as the provider and use it as the Interfaces file.

Interfaces File Server Name

Specifies the name of the section in the Interfaces file that contains the network connection information for the Sybase server you want to access. The section name typically is the host name of the server that contains the Sybase Server you want to access.

Language

Specifies the national language corresponding to a subdirectory in $SYBASE/locales.

LogonID

Specifies 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.

Network Protocol

Specifies the name of the Sybase netlib to use for application communication. The options are Winsock and Named Pipes.

The initial default is Winsock.

Optimize Prepare

Optimize Prepare={Full | Partial | None | Full at Prepare}. Determines whether stored procedures are created on the server for every call to ICommand::Prepare.

When set to Full, stored procedures are created for every call to ICommand::Prepare. This setting can result in bad performance when processing static statements.

When set to Partial (the initial default), the Sybase data provider creates stored procedures only if the statement contains parameters. Otherwise, the statement is cached and run directly at ICommand::Execute time.

When set to None, the Sybase data provider never creates stored procedures.

When set to Full at Prepare, the data provider never creates stored procedures. Any syntax or similar errors for Select statements will be returned at ICommand::Prepare time, instead of at ICommand::Execute time.

Packet Size

Packet Size={0 | x}. 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 Sybase data provider uses the default packet size as specified in the Sybase server configuration.

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).

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

PrintStatement Behavior

PrintStatementBehavior={MS Compatible | Warnings Only}. Specifies whether print statements are sent back to the user as separate result sets.

When set to MS Compatible (the initial default), each print statement is treated as a separate result set.

When set to Warnings Only, all print statement errors are sent back to the user as a warning and are not treated as a separate result set.

Raiseerror Position Behavior

Raiseerror Position Behavior={MS Compatible | Warnings Only}. Specifies when the error is returned and where the cursor is positioned when Raise Error is encountered.

When set to MS Compatible (the initial default), Raise Error 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 Raise Errors being returned on a single execute.

When set to Warnings Only, Raise Error is handled separately from surrounding statements. The error is returned when Raise Error is processed using ICommand::Execute or IMultipleResults. The result set is empty.

Row Cache Size

Specifies the number of rows the Sybase data provider 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.

Select Method

Select Method={Direct | Cursor}. Determines whether database cursors are used for Select statements.

When set to Direct (the initial default), Select statements are run directly without using database cursors, and the data source is limited to one active statement.

When set to Cursor, 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.

Server Name

The name of the server containing the Sybase tables you want to access.

Server Port Address

Specifies the address of the Sybase server.

If the Sybase server uses TCP/IP, the initial default value for the Server Port is 5000. If you selected Named Pipes in the Network Protocol field, type the string address of the server after the server name, for example, \\machine_name\pipe\sybase\query.

StoredProcRow Count

StoredProcRowCount={Last Statement Only | Each Statement}. Specifies the row count for SQL statements in the stored procedure.

When set to Last Statement Only (the initial default), the row count from the last SQL statement in the stored procedure is returned.

When set to Each Statement, if the IMultipleResults interface is requested, the count of rows affected for each SQL statement in the stored procedure is returned. When used with the IRowset interface, this returns only the row count, and does not return resultsets.

Tightly Coupled Distributed Transactions

TightlyCoupledDistributedTransactions={0 | 1}. Determines whether the data provider uses tightly coupled distributed transactions when connected to a Sybase ASE version 12 database with specific Sybase patches (contact Sybase for specific patch levels that are required to support tightly coupled transactions).

When set to 0 (the initial default), the overall performance of the data provider is better, but multiple connections within the same distributed transaction may hang each other because the connections do not obey each other's locks.

When set to 1, the data provider uses this type of transaction and multiple connections within the same distributed transaction do not obey each other's locks.

This attribute is valid only when the data provider is enlisted in a distributed transaction or when it is connected to a Sybase ASE version 12 database. Otherwise, this attribute is ignored.

Workstation ID

Specifies the workstation ID used by the client.

Data Types

Table 5-2 defines how the underlying data provider's data types map to the standard OLE DB data types.

NOTE: Always use four-digit years for conversions from variant types to date/time types. Use of two-digit years is not supported and will result in undefined behavior.

Table 5-2. Mapping of Sybase Data Types 

binary

DBTYPE_BYTES

bit

DBTYPE_BOOL

char

DBTYPE_STR

datetime

DBTYPE_DBTIMESTAMP

decimal

DBTYPE_NUMERIC

float

DBTYPE_R8

image

DBTYPE_BYTES

int

DBTYPE_I4

money

DBTYPE_CY

numeric

DBTYPE_NUMERIC

real

DBTYPE_R4

smalldatetime

DBTYPE_DBTIMESTAMP

smallint

DBTYPE_I2

smallmoney

DBTYPE_CY

sysname

DBTYPE_STR

text

DBTYPE_STR

tinyint

DBTYPE_UI1

varbinary

DBTYPE_BYTES

varchar

DBTYPE_STR

Isolation and Lock Levels Supported

Sybase supports page-level locking, and isolation levels 0, 1 (read committed, the default), and 3 (serializable).

Using Sessions and Transactions

The Sybase data provider supports multiple sessions and multiple commands per session to the underlying database system.

The Sybase data provider supports homogeneous transactions.

The primary purpose of a session is to define a transaction. The data source object for the Sybase data provider supports multiple sessions, and therefore, can have multiple transactions. When a consumer creates a session, the session is in autocommit mode, and is not part of a transaction. All of the work in the session is automatically committed. When the session enters a transaction, all the work done under the session is part of the transaction.

Autocommit mode typically reduces performance, because of the extreme amount of disk input/output needed to commit every operation.

Threading Model Support

The Sybase data provider is internally thread-safe, and is registered with "Threading Model=Both". This provides the best combination of speed and safety. The threading model for all non-rowset objects is the same as that of the data source object.

Schema Rowsets Supported

ADO/OLE DB data providers use rowsets to expose data in tabular form. The Sybase data provider supports the IOpenRowset interface, which retrieves all data from a table for a consumer. In addition, the provider supports the ICommand interface, which allows a consumer to get a rowset that meets a specific criteria.

The Sybase data provider supports the following schema rowsets:

  • CATALOGS
  • COLUMN_PRIVILEGES
  • COLUMNS
  • FOREIGN_KEYS
  • INDEXES
  • PRIMARY_KEYS
  • PROCEDURES
  • PROCEDURE_COLUMNS
  • PROCEDURE_PARAMETERS
  • PROVIDER_TYPES
  • REFERENTIAL_CONSTRAINTS
  • SCHEMATA
  • STATISTICS
  • TABLE_CONSTRAINTS
  • TABLE_PRIVILEGES
  • TABLES
  • VIEWS

For more information about rowsets, refer to the Microsoft OLE DB programming documentation.

MTS Support

When you install the Sybase data provider from the Server Pack, you can take advantage of Microsoft Transaction Server (MTS) capabilities, specifically, the Distributed Transaction Coordinator (DTC). The server must be a Sybase 12.x Server. For a general discussion of MTS and DTC, refer to the help file of the "Microsoft Transaction Server SDK."

To enable Distributed Transactions on the Sybase server:

  1. Assign the dtm_tm_role to each user who will participate in distributed transactions (who will log in to Adaptive Server). You can do this using the sp_role command. For example:

    sp_role "grant", dtm_tm_role, user_name
    
    

    In the open string for resource managers, the specified username must have the dtm_tm_role.

  2. Specify a default database other than the master for each user. Sybase cannot start distributed transactions in a master database.

Custom Error Object Support

Basic error information is returned as part of each error record. In addition, the Sybase data provider defines a custom error object that maps the server-generated errors to the equivalent SQLSTATE value.



Title | Contents | Previous | Next | Index

Connect ADO User's Guide and Reference

2002, DataDirect Technologies. All rights reserved.