About this chapter
This chapter describes the OLE DB interface and explains how to prepare to use this interface and how to define the OLE DB database profile.
For more information
This chapter gives general information about using the OLE DB interface. For more detailed information:
-
See the OLE DB Programmer's Guide in the Microsoft MSDN library at http://msdn.microsoft.com/en-us/library/ms713643.aspx.
-
Use the online Help provided by the data provider vendor.
-
Check to see if there is a technical document that describes how to connect to your OLE DB data provider.
You can access a wide variety of data through OLE DB data providers in PowerBuilder. This section describes what you need to know to use OLE DB connections to access your data in PowerBuilder.
Supported OLE DB data providers
For a complete list of the OLE DB data providers supplied with PowerBuilder and the data they access, see the section called “Database preferences and supported database interfaces” in Connection Reference and the section called “Database parameters and supported database interfaces” in Connection Reference.
OLE DB API
OLE DB is a standard application programming interface (API) developed by Microsoft. It is a component of Microsoft's Data Access Components software. OLE DB allows an application to access a variety of data for which OLE DB data providers exist. It provides an application with uniform access to data stored in diverse formats, such as indexed-sequential files like Btrieve, personal databases like Paradox, productivity tools such as spreadsheets and electronic mail, and SQL-based DBMSs.
The OLE DB interface supports direct connections to SQL-based databases.
Accessing data through OLE DB
Applications like PowerBuilder that provide an OLE DB interface can access data for which an OLE DB data provider exists. An OLE DB data provider is a dynamic link library (DLL) that implements OLE DB function calls to access a particular data source.
The PowerBuilder OLE DB interface can connect to any OLE DB data provider that supports the OLE DB object interfaces listed in the following table. An OLE DB data provider must support these interfaces in order to adhere to the Microsoft OLE DB 2.0 specification.
|
IAccessor |
IDBInitialize |
|
IColumnsInfo |
IDBProperties |
|
ICommand |
IOpenRowset |
|
ICommandProperties |
IRowset |
|
ICommandText |
IRowsetInfo |
|
IDBCreateCommand |
IDBSchemaRowset |
|
IDBCreateSession |
ISourcesRowset |
In addition to the required OLE DB interfaces, PowerBuilder also uses the OLE DB interfaces listed in the following table to provide further functionality.
|
OLE DB interface |
Use in PowerBuilder |
|---|---|
|
ICommandPrepare |
Preparing commands and retrieving column information. |
|
IDBInfo |
Querying the data provider for its properties. If this interface is not supported, database connections might fail. |
|
IDBCommandWithParameters |
Querying the data provider for parameters. |
|
IErrorInfo |
Providing error information. |
|
IErrorRecords |
Providing error information. |
|
IIndexDefinition |
Creating indexes for the extended attribute system tables. Also creating indexes in the Database painter. If this interface is not supported, PowerBuilder looks for index definition syntax in the pbodb.ini file. |
|
IMultipleResults |
Providing information. |
|
IRowsetChange |
Populating the extended attribute system tables when they are created. Also, for updating blobs. |
|
IRowsetUpdate |
Creating the extended attribute system tables. |
|
ISQLErrorInfo |
Providing error information. |
|
ISupportErrorInfo |
Providing error information. |
|
ITableDefinition |
Creating the extended attribute system tables and also for creating tables in the Database painter. If this interface is not supported, the following behavior results:
|
|
ITransactionLocal |
Supporting transactions. If this interface is not supported, PowerBuilder defaults to AutoCommit mode. |
Accessing Unicode data
Using the OLE DB interface, PowerBuilder can connect, save, and retrieve data in both ANSI/DBCS and Unicode databases but does not convert data between Unicode and ANSI/DBCS. When character data or command text is sent to the database, PowerBuilder sends a Unicode string. The data provider must guarantee that the data is saved as Unicode data correctly. When PowerBuilder retrieves character data, it assumes the data is Unicode.
A Unicode database is a database whose character set is set to a Unicode format, such as UTF-8, UTF-16, UCS-2, or UCS-4. All data must be in Unicode format, and any data saved to the database must be converted to Unicode data implicitly or explicitly.
A database that uses ANSI (or DBCS) as its character set might use special datatypes to store Unicode data. Columns with these datatypes can store only Unicode data. Any data saved into such a column must be converted to Unicode explicitly. This conversion must be handled by the database server or client.
When you access an OLE DB data provider in PowerBuilder, your connection goes through several layers before reaching the data provider. It is important to understand that each layer represents a separate component of the connection, and that each component might come from a different vendor.
Because OLE DB is a standard API, PowerBuilder uses the same interface to access every OLE DB data provider. As long as an OLE DB data provider supports the object interfaces required by PowerBuilder, PowerBuilder can access it through the OLE DB interface.
The following figure shows the general components of a OLE DB connection.
Figure: Components of an OLE DB connection
PowerBuilder lets you access data with any OLE DB data provider if that data provider supports the OLE DB object interfaces required by PowerBuilder. In most cases, these drivers work with PowerBuilder. However, Appeon might not have tested the drivers to verify this.
The OLE DB interface uses a DLL named PBOLE.dll to access a database through an OLE DB data provider.
Required OLE DB version
To use the OLE DB interface to access an OLE DB database, you must connect through an OLE DB data provider that supports OLE DB version 2.0 or later. For information on OLE DB specifications, see the Microsoft documentation at http://msdn.microsoft.com/en-us/library/default.aspx.
Before you define the interface and connect to a data provider through OLE DB:
-
Install and configure the database server, network, and client software.
-
Install the OLE DB interface and the OLE DB data provider that accesses your data source.
-
Install Microsoft's Data Access Components software on your machine.
-
If required, define the OLE DB data source.
Step 1: Install and configure the data server
You must install and configure the database server and install the network software and client software.
To install and configure the database server, network, and client software:
-
Make sure the appropriate database software is installed and running on its server.
You must obtain the database server software from your database vendor. For installation instructions, see your database vendor's documentation.
-
Make sure the required network software (such as TCP/IP) is installed and running on your computer and is properly configured so that you can connect to the data server at your site. You must install the network communication driver that supports the network protocol and operating system platform you are using.
For installation and configuration instructions, see your network or data source administrator.
-
If required, install the appropriate client software on each client computer on which PowerBuilder is installed.
Client software requirements
To determine client software requirements, see your database vendor's documentation.
Step 2: Install the OLE DB interface and data provider
If you install PowerBuilder using the PowerBuilder Installer (an online setup program), the OLE DB provider is automatically installed. If you install PowerBuilder using the downloaded installation package (an offline setup program), select OLE DB Driver from the list of components. You can install the OLE DB data providers shipped with PowerBuilder, or you can install data providers from another vendor later.
Step 3: Install the Microsoft Data Access Components software
The PowerBuilder OLE DB interface requires the functionality of the Microsoft Data Access Components (MDAC) version 2.8 or higher software.
To check the version of MDAC on your computer, you can download and run the MDAC Component Checker utility from the MDAC Downloads page at http://msdn.microsoft.com/en-us/data/aa937730.aspx.
OLE DB data providers installed with MDAC
Several Microsoft OLE DB data providers are automatically installed with MDAC, including the providers for OLE DB Provider for SQL Server (SQLOLEDB) and OLE DB Provider for ODBC (MSDASQL).
Step 4: Define the OLE DB data source
Once the OLE DB data provider is installed, you might have to define the OLE DB data source the data provider will access. How you define the data source depends on the OLE DB data provider you are using and the vendor who provided it.
If you are connecting to an ODBC data provider (such as Microsoft's OLE DB Provider for ODBC), you must define the ODBC data source as you would if you were using a direct ODBC connection. To define an ODBC data source, use Microsoft's ODBC Data Source Administrator. You can access this utility from the Control Panel in Windows or from the Database painter or Database Profile Setup dialog box in PowerBuilder.
Using the OLE DB Database Profile Setup
To define a connection through the OLE DB interface, you must create a database profile by supplying values for at least the basic connection parameters in the Database Profile Setup -- OLE DB dialog box. You can then select this profile anytime to connect to your data in the development environment.
For information on how to define a database profile, see Using database profiles.
Specifying connection parameters
You must supply values for the Provider and Data Source connection parameters. Select a data provider from the list of installed data providers in the Provider drop-down list. The Data Source value varies depending on the type of data source connection you are making. For example:
-
If you are using Microsoft's OLE DB Provider for ODBC to connect to the Demo Database, you select MSDASQL as the Provider value and enter the actual ODBC data source name (for example, Demo Database) as the Data Source value.
-
If you are using Microsoft's OLE DB Provider for SQL Server, you select SQLOLEDB as the Provider value and enter the actual server name as the Data Source value. You must also use the Extended Properties field to provide the database name (for example, Database=Pubs) since you might have multiple instances of a database.
Note that Microsoft's OLE DB Provider for SQL Server (SQLOLEDB) is not maintained any more and it is not recommended to use it for new development.
Note
If you are using Microsoft OLE DB Driver for SQL Server (the new generation of OLE DB provider for SQL Server), you should select the MSOLEDBSQL SQL Server interface in PowerBuilder; and if you are using SQL Server Native Client, you should select the SNC SQL Native Client interface in PowerBuilder.
Using the Data Link API
The Data Link option allows you to access Microsoft's Data Link API, which allows you to define a file or use an existing file that contains your OLE DB connection information. A Data Link file is identified with the suffix .udl. If you use a Data Link file to connect to your data source, all other settings you make in the OLE DB Database Profile Setup dialog box are ignored.
To launch this option, select the File Name check box on the Connection tab and double-click on the button next to the File Name box. (You can also launch the Data Link API in the Database painter by double-clicking on the Manage Data Links utility included with the OLE DB interface in the list of Installed Database Interfaces.)
For more information on using the Data Link API, see the OLE DB Programmer's Guide in the Microsoft MSDN library at http://msdn.microsoft.com/en-us/library/ms713643.aspx.
