SUMMARY:

This document contains information on how to prepare and define OLE DB data providers that are shipped with PowerBuilder.

Document ID:	47903	Last Revised:	04/16/99
Topic:	Connectivity	Document Type:	TechNote
Product:	PowerBuilder	Version:	7.0
Platform:	PC	Operating System:	Windows 95, Windows NT

Document:

Connecting to OLE DB Providers from PowerBuilder 7 Summary

　　This document contains information on how to prepare and define OLE DB data providers that are shipped with PowerBuilder. This document is a supplement to the connection information found in the Connecting to your Database manual. It goes into more detail regard how to connect to the OLE DB data providers.

　　What is OLE DB?

　　OLE DB is an application programming interface (API) developed by Microsoft. It is designed to provide access to a wide range of data sources. A data source consists of data that is stored in diverse formats, such as indexed-sequential files like Btrieve, personal databases like Microsoft Paradox, productivity tools such as spreadsheets and electronic mail, as well as SQL-based data.

　　The OLE DB architecture uses the concept of data Consumers, data Providers and Service Providers. A Consumer can be any software component that uses an OLE DB interface. It could be a business application, a software development tool, such as PowerBuilder, or a programming language. A Provider communicates between the consumer and the actual data source. A Service Provider (also known as a Service Component) can provide extended DBMS functionality.

　　Advantages of OLE DB vs. ODBC

　　OLE DB is designed to access non-relational data, such as mail, as well as relational SQL-based data. The ODBC standard provides a unified way to access SQL-based data only.

　　Preparing to use the OLE DB Interface

　　These steps must be taken in order to connect to an OLE DB provider:

?Install and configure the database server, network, and client software
?Install the OLE DB interface
?Install Microsoft Data Access Components
?Define the OLE DB providers
?Define the OLE DB interface

　　Install and configure the database server, network, and client software

　　You must obtain the database server software from your database vendor. For installation instructions, see your database vendor's documentation.

　　For installation and configuration instructions for your network software, see your network administrator.

　　To determine the client software requirements, see your data provider's documentation. Most providers require the client software already be installed.

　　Install the OLE DB interface

　　Only PowerBuilder Enterprise includes the OLE DB interface. To install the OLE DB interface, choose the Custom installation option and select the OLE DB interface. The PBOLExx.DLL (where xx is the PowerBuilder version) will be installed to \Program Files\Sybase\Shared\PowerBuilder (assuming all default paths were selected during the install). It will also install Intersolv Providers for Informix 7, Informix 9, Oracle 7, Oracle 8 and Sybase.

　　The initial release of the PowerBuilder OLE DB interface will not support the Intersolv Reflector and Notes providers, which are used to access non-SQL based data.

　　

Install Microsoft Data Access Components (MDAC)

　　PowerBuilder ships Microsoft Data Access Components, which is required to utilize the OLE DB functionality within PowerBuilder. To install MDAC, you must execute mdac_min.exe found in the \Support directory on the Install CD Disk 2. In addition to installing the OLE DB components, mdac_min.exe also installs the following Microsoft OLE DB data providers:

?Microsoft OLE DB Provider for ODBC (MSDASQL.DLL)
?Microsoft OLE DB Provider for SQL Server (SQLOLEDB.DLL)

　　The above files will be installed into the \Program Files\Common Files\System\OLE DB directory.

　　Note: The Microsoft OLE DB Provider for Oracle (MSDAORA.DLL) is not included in mdac_min.exe. Instead, use the Intersolv Oracle provider.

　　Define the OLE DB providers

　　Depending upon which vendor and provider you are using, you must set up the data source before you can define the OLE DB interface within the Database Profile setup.

　　Intersolv Providers

　　To define a data source via the Intersolv OLE DB Administrator, you need to execute the program pbadmin.exe from \Program Files\Sybase\Shared\IntersolvOLEDB.

　　Do the following to create the data source:

?Right-click on the Connect OLE DB Data Source folder and select New Connect OLE DB Data Source from the pop-up menu.
?Type a name for the data source at the Intersolv Connect OLE DB providers'window. All the data sources located in the same directory must have unique names.
?Select a data provider from the list of installed data providers in the Provider dropdown listbox.
?Click the Set Up Data Source button and enter additional connection information.

　　As you set up each data source, make sure you test the connection. Do this by clicking the Test Connect button on the General tab of the Provider Setup window. This will ensure the connection is properly setup outside of PowerBuilder and it will eliminate possible connection errors.

　　Microsoft Providers

　　If you are using the Microsoft OLE DB Provider for ODBC, you have to create an ODBC data source via the ODBC Data Source Administrator just as you would if you were using a direct ODBC connection. This utility can be accessed via the Control Panel in Windows or via the Database painter.
If you are using the Microsoft OLE DB Provider for SQL Server 7.0, you don't have to set up a data source. It will be a direct connection.

　　Define the OLE DB Interface

　　There are up to six basic DBParms that need to be filled in order to connect to the OLE DB provider within PowerBuilder. The following parameters can be found on the Connection tab of the profile setup for OLE DB:

1. Profile Name Name of the connection profile
2. Provider Click the drop down arrow to select the provider you are connecting to
3. Data Source This varies depending upon which vendor you are using

　　ntersolv

　　If you are using an Intersolv provider, the Data Source name you specify in the profile setup is the actual Data Source name that was created via the Intersolv OLE DB Administrator. Click the drop down arrow to see all the data sources for the particular provider.

　　

Microsoft

　

4. User ID User ID necessary to connect to database
5. Password Password necessary to connect to database
6. Extended Properties The option can be used to define the database you want to connect to when using the SQLOLEDB provider. For example, enter database=pubs, to connect to the PUBS database.

　　For information on additional DBParms, refer to the Connecting to Your Database book.

　　OLE DB Object Interfaces

　　An OLE DB provider must support certain OLE DB object interfaces to meet the Microsoft
OLE DB 2.0 Specification. These interfaces are also necessary to connect to the provider within PowerBuilder. Please refer to Chapter 4 of the Connecting to Your Database book to get a list of the interfaces required by PowerBuilder.

　　Some vendors may not support certain additional OLE DB interfaces. If this is the case, you will get a message stating the provider does not support the interface and you will not be able to execute that certain function.

　　Common Error Messages

　　"The current provider does not support the ITableDefinition interface which is used to create/drop/alter tables."

　　This error occurs if the provider does not support the ITableDefinition interface. This will most likely occur when you use the OLE DB interface to connect to your database the first time from PowerBuilder. Upon initial connection to a database, PowerBuilder tries to create the catalog tables and if the provider does not support the ITableDefinition interface, these tables cannot be created. To remedy this problem, use PowerBuilder's native driver to make the initial connection and create the catalog tables. Then connect to that database using the OLE DB driver.

　　If the catalog tables exist and you still get this message, make sure the setting PowerBuilder Catalog Table Owner on the System tab of the profile is set to the owner of the catalog tables.

　　Note: In most cases, you will still be able to create tables within the database painter even if this message appears upon the initial connection.

"SQLState = IM002
Microsoft OLE DB Provider for ODBC drivers
[Microsoft] [ODBC Driver Manager] Data Source name not found and no default driver specified"

　　This error message occurs when the data source name specified in the Database Profile is not the same as the data source name defined in the ODBC Data Source Administrator.

"SQLState = S1000
[Intersolv] [ODBC ODBC/OLE DB Adapter Driver] ParseDisplayName failed"

　　This error occurs when you try to connect to a data source that uses a different data provider than the one specified in the Database Profile.

　　

Frequently Asked Questions

Q: Do I have to install the Microsoft Data Access Components (MDAC) even if I only use the Intersolv providers?

A: Yes. You have to install MDAC because in addition to installing the providers, it installs the OLE DB components that are necessary to utilize OLE DB with PowerBuilder.