OLE DB (Microsoft SQL Server) Part 1

This article discusses the use of the OLE DB database interface, with an emphasis on connecting to Microsoft SQL Server 2000 from PowerBuilder 9. I'll start by discussing OLE DB in a generic fashion, then move to PowerBuilder interfaces with OLE DB, and finally discuss the specifics of accessing Microsoft SQL Server from PowerBuilder.

This is not about developing OLE DB data interfaces, but rather an overview of OLE DB functionality followed by some details on utilizing OLE DB data sources from your PowerBuilder applications.

Please note that Microsoft's native driver for Microsoft SQL Server 2000 is now OLE DB. The "MSS Microsoft SQL Server" native database interface from PowerBuilder uses the Microsoft dblib interface and is meant for Microsoft SQL Server 6.5. You can continue to use the old database driver even with a later version of Microsoft SQL Server, but due to Microsoft having frozen their dblib interface at the Microsoft SQL Server 6.5 version level, none of the newer features of Microsoft SQL Server will be accessible.

In general, please keep in mind that PowerBuilder to a large extent provides database-independent functionality across most of its objects.

What Is OLE DB?
OLE DB is a definition of data access interfaces that have been designed by Microsoft. It specifies or defines a set of interfaces that allow access to many different types of data sources, including SQL data sources. Its design was based on the component object model.

In many ways, for the end user OLE DB and ODBC appear to provide similar means of accessing SQL data. OLE DB does more than just provide access to SQL data, though. It allows access to several other types of data formats such as e-mail, spreadsheets, and active directory structures. OLE DB defines a comprehensive set of data interfaces that need to be utilized by sources of data as well as by applications that need to read data from those sources.

The OLE DB Programmer's Reference manual is voluminous and it's a fairly daunting task to read the entire reference. Of course, no one data store is expected to support all of the functionality defined in the manual.

The OLE DB interface specifications provide a mechanism that allows the ever-increasing set of data sources to be accessed by a correspondingly ever-increasing set of applications that need to access that data.

In OLE DB terminology, the applications that are sources of data are known as data providers. Some examples are relational databases, e-mail servers, and indexed files.

Applications that read, interpret, and manipulate that data are known as data consumers. Some examples are the PowerBuilder development environment, business applications written in PowerBuilder, or a programming language that makes calls using the OLE DB interface specification to work with data from a data provider that also uses the OLE DB interface specification.

In addition, the OLE DB specification allows for service providers that provide a service that could be an extension of typical DBMS functionality. A couple of examples of service providers are the Microsoft Remoting Provider and the Microsoft Persistence Provider.

OLE DB makes it easier for various data providers, service providers, and data consumers to communicate efficiently with each other by defining a set of standard interfaces that all these elements adhere to (see Figure 1).

OLE DB data providers typically provide only those interfaces that are supported by the underlying data store. There's a certain base level of functionality that must be supported by all data providers. Beyond the functionality directly supported by the data store, certain generic functions could be written that provide additional data interfaces. One example is scrolling through a data set. Certain generic methods of scrolling could be used to provide this data interface if the data store does not directly support scrolling.

Enforcing a basic number of interfaces that are required to be supported by all data providers along with a set of generic interfaces makes it easier to write data providers. They only need to write those specific interfaces that are supported directly by the data store. Generic functionality can be made available for other base functionality.

Another advantage to requiring support of a base level of functionality is that data consumers can now rely on getting a rich level of functionality even if the data store does not directly support it, since service providers can provide extended functionality that will become available to the data consumer.

The main difference between OLE DB and ODBC is that OLE DB is designed to access nonrelational data, such as mail, in addition to relational SQL-based data, whereas the ODBC standard provides a unified way to access just SQL-based data.

Connecting Using the OLE DB Interface
OLE DB is designed to be data-provider agnostic as well as data-consumer agnostic. It is designed to allow any data consumer to connect to any data provider through a set of standard interfaces designed by Microsoft. The steps required to connect to an OLE DB data provider are mostly the same irrespective of which specific data provider you are connecting to. The differences are usually found in some parameter requirements for each data provider.

A typical sequence of events would be:

OLE DB Interfaces Provided with PowerBuilder
PowerBuilder 9.0 Enterprise includes the option to install DataDirect Providers for Informix, Oracle 7 and 8, Microsoft SQL Server, Sybase, and XML.

To use these DataDirect OLE DB Drivers, you need to define a data source via the DataDirect OLE DB Administrator. You need to execute the program pbadmin.exe from Program FilesSybaseSharedDataDirect.

Additional information on these drivers and any updates are available at www.datadirect-technologies.com.

Universal Data Link (.UDL) Files
Universal Data Link files provide a method of encapsulating information about an OLE DB connection in a file instead of coding this information in an application. This file can be used by a server component to access the data provider, increasing security because the connection information would never need to pass over the wire or over most of the network.

Create a .UDL File

Go to the "Provider" tab and select the provider you plan to use. In this example, we use the Microsoft OLE DB Provider for SQL Server. As you can see, there's a wide variety of OLE DB providers to choose from, including several from Sybase.

Go to the "Connection" tab and select the server where you set up the OLE DB example SQL Server database. In this example, I have it on the local server, so I set the value to (local) (see Figure 2).

Also specify the login information. I have set this connection to use integrated Windows NT Authentication.

Select the database you want to connect to. Click on the "Test Connection" button to ensure that the connection works as expected (see Figure 3).

Now click on the "Advanced" tab. Depending on the length of the queries you expect to have running, you may need to change the value of the Connection Timeout parameter.

Finally go to the "All" tab. This gives you an overview of all the settings for the connection. It is a good idea at this point to select the "Application Name" parameter and click the "Edit Value" button. Set this to your application name. In some cases when you may need to troubleshoot the database server, it's very useful to see the names of the applications that have made the various connections to the database server.

Microsoft OLE DB Provider for Microsoft Active Directory Services
Another example of using OLE DB would be to connect to Microsoft's Active Directory Services. This allows you to use SQL commands to query the Active Directory, making it much easier to work with it for those who know SQL but may not know the structure of the Active Directory in detail.

More information on how to use this OLE DB Provider can be found at http://msdn.microsoft.com/library/default.asp? url=/library/en-us/ado270/htm/pg_ado_providers_4.asp.

There is a lot of great information on OLE DB available on the Microsoft Web site so I won't repeat it all here. A good starting point is "Choosing Your Data Access Strategy" at http://msdn.microsoft.com/library/default.asp? url=/nhp/Default.asp?contentid=28001860.

Accessing Microsoft SQL Server 2000 from PowerBuilder Using OLE DB
The PowerBuilder 9 OLE DB interface works through a DLL named PBOLE90.DLL to access data through OLE DB. Any OLE DB provider must support OLE DB version 2.0 or later in order for PowerBuilder to be able to work with it.

PowerBuilder requires that the OLE DB provider support a certain set of interfaces in order for PowerBuilder to connect to and work correctly with the data provider. A list of the required interfaces includes IAccessor, IColumnsInfo, ICommand, and ICommandProperties.

In addition, PowerBuilder utilizes several other interfaces for various functions. The list of those interfaces includes ICommandPrepare, IDBInfo, IDBCommandWithParameters, and IErrorInfo.

Any access to an OLE DB data provider, whether from PowerBuilder or from any other data consumer, goes through several layers of interfaces before the request reaches the data provider, and then makes the return trip through the same set of interface layers for the data to get back to the consumer. Each layer could come from a different vendor, and they must all be able to work together for the application to successfully interact with the database.

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.

Connecting to Microsoft SQL Server 2000 Using OLE DB
Let us look briefly at the various parameters required when connecting to an OLE DB data source.

Open the Database Profiles dialog and click the "New" button.

Enter the parameters. This will be modified based on how you have installed the OLE DB Example database.

As you can see in the "Preview" tab, this translates to the following code:

// Profile OLEDB-SQLServer-Local
SQLCA.LogPass = <*****>
SQLCA.LogId = "sa"
SQLCA.AutoCommit = False

Connecting to Microsoft SQL Server Using a .UDL File
Earlier in the article we created the .UDL file; now let's look at how to use this from a PowerBuilder application.

Open the Database Profiles dialog and click the "New" button.

Enter the database Profile Name, then check the check box for the Data Link and select the name of the .UDL file you just created.

Go to the "Preview" tab and click "Test Connection".

Since the parameters are taken from the .UDL file, the other parameters you may enter in the PB database profile dialog are ignored.

When you use this connection from PB, it behaves like any other database connection (see Figure 4).

Mapping PowerBuilder Data Types to Microsoft SQL Server 2000
Table 1 lists a few of the commonly used Microsoft SQL Server system data types and the corresponding PowerBuilder data types. Microsoft SQL Server allows you to create user-defined data types that are essentially just aliases for system data types.

Deployment Considerations
Apart from the normal deployment files required for deployment of a PB client application, you need OLE DB drivers for any data sources you need to access. You must install Microsoft's Data Access Components software on each user's computer if it is not installed already.

You can use the MDAC_TYP.EXE setup file in the Support directory on the PowerBuilder CD to update users' computers. The Microsoft OLE DB providers SQLOLEDB and MSDASQL are installed when you run MDAC_TYP.EXE. Client machines that have Windows XP SP1 installed will not require this install since the OLE DB drivers are installed as a core part of Windows XP SP1.

Troubleshooting OLE DB Connections
A couple of common error messages you might see are:

In addition to this, as mentioned earlier, the OLE DB providers must support OLE DB version 2.0 or later, and a certain set of interfaces that PowerBuilder uses.

OLE DB is a powerful data interface specification that allows data consumers to access many different types of data sources from various data providers and service providers using a consistent interface. The sample application for this article can be downloaded from zip file.

In Part 2 I'll discuss OLE DB and how it interacts through DataWindows, as well as storing binary objects in the Microsoft SQL Server database.

This article was originally intended to be published as part of PowerBuilder 9 Client/Server Development by various authors (ISBN 0672325004) from Sams Publishing. Also look for PowerBuilder 9 Internet and Distributed Application Development.

© 2008 SYS-CON Media