SybasePhone Number

Basic Connectivity for Sybase IQ White Paper-Technical: Data Warehousing, Archived Products, Financial Services Solutions - Sybase Inc

Abstract: This paper describes the Sybase OLE DB and .NET providers for Sybase IQ, ASE and SQL Anywhere databases. It includes information about using the OLE DB providers with client applications, Microsoft Linked Servers, and the ADO programming interface, as well as information about the Sybase ADO.NET data providers, which are preferable for the .NET Framework. Sybase publishes three more connectivity white papers for ODBC, JDBC and Sybase Open Client connectivity, which are also available on the Sybase website. These white papers are intended for use as supplements to Sybase partner certification reports, which document certification testing.

Audience: This paper is for users and system administrators responsible for configuring OLE DB and .NET connectivity to Sybase databases. While connectivity with ADO and ADO.NET is briefly discussed, this paper is not intended as programming guide supplement.

Author: Jeannette Smith, Sybase Application Engineering Team

Revisions:

RevisionAuthorDate
Version 1.1 - Expanded title to include ADO and .NET Providers; Also contains new input from SA Engineering. J. Smith October 2010
First version. J. Smith August 2010

Contents


Overview

The purpose of this paper is to provide basic information about the Sybase data providers for the Microsoft OLE DB, ADO and .NET application programming interfaces (APIs). It briefly discusses these APIs and then describes the Sybase providers. Topics covered include how to make provider connections to Sybase databases, how to verify connectivity, how the providers are packaged, and how to download and install them. Also included is information about setting up a Microsoft Linked Server using the Sybase OLE DB providers and the Microsoft OLE DB Provider for ODBC. The information contained herein is based on the following versions of Sybase products: Sybase Adaptive Server Enterprise (ASE) 15.x, Sybase IQ 15.2, and Sybase SQL Anywhere (SA) 11.x.

The OLE DB, ADO and .NET data access technologies are part of what Microsoft refers to as its "Microsoft/Windows Data Access Components (MDAC/WDAC)." The MDAC components enable applications to access data in various data sources. These components can be divided into three layers:

  • Progamming interface layer, which includes ADO and ADO.NET
  • Database access layer, which includes OLE DB providers, ODBC drivers and .NET managed providers
  • Database layer, which includes data stores such as Sybase ASE, SA and IQ databases.

MDAC Layers

Understanding where the components fit in these layers will help you to understand how the Sybase data providers are used. For more information about the MDAC components, see the Microsoft "Data Access Technologies Road Map," in the MSDN Library or the MDAC Wiki page, http://en.wikipedia.org/wiki/Microsoft_Data_Access_Components, which provides a diagram of how the components access data. Desscriptions of the OLE DB, ADO and ADO.NET APIs and providers follow in that order.


The Microsoft OLE DB API and the Sybase providers

Microsoft developed the OLE DB API to be a successor to the ODBC API. ODBC was designed to work with database management systems. OLE DB expanded the support to a wider variety of data stores, including files, spreadsheets, and non-relational databases. ODBC uses driver "calls" to translate between an application and a database. OLE DB uses "providers" make data available to "consumers," which are applications requesting access to data. The OLE DB API consists of a set of "interfaces" similar to the way the ODBC API consists of a set of driver calls. Thus, the "data providers" for OLE DB serve a similar purpose as the "drivers" for ODBC. Sybase supports the OLE DB API in the form of "data providers" that work with the Sybase ASE, SA and IQ databases. Sybase only makes OLE DB providers for Microsoft Windows platforms. They are implemented as dynamic-linked libraries (DLLs), like the Windows-platform ODBC drivers.

Sybase OLE DB data providers

Sybase makes the following data providers for OLE DB access to Sybase databases. Client applications can use these OLE DB providers to access data directly. The OLE DB provider must be installed on the same machine with client application you want to to use to access the Sybase server. Information about OLE DB provider packaging and installation is provided later in this paper, in the section Sybase provider software packaging, downloads and installation.

  • Sybase ASE OLE DB Provider (ASEOLEDB, aseoledb.dll) is designed by Sybase specifically for ASE. It is discussed at length in the ASE 15.0 OLE DB Provider Users Guide. This guide includes a list of the interfaces supported by this provider in the section "Supported OLE DB interfaces."
  • Sybase SQL Anywhere OLE DB Provider (SAOLEDB, dboledb.dll) is designed by Sybase for SQL Anywhere but it may also be used with Sybase IQ. The SQL Anywhere provider is discussed in the Sybase SQL Anywhere Server 11 - Programming guide. This guide also includes a list of supported interfaces in the section "Supported OLE DB interfaces." The use of the SQL Anywhere OLE DB Provider with Sybase IQ, including some limitations, is documented in the Sybase IQ 15.2 System Administration Guide: Volume 1, in the section "Connecting to a database using OLE DB."

Related Microsoft OLE DB data access methods for Sybase

Two other related Microsoft data access methods that use OLE DB connectivity to Sybase databases are the Microsoft Linked Servers and the Microsoft OLE DB Provider for ODBC. A brief discussion of each follows:

  • Microsoft Linked Servers and Sybase OLE DB providers - a Microsoft "Linked Server" is a server definition within one database server, such as Microsoft SQL Server, that allows access to data in a different server. This is similar to the Sybase concept of a "remote server." Microsoft supports OLE DB connectivity for Linked Servers. Thus, you can use the Sybase OLE DB providers to create Linked Servers that access Sybase ASE, SA or IQ databases. For information about setting up a Linked Server, refer to the documentation for the application in which you want to do so. Use of the SQL Anywhere OLE DB Provider to create a Linked Server is described in the section "Setting up a Microsoft Linked Server using OLE DB" in the "SQL Anywhere OLE DB and ADO Development" section of the SQL Anywhere - Programming guide. Please note that the information in this guide is for versions of Windows earlier than Windows 7 or Vista. It contains an example in which the "SQL Anywhere 11 Demo" data source is used to start a server and connect using shared memory. With Windows 7 and Vista, Windows services run in a different space than the desktop. Thus, SQL Server cannot used shared memory for connections and this example is not appropriate.
  • Microsoft OLE DB Provider for ODBC - The Microsoft OLE DB Provider for ODBC (MSDASQL) works as a bridge with the database vendor ODBC drivers to provide OLE DB connectivity to a database. Thus, you could use it with the Sybase IQ, SA and SQL Anywhere ODBC drivers. This method is not, however, recommended by Sybase. Instead, Sybase recommends using the Sybase OLE DB providers. The Sybase providers have the following advantages over MSDASQL: they do not require ODBC components and drivers; they are designed specifically for the target Sybase databases and their functionality, and they are likely to be faster because they are not a bridge.

ADO API and the Sybase OLE DB data providers

Microsoft ActiveX Data Objects (ADO) is an object-oriented programming interface that was intended for use with scripting languages, such as Visual Basic. ADO can use OLE DB or ODBC connectivity from an ADO programming environment. For OLE DB connectivity to data sources, you can use the Sybase ASE and SA OLE DB providers described in the previous section. Of note are two ADO connection object properties you can use to initiate a connection. These properties are as follows:

  • The Provider property holds the name of the data provider. This is where you specify the Sybase OLE DB provider (ASEOLEDB or SAOLEDB). The property defaults to the Microsoft OLE DB Provider for ODBC (MSDASQL), which is not recommended as described in the previous section.
  • The ConnectionString property holds connection information. For this property, you can supply data source names, or explicit UserID, Password, DatabaseName and other parameters in a connection string. More information about specifying connection information follows in the section Sybase connection strings and data sources later in this report.

To see sample Visual Basic code using the SA OLE DB Provider in a connection object, see the section "Connecting from ADO" in the SQL Anywhere Server - Database Administration guide. For detailed information, see the section "ADO programming with SQL Anywhere" in the SQL Anywhere Server - Programming guide.


ADO.NET and the Sybase .NET providers

ADO.NET is also a programming interface. It is part of the Microsoft .NET Framework and a successor to ADO. In the ADO.NET architecture, data manipulation is separated from data access, which is handled by providers, called ".NET managed providers."

Microsoft supports OLE DB and ODBC connectivity in .NET. For OLE DB, Microsoft provides the .NET Framework Provider for OLE DB, which is implemented in the System.Data.OleDb namespace. ("Namespace" is the Microsoft term for a logical grouping of code in .NET). This provider works with a database vendor OLE DB provider. Thus, you could use it with the Sybase OLE DB providers listed above. But this is not the recommended method. Instead, Sybase recommends using the following ADO.NET providers it makes specifically for its databases:

  • For ASE: ASE ADO.NET Data Provider - this provider has its own API, which is an implementation of the properties and methods from the ADO.NET interfaces. It allows you to access ASE from any language supported by .NET, including C# and Visual Basic. This provider is a common language runtime (CLR) assembly, which is a class library that contains all the required sets of classes to provide functionality for the ADO.NET interfaces. It does not use the Sybase OLE DB provider or Microsoft .NET Framework Provider for OLE DB, making it faster. For more information about the Sybase ASE .NET provider, see the ASE ADO.NET Data Provider User's Guide.
  • For SA and IQ: SQL Anywhere .NET Provider - this provider also interacts directly with a SQL Anywhere or IQ database and, like the ASE .NET Provider, it does not use OLE DB or ODBC, making it faster. The SQL Anywhere .NET Data Provider is also a CLR assembly and is represented in the .NET namespace as iAnywhere.Data.SQL.Anywhere. For more information about the SQL Anywhere .NET provider, see the "SQL Anywhere .NET Data Provider" section of the SQL Anywhere Server - Programming guide, in the "Data Access APIs" section. For more information about using SQL Anywhere with .NET, see the Sybase SQL Anywhere .NET 2.0 API Reference.

Sybase connection strings and data sources

In order for an application to connect to a Sybase database, you must configure it to use an interfacing library (driver or provider) with the connection information for the database to which you want to connect. In the case of OLE DB, the interface library is a Sybase OLE DB provider. Most applications specify the database connection information using one of two formats:

  • Data sources - a named connection definition for a target data store
  • Connection strings - a list of keyword parameter and value pairs with connection information for a target data store

Information about how Sybase supports both types follows.

Connection strings

Sybase supports connection strings with defined parameters for its databases. For OLE DB connectivity, the connection string must also include the OLE DB provider name. For some applications, you enter the connection information and then the application builds the connection string and passes it to the interface behind the scenes. For others, you enter the string directly.

Following is an example of a Sybase OLE DB provider connection string:

Provider=SAOLEDB;Data Source=TestMachine;Initial Catalog=Demo;User ID=UserID; Password=Password

where:

Provider= the parameter for the OLE DB provider name. SAOLEDB is the SQL Anywhere OLE DB provider name, which you can specify for Sybase SA or IQ access. This would be ASEOLEDB for the ASE provider, or MSDASQL for the Microsoft OLE DB Provider for ODBC.

Data Source= the name of the host machine where the target database is running. TestMachine is simply a sample host machine name shown for this example. For the SAOLEDB provider, this maps to the SQL Anywhere or IQ DataSourceName or DSN connection parameter. You may need to provide both the host and port separated with a colon (server:port) for this this value. This could also be a data source (DSN) defined with the ASE or ODBC Administrator as described in the following section.

Initial Catalog = is the name of the specific database to which you want to connect. Demo is the name of the SQL Anywhere demo database. For the SAOLEDB provider, this maps to the SQL Anywhere or IQ DatabaseName or DBN connection parameter. For the ASE pubs2 sample database, this would be pubs2, and so on. Depending on the application, you may not need to supply this parameter for SA and IQ connections to servers that run one database with one server.

User ID= and Password= are a valid user ID and password for the target database. These also map to the SA or IQ connection parameters of the same names.

There are other valid connection parameters you can pass in this string as follows:

SAOLEDB - Additional parameters supported by the SQL Anywhere OLE DB Provider are as follows:

Location={hostname | ipaddress}[:portnumber]... - the host machine or ip address and port of the machine where the target Sybase SA or IQ server is running. For version 12 of the provider, this maps to the SQL Anywhere connection parameter Host={hostname | ip-address}[:port-number]. For version 11, this maps to ENG=no;LINKS=TCPIP(dobroad=none;verify=no;host={hostname | ip-address}[:port-number]...

Extended Properties - used to pass in all the SA- and IQ-specific connection parameters. For example, Extended Properties="DBKEY=XXXX3952B;DBF=c:\samples\demo.db". Note that some Microsoft dialog boxes, label this entry field as "Prov String."

OLE DB Services - not directly handled by our provider, it controls connection pooling in ADO. This topic is covered in version 12 of the SQL Anywhere Server - Programming product manual under "OLE DB Connection Pooling."

Window Handle - applications can pass the handle of the parent window, if applicable, or a null pointer if either the window handle is not applicable or the provider will not present any dialog boxes.

Prompt - Prompt=1|2|3|4, where DBPROMPT_PROMPT (1), DBPROMPT_COMPLETE (2), DBPROMPT_COMPLETEREQUIRED(3), DBPROMPT_NOPROMPT (4=default).

Other Microsoft OLE DB connection parameters may be specified but will be ignored. For information about SA and IQ connection parameters, see the section "Connection parameters" in the "Starting and Connecting to Your Database" section of the SQL Anywhere Server - Database Administration guide.

ASEOLEDB - All connection parameters supported by the Sybase ASE OLE DB provider are documented in the ASE 15.0 OLE DB Provider Users Guide, in the section "Using connection parameters" in Chapter 2, "Connecting to a Database."

Data sources

A data source, also called a "data source name" or "DSN," can be specified in a Data Source or DSN field in an application, or for the DataSource connection string parameter as described in the previous section. Sybase supports the use of data sources for ASE, SA and IQ. However, the tools that you use to create them differ for the different databases as follows:

  • For SA & IQ: ODBC Data Source Administrator - a standard Microsoft ODBC component installed by Sybase or Microsoft for creating ODBC DSNs. While the ODBC Administrator is designed to create ODBC data sources, applications can use those DSNs for the connection information to access Sybase SA or IQ with the SQL Anywhere OLE DB Provider. The ODBC Administrator is available on the Start menu in either the ODBC, SQL Anywhere or Sybase IQ | Data Access program groups. The ODBC Administrator stores the complete DSN information in the Windows operating system registry, and the DSN names and driver file information in the odbc.ini and odbcinst.ini files, which are in C:\WINDOWS, by default. Instructions for using the ODBC Administrator to create DSNs are provided in the Sybase White Paper: Basic ODBC connectivity for Sybase Databases.
  • For ASE: Sybase ASE Data Source Administrator - a Sybase utility designed specifically for creating OLE DB data sources with the Sybase ASE OLE DB Provider. This tool is available on the Start menu, in the Sybase | Data Access program group when you install the Sybase ASE PC-Client, ASE Server, or Sybase Software Development Kit (SDK). By default, it stores the data source definitions under C:\sybase\DataAccess\OLEDB\DataSources. More information about packaging and installation is provided later in this report, in the section Sybase provider software packaging, downloads and installation. Instructions for using the ASE Data Source Administrator follow.

Sybase ASE Data Source Administrator

This section describes how to create a basic OLE DB data source using the Sybase ASE Data Source Adminstrator. This utility is only for Sybase ASE. As described above, you use the ODBC Data Source Administrator to create DSNs for Sybase IQ and SQL Anywhere. Instructions for doing so are documented in the Sybase White Paper: Basic ODBC connectivity for Sybase Databases.

Important! Before you can successfully connect to a Sybase ASE server with an OLE DB Provider, OLE DB meta data stored procedures must exist in the target ASE server. They are installed by default with ASE version 15.5 but not with previous versions in which case you must create them. See the "Installing the software" in the Sybase provider software packaging, downloads and installation section near the end of this paper for instructions for creating the stored procedures. Note that there are similar procedures for the ODBC driver. See the aforementioned white paper for ODBC connectivity for information about creating those procedures.

  1. Install the pre-requisite software and the Sybase ASE PC-Client, ASE Server or SDK appropriate for your operating system. Information about installing the software is included in "Installing the software" in the Sybase provider software packaging, downloads and installation section near the end of this paper.
  2. Launch the ASE Data Source Administrator by choosing Start | Programs | Sybase | Data Access | ASE Data Source Administrator. The application opens. The exact program group options will vary slightly with the product you install.

ASE DS Admin 1

  1. Click Add. The New Data Source dialog opens with the General tab in focus. Note that the other tabs on the New Data Source dialog box are not covered in this paper.

New Data Src - General tab

  1. Fill in the name and information for the data source you want to create as follows:

Data Source Name - the arbitrary name you want for the data source.

Description - an optional description of the data source.

Server Name - the name of the machine hosting the target ASE server.

Server Port - the port on which the target ASE listens.

Database Name - the name of the database in the ASE server to which you want the data source to connect.

Login ID - the name of the user you want to use to connect to the data source.

When you finish entering information, click Test Connection. A Login dialog box opens with the user you entered in the Login ID field.

  1. Enter the password for the specified user, if there is one, and click OK. If the information you entered is valid and the target ASE server you specified is running, a "Connection Successful" message appears. If not, be sure the ASE server is running, verify the connection information you specified, and try the connection test again. When the connection test passes, the ASE OLE DB data source is ready to use.
  2. Click OK twice. Once to dismiss the Connection Successful dialog and again to save the data source. The data source you created will be listed in the Data Sources window of the Sybase ASE Data Source Administrator application.
  3. Click Close to exit the application. Note that the test described in this section is simply a connection test. The following section provides information about a utility you can use to do more testing with the OLE DB providers.

OLE DB provider connectivity test (Microsoft Rowset Viewer)

If you want to verify a Sybase OLE DB provider is connecting to the target database and returning data properly, you can use the Microsoft OLE DB Rowset Viewer application, which is included with the Microsoft Data Access (MDAC) SDK version 2.8. As mentioned before, OLE DB API consists of a set of "interfaces." The Rowset Viewer tests the OLE DB interfaces. For more information about using the Rowset Viewer, refer to the OLE DB Help item on the Start Menu MDAC SDK program group.

Note that if you install the MDAC SDK on a 64-bit Microsoft Windows operating system, you will have three versions of the Rowset Viewer: one for amd64, one for ia64 and and one for x86. You want to test with the version that is appropriate for your machine arcnitecture, operating system and has the same word-size (32-bit or 64-bit) as the application you plan to use with the OLE DB provider. For example, if you are using a 32-bit application, you want to use the x86 version of the Rowset Viewer. If you are running on an Itanium machine with the Itanium version of Windows, you want to use the ia64 version, and so on.

The following steps describe how to use the Rowset Viewer for a basic OLE DB provider connectivity and data retrieval test. The sample screens show the x86 version of the Rowset Viewer with the SQL Anywhere OLE DB Provider accessing a Sybase IQ database. The steps should also apply for using other versions of the Rowset Viewer or the Sybase ASE OLE DB Provider.

  1. Choose a Default install-type of the 2.8 version of the MDAC SDK, if it is not already installed in your environment. This product is available as a free download from the Microsoft website. Note that the MDAC is sensitive to the version, word-size and architecture of the Windows operating system on which you install it. You must have the appropriate version for your Windows machine or the installation program will not run.
  2. Launch the Rowset Viewer by choosing Start | Programs | Microsoft Data Access SDK 2.8 | Rowset Viewer. The exact names of the Rowset Viewer menu options vary with the version of the Rowset Viewer installed as mentioned above. Choose the one appropriate for your OLE DB Provider and operating system. The Rowset Viewer will open. The sample screen below shows the Rowset Viewer with the windows tiled horizontally.

MDAC Rowset Viewer before connection

  1. Choose File | Data Links. The Data Links Properties dialog opens with the Provider tab in focus and the Microsoft OLE DB Provider for ODBC Drivers selected by default.
  2. Select the Sybase provider you want from the Provider drop-down list. The following screen shows the SQL Anywhere OLE DB Provider 11 available and selected. Note that the when the ASE OLE DB Provider is installed it is listed here simply as "Sybase OLE DB Provider."

Data Links Provider tab

  1. Click Next to display the Connection tab. The Connections tab opens. The options available on the Connection tab vary with the selected Provider but are the same for both the Sybase ASE 15.5 and SA 11.x providers. The following sample screen shows the Connection tab for the Sybase SQL Anywhere 11.x OLE DB Provider. It and the one for the ASE provider are designed to use a data source to make the connection.

Data Links Connection tab

  1. Specify the name of a data source you have defined with either an ODBC Administrator or the ASE Data Source Administrator in the Data Source field for 1. Enter the data source and/or location of the data. In the following example, the Sybase IQ Demo database User DSN created by the Sybase IQ client is shown. Note that you should define your DSN as a system DSN to make it available to all users. For information about creating ODBC DSNs, see the Sybase White Paper: Basic ODBC connectivity for Sybase Databases.

Data Links Connection tab

  1. For 2. Enter information to log on to the server:, select Use a specific user name and password and enter a valid user ID and password for the target database.
  2. In the 3. Enter the initial catalog field, you provide the database name. Note that a database name may fill in here when you specify the data source if the data source definition includes a database name. Or, if a database is not specified, the databases available in the server specified in the data source will be available in a drop-down list.
  3. Click the Test Connection button. If the DSN and user information is valid and the target Sybase server is running, you should see "Test connection succeeded" message. If not, verify the connection information and try the test again.
  4. Leave the default values on the Advanced and All tabs for now and click OK. The Command window opens with the OLE DB Provider name selected in Step 4, SQL Anywhere OLE DB 11.0 (ASADataSource) as shown in the following screen.
  5. Query a table you know exists in the target Sybase database by entering its name in the upper pane of the Command window. The following sample screen shows GROUPO.Customers, which is the owner-qualified name of the IQ demo database Customers table. The qualification of the owner name may be necessary if the user with which you log in is not the owner of the table.

SA Command window with Table name

  1. Execute the query by clicking the exclamation point button in the Rowset Viewer toolbar. The IOpenRowset::OpenRowset dialog box opens, showing the table name you specified. IOpenRowset is one of the OLE DB interfaces. It is supported by both the SQL Anywhere and ASE OLE DB providers. Note that if you see and error from the SAOLEDB.11 provider that says "Table 'Customers' not found" at this point, it may be necessary for you to qualify the table name.

IOpenRowset dialog

  1. Click OK. The result data from the table you specified should return to a Rowset window.

Rowset Results window

  1. Besides the results in the Rowset window, you should also look at the Output window. The Rowset Viewer reports the OLE DB interface calls executed for the table query as shown below.

Rowset Viewer output window

  1. When you are finished using Rowset Viewer, you can choose File | Exit to close the application. For more information about using Rowset Viewer to test OLE DB interfaces or information about testing provider compliance, see the OLE DB Help item on the Microsoft Data Access SDK Start menu program group. For information about Sybase SA-supported interfaces, see the SQL Anywhere - Programming guide, specifically the "Supported OLE DB interfaces" section of the "SQL Anywhere OLE DB and ADO Development" section. Or, for ASE, you can see the section "Supported OLE DB interfaces" in the ASE 15.0 OLE DB Provider Users Guide.

This completes the basic OLE DB Provider verification test.

Back to Contents


Sybase provider software packaging, downloads and installation

This section provides information about how the Sybase OLE DB data provider software is packaged, how to download it, and how to install it.

Sybase data provider packaging

This section lists the Sybase software packages that include the Sybase data providers. Remember that the providers are only available for Microsoft Windows platforms. Thus, they are only available with the Sybase software for Windows platforms. The OLE DB provider must be installed on the same machine with each client application you want to use to access the Sybase server. If the client application will not be running on the same machine as the Sybase server, you will need to install the provider from the database client software on the same machine with the client application.

The following list contains the Sybase software products that include the Sybase data providers:

For ASE:

  • Sybase ASE OLE DB Provider
    • Sybase PC-Client - the Sybase ASE database client software package, which includes the ASE OLE DB Provider and ASE ADO.NET Data Provider. It installs the Sybase Software Development Kit (SDK), which includes the providers. Note that even though this product package is only available for Windows, it is available on the download page or shipped with the ASE server installations for most platforms because so many client applications that access Sybase ASE are made for Windows. For ASE PC-Client versions 15.0.3 and 15.5, the installation program includes both the 32-bit and 64-bit installations. The installation program detects the word-size of the operating system and installs the appropriate version. Note that since most client applications do not run on the same machine with the ASE server, you are likely to install this software.
    • Sybase ASE Server for Windows - the Sybase ASE database server software package, also includes the Sybase Software Developer's Kit (SDK) for Windows with the ASE OLE DB and ADO.NET Data Providers. Note that the OLE DB and ADO.NET providers are not installed with a "Typical" type installation. You choose either a "Custom" installation and explicitly select the providers or a "Full" installation to install them with the server. If you install a Full install type, both the 32-bit and 64-bit OLE DB providers will be installed.
    • Sybase Software Developer's Kit (SDK) - the Sybase SDK standalone product software package. The 15.5 version of the Windows x64 SDK installs both the 32- and 64-bit OLE DB Providers. The 15.0 version includes only a 32-bit OLE DB Provider; but both 32-bit and 64-bit versions of the ODBC driver.

For SQL Anywhere and IQ:

  • Sybase SQL Anywhere OLE DB Provider
    • Sybase SQL Anywhere, the SA database server software, includes the OLE DB Provider and ADO .NET Provider. The word-size of the providers depend on the word-size of the SQL Anywhere server. But if you install the 64-bit version, both the 32-bit and 64-bit versions will be installed.
    • Sybase IQ Network Client - Client for Windows, the Sybase IQ database client software, includes both the SQL Anywhere OLE DB Provider and SQL Anywhere ADO.NET Provider. You can use these providers with SQL Anywhere or Sybase IQ. Note that this product includes two installation binaries: one for the 32-bit version of the software and one for the 64-bit version of the software. The word-size of the providers depends on which version you choose to install. You can install either the 32-bit or 64-bit version on 64-bit operating system but you can only install the 32-bit version on a 32-bit operating system.
    • Sybase IQ Server for Windows, the Sybase IQ database server software, provides the option to install the Database Client components, which is the same as the Sybase IQ Network Client above. Note that Sybase makes the Sybase IQ Network Client for Windows available with the Sybase IQ server packages for other platforms because so many client applications used to access Sybase IQ run on Windows.

Downloading the software

The Sybase data providers are available from the Sybase website via Sybase Product Download Center (SPDC) or EBFs/Maintenance pages. As stated previously they are only available for Microsoft Windows platforms. See the section Sybase data provider packaging to determine which product you need to download to obtain the provider you want.

To use either the SPDC or EBFs/Maintenace pages to download software, you must have a valid Sybase website (MySybase) login. The software you see listed depends on the privileges of your login. If you do not have a valid login, contact Sybase Technical Support to determine if you are authorized for one. Otherwise, you should obtain the software from your normal distribution source.

To download software from the Sybase website:

  1. Determine which product you want to install. See the section Sybase data provider packaging above to see a list of how the providers are packaged.
  2. Open a browser and navigate to the appropriate site for the product or update you want to download. Note that both of the following URLs will initially take you to a login page:
    • For full, licensed customer product releases, navigate to the Sybase Product Download Center (SPDC) at the following URL: https://sybase.subscribenet.com. Note that for the Sybase ASE PC-Client, you may need to obtain it from the ASE server download page.
    • For Emergency Bug Fixes, Maintenance releases or Sybase IQ database client software, navigate to the EBFs/Maintenance page at the following URL: http://downloads.sybase.com
  3. When the login page opens, enter your valid Sybase user and password. The page for the URL you specified opens. Although they are different, both the SPDC and EBFs/Maintenance release URLs lead to linked lists of products. In addition to the list, the EBFs/Maintenance page also contains a field in which you can enter and search for a specific EBF or Maintenance release.
  4. Select the product you want: Sybase Adaptive Server Enterprise, Sybase IQ, SQL Anywhere or the SDK. The list of releases for the product you selected opens sorted alphabetically by platform/operating system. The client software may be listed at this level or under the Sybase database server product name, or on the same page with the server download when you select it. For example, if you choose Adaptive Server Enterprise, the PC-Client software will be available on the same page with the server. If you choose Adaptive Server Enterprise on the EBFs/Maintenance page, the PC Client releases and updates will be listed with the database server updates.
  5. Scroll through the list of products to the releases for the various Windows platforms.
  6. Locate and click the link for the product or release you want to download. A license agreement page will open.
  7. Read the license agreements. If you choose not to agree to the license agreements, you will not be able to download the software. If you do agree, check the agreement boxes and then click Continue. Links for product download and readme file are available on the next panel.
  8. To read the readme file, click that link and the readme will open in the browser. Press the browser Back button to return to the download panel.
  9. To open or download the product, click the product links. You will have the option to save the download file or open it. Follow the instructions on the download page for how to extract the files.

Installing the software

The following list provides information or references to the appropriate documentation for installing the Sybase software that contains the data providers. Always check the latest ASE Installation Guide and Release Bulletin for your platform for any new information or required updates before installing any software.

For ASE:

  • Sybase ASE PC-Client software - the installation of this software is described in the ASE Installation Guides for all platforms. Because the ASE Server installation packages for all platforms include the PC Client software, all the installation guides contain a section called "Installing Sybase PC-Client Products." The 15.0.3 and 15.5 PC-Client installations contain both the 32-bit and 64-bit SDK. The installation program detects the word-size of the operating system and installs the approrpriate version. Following are pre-requisites for installing and using the Sybase ASE OLE DB Provider, versions 15.02 and 15.5:
    • Before you run the ASE PC-Client software installation program, you must install Microsoft .NET Framework 2.0 Service Pack 1.
    • After you install the ASE PC-Client, verify the OLE DB meta data stored procedures, which are required for OLE DB connectivity, exist in the target ASE server. For ASE 15.5, the stored procedures are automatically created. For prior versions, you will need to create them. See the following section Creating OLE DB metadata stored procedures in ASE for instructions to create the procedures.
  • Sybase ASE server - the installation of this software is described in the ASE Installation Guides for all platforms. In addition to the requirements listed for the PC-Client software above, you must also install the ASE server pre-requisite: Microsoft Visual Studio C++ 2005 Redistributable package.
  • Sybase Software Developer's Kit (SDK) - the installation of this software is described in the Software Developer's Kit and Open Server Installation Guide for Windows for version 15.5 and Installation Guide SDK and Open Server 15.0 for Windows for version 15.0. If you are developing applications, you are likely to install this product. If you are configuring client application connectivity, you are more likely to install the PC-Client software.

For SQL Anywhere and IQ:

  • For SQL Anywhere - SQL Anywhere installation program is self-explanatory. Thus, Sybase does not publish installation guides for it.
  • Sybase IQ Network Client - Client for Windows - the installation of this software is described in the "Installing client components on Windows" section, which is in the "Installing Sybase IQ Client Components" section of the Sybase IQ server Installation and Configuration Guides for all platforms. The 15.2 version includes installation programs for both 32-bit and 64-bit versions.
  • For Sybase IQ - the installation of this software is described in the Sybase IQ Installation and Configuration Guide for Windows.

Creating OLE DB metadata stored procedures in ASE

To access ASE versions prior to 15.5 with the Sybase ASE OLE DB Provider, you must perform the one-time task of creating the OLE DB metadata stored procedures in any ASE servers you want to access. This applies to ASE servers on all platforms, not just Windows. The Sybase ASE Server, SDK and Sybase PC-Client Products for Windows include the OLE DB priver and also provide a script called install_oledb_sprocs.bat. Run this script against any ASE servers you plan to access with an ASE OLE DB Provider. On Windows, the Sybase PC-Client Products installation program installs the script in the root Sybase installation, C:\Sybase\DataAccess\ODBC\sp by default. The script creates the procedures in the sybsystemprocs database. The procdure names begin with "sp_oledb_...."

The script you use to create the procedures uses the Sybase (native) Open Client server definition name for the ASE server. Thus, you must create a server definition for the target ASE in the sql.ini or interfaces file on the machine where you plan to run the script. When you install the Sybase PC-Client, SDK or Sybase IQ server, you also install Sybase Open Client. See the Sybase White Paper: Basic Sybase Open Client Connectivity for Sybase Databases for instructions to use the Sybase utility dsedit to create this server definition.

Creating the procedures consists of the following two tasks:

  1. Create the Sybase Open Client server definition name for ASE in the sql.ini file.
  2. Run the script by changing to the DataAccess\OLEDB\sp directory and calling the script with the ASE ServerName, a valid user and password. The following example shows this script being run with the ASE default administrative user "sa" without a password. The ServerName parameter is the name of the ASE data server as defined in the sql.ini file on Windows.
    install_oledb_sprocs ServerName sa  

The script determines the version of ASE against which it is running and creates the stored procedures appropriate for the ASE version. The script calls one of several SQL scripts provided for different ASE versions. For example, for 15.x versions of ASE, the oledb_mda_150.sql script will be run.

After you create the procedures, you will be able to connect to ASE with the OLE DB provider.

Back to Contents


References

This section is both a bibliography for this white paper and a reference for more information.

  • Micrsoft Developer's Network (MSDN) Library provides a significant amount of relevant information regarding the OLE DB, ADO, ADO.NET and MDAC topics, especially the "Data Access Technologies Road Map," (http://msdn.microsoft.com/en-us/library/ms810810.aspx).
  • Sybase SQL Anywhere Server 11 - Programming guide provides information about the SQL Anywhere data providers in the "Data Access APIs" section. It includes a list of SQL Anywhere OLE DB Provider-supported interfaces in the section "Supported OLE DB interfaces," which is in the "SQL Anywhere OLE DB and ADO Development" section. With a few exceptions, this information also applies to using the SQL Anywhere data providers with Sybase IQ.
  • SQL Anywhere Server - Database Administration guide describes the ADO connection object properties you can use to initiate a connection in the section "Connecting from ADO." The two properties are mentioned in this paper in the section Sybase ADO data providers.
  • Sybase IQ 15.2 System Administration Guide: Volume 1 - describes the use and limitations of the SQL Anywhere OLE DB Provider with Sybase IQ in the in the section "Connecting to a database using OLE DB" in the "Sybase IQ Connections" section.
  • ASE 15.0 OLE DB Provider Users Guide- is a guide dedicated to the ASE OLE DB Provider. It includes a list of the OLE DB interfaces supported by this provider in section "Supported OLE DB interfaces." This guide is listed with the Sybase Open Server Developer's Kit (SDK) 15.0 product documentation, not the ASE product documentation.
  • Sybase ASE 15.0 ADO.NET User's Guide - is a guide dedicated to the ASE ADO.NET Data Provider. It is also listed with the Sybase Open Server Developer Kit documentation.
  • Micrsoft Developer's Network (MSDN) Library, several entries:
    • "Data Access Technologies Road Map," http://msdn.microsoft.com/en-us/library/ms810810.aspx
    • ".NET Framework Class Library," version 2.0, http://msdn.microsoft.com/en-US/library/ms229335%28v=VS.80%29.aspx
  • Wikipedia (Wiki) pages:
    • "Microsoft Data Access Components," http://en.wikipedia.org/wiki/Microsoft_Data_Access_Components
    • "ActiveX Data Objects," ADO, http://en.wikipedia.org/wiki/ActiveX_Data_Objects
    • "ADO.NET," http://en.wikipedia.org/wiki/ADO.NET

To access the Sybase product manuals, navigate to the following:

  1. Open a Web browser and navigate to www.sybase.com.
  2. Navigate to Support & Downloads | Product Manuals.
  3. When the Product Manuals page opens, locate the product you want, select it, the language you prefer, and click Go. A list of the document sets for the product you selected will open. Choose the set you want and then the document you want from the Collection list when it opens.
© Copyright 2014, Sybase Inc. - v 7.6