OLE DB is a very hot topic all across the world of client/server development, as well as in the Internet and intranet spheres. Many programmers are searching the Internet, reading magazines, and buying books trying to understand this new technology. However, there’s a lot of misunderstanding about OLE DB, especially how it relates to ODBC. Quite simply, OLE DB is a Microsoft technology that allows users to access any form of data, including graphics and relational data. OLE DB provides a unified method of access to all types of data in any supported form of data store, eliminating the need to use ODBC to access relational databases and a second method to data stored in other ways. OLE DB has the potential to make client/server development far easier than it was in the past.

Both IBM and Microsoft will soon have OLE DB providers available for the AS/400. IBM is beta testing its provider in a project called Lightning, and Microsoft is getting ready to ship its OLE DB Provider for AS/400 and VSAM, which was developed as a part of a project called Thor. Although both OLE DB providers are based on the same standard and both make AS/400 data available to client systems, there are some significant differences between the two products. These differences, which are the focus of this article, affect how and if you’ll implement and use OLE DB in your development projects.

I’ll start by giving you a short overview of OLE DB, in case you’re not already familiar with it. However, the focus of this article is to compare and contrast the two soon- to-arrive OLE DB providers so you’ll be better prepared to decide which is right for your use. Let’s get started with the OLE DB overview.

I suggest you read through it even if you feel you’re already comfortable with the concepts behind OLE DB because there is a lot of misinformation floating around.

Overview of OLE DB

OLE DB is a standard that Microsoft created to allow access to data from a wide variety of data sources. It’s a part of what Microsoft calls Universal Data Access (UDA), which is designed to address some of the shortcomings of ODBC. ODBC is fine for

accessing data in relational databases, but these days not all data is in a relational database. Graphics files from a CAD application can be in a special “database” used only by that application. Network directory information is in a hierarchical, but often nonrelational, database. These types of data are unavailable to an application using ODBC.

In response to this problem, Microsoft created UDA and OLE DB. (If you want to find out more about UDA, use the URL http://www.microsoft.com/data/. UDA and its data-access component, OLE DB, provide access to all forms of data from almost any data source possible using a common interface. Data from the AS/400, from a network directory, and from a CAD application would all be accessed in the same way.

Just as a program needs an ODBC driver to be in place before it can use ODBC, a program must have an OLE DB provider available. IBM’s OLE DB requires Client Access/400 for Windows 95/NT, and Microsoft’s OLE DB Provider for AS/400 and VSAM is dependent on SNA Server.

ADO: An Easier Interface for OLE DB

OLE DB, like ODBC, has a C-style interface that can be very challenging to use. Fortunately, like ODBC, there is a higher-level abstraction of OLE DB that is a lot more manageable. For OLE DB, this abstraction is called ADO (ActiveX Data Objects). ADO makes the use of OLE DB in languages such as Visual Basic, Java, and Delphi easier (read: more practical).

As you might guess from its name, ADO uses objects and methods to access data using an OLE DB provider. For example, to connect to an AS/400, you create an instance of a “connection” object that points to an ODBC data source connected to an AS/400. There are also collection, command, field, and recordset objects in ADO. To use ADO in most languages, you set properties for the object, then use methods to affect the object. For example, a recordset object would point to a specific file using properties of the recordset object. You would use methods such as MoveNext and MoveFirst to navigate through the recordset. (Figure 1 shows some code from a Visual Basic program. This code connects to the AS/400 and a specific table using ADO.)

You can use the basic OLE DB interfaces to access data on the AS/400. You can also use ADO from languages such as Visual Basic or Delphi. Another option uses the Active Server Pages (ASP) support for ADO in Microsoft’s Internet Information Server (IIS). This IIS integration with the AS/400 would give you a server-based option for integrating data from your AS/400 with your Windows NT-based Web site.

I’ve only outlined OLE DB and ADO at a conceptual level. If you’d like more hands-on details, check out another article I wrote. In “Lightning Strikes the AS/400,” Midrange Computing, , I walked through a working example of how IBM’s Lightning OLE DB provider in Visual Basic is used to provide record-level access. Now, let’s start looking at some of the differences between the OLE DB providers coming from IBM and Microsoft.

Depth of Support

One of the important benefits of OLE DB is that it provides a consistent interface to different types of data from different data stores. However, there are significant differences between the support for AS/400 data types in the two OLE DB providers for the AS/400. Here are some of the AS/400 data sources that the Lightning OLE DB provider supports:

• Stored Procedures
• Tables
• Commands
• Programs
• Data Queues Some of you might wonder how a command or a program could be considered a data source. The designers of OLE DB understood that in some cases you need to be able to run commands in order to prepare data before it can be used, so support for commands

is designed into the standard for OLE DB. (Commands and programs are treated as the same type of object in OLE DB.)

Here’s the list of AS/400 data sources that Microsoft’s OLE DB Provider for AS/400 and VSAM supports:

• Tables Quite a bit of difference, as you can see. I suspect the reason for the difference lies in the way that Microsoft’s OLE DB provider accesses the system. Microsoft uses Distributed Data Management (DDM) to access data, so it is limited to supporting record- level access because that’s all that DDM supports. If Microsoft wants to support the other AS/400 objects that Lightning supports, such as data queues and commands, it will have to use routes other than DDM. That’s not to say that IBM’s product is always the best choice. Client Access/400 only works with the AS/400. Microsoft SNA Server and the upcoming OLE DB Provider for AS/400 and VSAM work withAS/400s and mainframes. So if you’re in a mixed AS/400-and-mainframe environment, Microsoft’s OLE DB pro-vider might be a better choice for your use.

Client “Thickness”

There’s one difference between the OLE DB providers from IBM and Microsoft that I would like to point out. The two products have very different footprints. When I use that term, I mean the resources that you need on the client system in order support the OLE DB provider. Although the ongoing debate about whether thin clients are better than thick clients usually centers around network computers versus PCs, PC clients also come in thick and thin varieties. Thin client software usually has more of the processing done on the server than thick client software, so the hardware requirements for thick client software are often greater than the requirements for thin client software. You usually need more RAM and disk space for a thick client.

If all you need is OLE DB connectivity, then there’s a difference in the “thickness” of the two OLE DB providers. Client Access/400 includes a 5250 emulator and other utilities. SNA Server only provides the basic connectivity to the AS/400. If you choose Microsoft’s product but you still need a 5250 emulator, then you’ll have to purchase a package such as Wall Data’s RUMBA. However, if you’re only going to be using the connectivity to the AS/400 from client/server applications (including browser access), then the footprint of SNA Server is much smaller than Client Access/400.

A PC running Client Access/400 for Windows 95/NT should have at least 50 MB of disk space and 16 MB of RAM for Windows 95 or 32 MB of RAM for Windows NT. In addition, IBM recommends an Intel 486 processor for Windows 95 and 100 MHz (or faster) Intel Pentium processor for Windows NT. These are, as a practical matter, fairly low-end requirements. In most cases, you’ll want both additional memory and faster processors to get acceptable performance.

If you’re not going to need 5250 emulation or the other utilities included in Client Access/400, the hardware requirements for SNA Server are much lower. In many cases, the hardware required is not much above the basic hardware requirements for Windows 95 or Windows NT. This is because much of the work in supporting connectivity to the AS/400 is done by the server instead of by the client, as is the case with Client Access/400.

Making the Right Choice for You

As you’ve seen, there are some significant differences in the architectures of the two OLE DB providers. Some of the differences stem from the fact that one product is based on direct AS/400 connectivity while the other is based on SNA gateway connectivity to the AS/400. I can’t tell you which solution is right for you. You’re the only person who has the necessary knowledge of your company to make that decision. The points that I’ve raised will only help you answer some of the questions you’ll need to address in order to select the best OLE DB provider.

Are you using data queues in your applications? If so, then the Client Access/400 OLE DB provider might be the best answer because the Microsoft OLE DB provider can’t

access data queues. Are you in a mixed mainframe and AS/400 environment? If so, the Microsoft’s OLE DB provider might be the better answer because you won’t have two different vendors to turn to for support of your OLE DB connectivity. Finally, is client footprint a concern of yours? If so, and if you don’t need 5250 connectivity or the other utilities in Client Access/400, then Microsoft’s provider might be the better answer because it doesn’t have the overhead required to support all those applications that you don’t need.

If you want more information about IBM’s OLE DB provider, check out IBM’s Web site at http://www.as400.ibm. com/lightning/homepg.htm. If you want more information about Microsoft’s OLE DB Provider for AS/400 and VSAM, go to http://www.microsoft.com/ sna/thor.htm.

Public Sub OpenLinks()

Dim varRcds As Variant Dim varParms As Variant Const DBPROPVAL_UP_DELETE = 2 Const DBPROPVAL_UP_INSERT = 4

‘{{DA400_TABLE_OPENS_BEGIN}} Set CUSTOMER.ActiveConnection = MCRISC CUSTOMER.Properties(“Updatability”) = DBPROPVAL_UP_INSERT + DBPROPVAL_UP_DELETE CUSTOMER.CommandText = “LIGHTNING.CUSTOMER” CUSTOMER.Parameters.Append CUSTOMER.CreateParameter(“P1”, adChar, adParamInput, 1) Set CUSTOMER_DATA = CUSTOMER.Execute(varRcds, varParms, adCmdTable) ‘{{DA400_TABLE_OPENS_END}}

End Sub

Figure 1: Sample of code used to connect to an AS/400 table for read, insert, and delete access

Also Read

The API Corner: Moving Jobs Within and Across Job Queues

The API Corner: What Time Zone Do You Want That Date and Time In?

The API Corner: How Do You Want That Date and Time?