The other day, a colleague and I were discussing some new Web applications I had recently written that accessed our AS/400 database. He asked about the data access technology I used, and when I told him ODBC, he said, “ODBC! Why are you writing to a specification that’s going to disappear soon? Microsoft is dropping ODBC for OLE DB, and you’re going to be left behind.” This made me realize how much confusion there is in AS/400 shops over the roles of ODBC and OLE DB. Some people think ODBC is dead, while others are die-hard users who are suspicious of moving their key client/server business applications to OLE DB.
To help you make intelligent AS/400 client/server decisions, this article looks at the use of ODBC, OLE DB, and ActiveX Data Objects (ADO) in the modern AS/400 shop. I examine what each technology is, what it does, how they work together, and how you can leverage them for application integration success both now and in the future. I attempt to pierce the confusion surrounding ODBC and OLE DB for AS/400 access and show you where I believe the marketplace is going.
What Is ODBC?
ODBC, which stands for Open Database Connectivity, is a data access API created by Microsoft. It is a superset of the X/Open and International Organization for Standardization (ISO) Call Level Interface standard for relational database access. Call Level Interface (CLI) and ODBC are so close that, if you have a C application written to ODBC, you can port that application to the AS/400 and CLI very easily.
SQL is the database access language at the heart of ODBC. Currently, over 55 database platforms offer ODBC connectivity in one form or another. ODBC connectivity can vary from level 1 through level 3 compliance. Level 1 indicates core ODBC functionality support, while level 3 indicates that the driver supports the latest ODBC specification functions. Several AS/400-capable ODBC drivers range from level 1 through level 3 support. IBM’s Client Access ODBC drivers (included with Client Access/400 for Windows 95/NT and Client Access Express for Windows) is currently somewhere between level 2 and level 3 support.
ODBC is a tried-and-trusted AS/400 data access technology. I use ODBC in over 37 applications for high-speed access to my AS/400. Like the AS/400, because it is a technology with some history behind it, most of the egregious bugs have already been worked out, so you can rely on it for mission-critical applications and data access.
OLE DB: The “Relative” Newcomer
At three years old, Object Linking and Embedding Database (OLE DB) is Microsoft’s newest low-level Component Object Model (COM) specification for accessing data sources. Notice that I said data sources, not databases. OLE DB was not created as a replacement for ODBC. OLE DB is a method that allows data providers to provide tabular access to any type of data, whether that data is relational or not. As shown in Figure 1, a programmer can use one OLE DB interface to consume data from a variety of information sources, including legacy and email databases, spreadsheets, plant-floor manufacturing devices, and telephone switching equipment.
OLE DB was created as an extension to Object Linking and Embedding (OLE). OLE allows a program to view sound files, graphs, spreadsheets, application programs, and other common PC file types as objects. OLE objects can expose their properties and methods through Microsoft’s COM interface.
OLE DB allows programs to access heterogeneous data sources by providing a core set of COM interfaces that behave the same across all providers. (I’ll explain exactly what a provider is in the next paragraph.) The core COM interfaces are designed to perform database services and allow data to appear as tables. Because all providers must support the core interfaces, an OLE DB program can manipulate email messages by using the same interface that queries an AS/400 table. As a bonus, OLE DB is extensible, and vendors can add extra functionality to the providers they create. OLE DB programs can query the provider to find out about these extra capabilities.
The OLE DB specification is divided into three areas: consumers, providers, and service providers. Consumers are programs that need data. They retrieve data from providers, which are written to contain and expose data to consumers and service providers. Service providers are also written to use providers to supply additional functionality for consumers to use. To make it easier to understand, imagine you want a fish dinner. The river (provider) is the source of fish. As the consumer, you can go directly to the provider and retrieve a fish via the fishing method, or you could take advantage of a service provider (a restaurant) to obtain your meal. The restaurant uses the fishing method to obtain the fish but has additional functionality and methods to prepare the fish, as in fried, poached, broiled, or flambé. The point is that at the heart of the transaction, you still have data (fish) and can access that data using the provider or a service provider.
Like fishing, OLE DB also comes with a price. Since OLE DB is a low-level interface, programming directly to an OLE DB provider can be as complicated as programming directly to the ODBC API. Luckily, Microsoft understood this problem and gave programmers the ActiveX Data Objects (ADO) interface to solve it.
ADO: Easing the Load of OLE DB Programming
ADO is a simple high-level interface into the low-level OLE DB object model. A programmer needs to learn only three simple objects in order to manipulate OLE DB data sources. Though similar to Microsoft’s older Remote Data Objects (RDO) and Data Access Objects (DAO), ADO is a much cleaner and simpler set of objects. ADO is the replacement technology for both DAO and RDO.
ADO is much more robust and efficient than its predecessors. ADO can automatically query an OLE DB provider and find the methods that it supports for data access. It can intelligently and automatically take advantage of any performance features that an OLE DB provider or service provider makes available. ADO also supports advanced cursor and synchronization facilities.
Microsoft makes available the Microsoft Data Access Components (MDAC), which includes everything you need for universal data access. MDAC will install ADO, OLE DB, and ODBC onto your Windows computer. It also installs a special Microsoft OLE DB provider that allows ADO to talk to an ODBC driver as if it were an OLE DB provider. This means you can program your applications using ADO, which is considered OLE DB, and then talk to the AS/400 through its ODBC driver. Then, using the same code, you can switch the driver to the Client Access OLE DB provider and convert your application from ODBC to the IBM OLE DB provider. Note that the code does not change; only the database provider changes (see Figure 2). The program is still an OLE DB application, even though at one point it communicates via ODBC. It is at this point that people become confused about ODBC versus OLE DB.
Which Technology Should You Use?
That you can still use an ODBC driver does not necessarily mean that you would want to. This mechanism is provided so that you can communicate with relational databases that support only ODBC. The AS/400 has several OLE DB providers available from different companies. By using an OLE DB provider, your program can have further interactions with the AS/400. Through OLE DB providers, you could directly manipulate information in AS/400 data queues or perform record-level access and manipulate indexes. OLE DB provides extensions that let you access other types of data and information on the AS/400 as if it were tabular data.
There is no question that any new development of client/server, n-tiered, or Web- based applications should be written using the ADO object model and not the ODBC API. In the coming years, OLE DB drivers will become more and more robust and will allow you to take advantage of increased functionality from the AS/400. Even if you write in ADO, you can still use the ODBC driver and later replace the driver with an OLE DB provider.
However, if you start with an OLE DB provider in the beginning, OLE DB will give you more flexibility, choices, and options in your development strategy. And you have several providers to choose from. Some OLE DB providers, such as HiT Software’s OLEDB/400, provide access to an optimized server and allow you to use OLE DB via SQL. Others, like ASNA, provide their interfaces via record-level access. IBM’s Client Access providers allow access to logical files via an index object and access to data queues via its service provider. Different data providers may increase application flexibility or performance depending on how the provider accesses AS/400 data and what additional extensions it provides. Figure 3 shows a listing of OLE DB providers and their Web addresses.
Should you reengineer your existing ODBC applications for OLE DB? Microsoft is still supporting the ODBC standard and will continue to do so for some time, so there’s no reason to dump ODBC if you don’t want to. ODBC is robust, and applications written to the ODBC API will still be very fast and stable. As evidenced by Microsoft’s example (the primary access to Microsoft’s SQLServer is still an ODBC driver), you do not need to abandon ODBC. The AS/400 has many ODBC drivers available for it, and most are very robust and bug free.
However, it might be foolhardy to develop new applications that are written specifically to the ODBC API. By writing to ADO, you are given the choice of using ODBC or an OLE DB provider for data access. The application will also be more open and flexible. Down the road, maintenance of the new program will be easier, and even more choices for data access providers will become available. Most companies will spend their development energies in making their OLE DB providers better and better, so you will soon be able to outstrip ODBC performance with an OLE DB provider. ODBC will still be around for a while, but any new development should be geared toward using ADO and OLE DB.
Another reason to start developing with OLE DB is that third-party software companies are starting to use it. Seagate Software’s Crystal Reports 7.0 uses OLE DB, and so does Brio.Enterprise 5.5.6. If you have Microsoft SQLServer, you will need an OLE DB driver for high-speed data transfer between it and your AS/400. More and more third- party applications will migrate to OLE DB this year, so it’s to your benefit to gain more OLE DB programming and administration experience.
I firmly believe that, within the year, almost everyone will be using ADO and OLE DB for developing client/server applications. It offers much more flexibility to the developer, and more and more connectivity options will be available. You can still take advantage of ODBC drivers, but I would recommend that you try out an OLE DB provider. Microsoft is committed to the OLE DB standards and to ADO. In fact, it has recently released ADO Multi Dimensional, which will be the standard for accessing OLAP databases. If you get your feet wet with OLE DB now, you will be preparing your career for the future of database access.
The Tricky Business of Compatibility
As if our jobs weren’t hard enough, IBM currently has two versions of OLE DB support. The Client Access for Windows 95/NT (the Windows 95/NT client) OLE DB provider is written for OLE DB Version 1.1, which matches up to the Microsoft ActiveX Data Objects (ADO) Version 1.0 specification. The Client Access Express for Windows (the Express client) provider was updated to OLE DB Version 1.5, which matches the ADO Version 1.5+ specification. Think this sounds confusing? Just wait!
Microsoft, lover of the standards it creates and then modifies, has released multiple versions of ADO, from
1.0 to a 2.5 beta. Some of these ADO versions will not work correctly with the IBM OLE DB drivers or, for that matter, with OLE DB drivers from other driver providers. To determine which version of ADO you have, execute the following code in VBScript or Visual Basic:
Dim C as New ADODB.Connection
X = C.Version
MsgBox X
(If using VB, make sure you have msado10.dll or msado15.dll referenced in your project. For VBScript, use the server.createobject method to create the ADO connection object.)
Alternately, you can point your Windows Explorer to C:Program FilesCommon FilesSystemADO and right- click on the msado15.dll file. From the pop-up menu that appears, select Properties and click on the Version tab to see your ADO version number.
ADO 1.0 and ADO 1.5 and later are not binary-compatible. This means that you can have ADO Version 1.0 and ADO Version 1.5 or later installed on your machine at the same time. If you then install ADO 2.1 on a machine that has ADO 1.0 and ADO 1.5, you will be upgrading only the ADO 1.5 files. Remember this fact when you are referencing ADO in your programming projects to ensure that you are referencing the ADO libraries you really want to use.
OK, now that you know how many confusing ADO standards there are, how do you determine what you should do? I recommend that you download and install the latest Microsoft Data Access Components (MDAC) 2.1 release from Microsoft. This will get the latest general availability release of OLE DB, ODBC, and other good stuff onto your computer. (MDAC is available from the Microsoft Universal Data Access Web page at www.microsoft.com/data/download.htm.)
If you somehow have a copy of the 2.5 beta or want to remove an older version of ADO/OLE DB completely, you will need the utility clsidvw.exe, which is available at the Microsoft site. To get the utility and documentation on its usage, go to support.microsoft.com/servicedesks/msdn/ and search for article ID Q176562. This tool will remove all of the MDAC shared components so that you can perform a clean
installation of the proper version. (Please note that this utility is available from Microsoft but is not supported by Microsoft.)
What’s the proper version of ADO for you to use in your AS/400 client/server programming? That depends on which release of Client Access you have. As I stated previously, Client Access for Windows 95/NT is written for OLE DB 1.0 and should be used with ADO 1.0. However, ADO 1.0 has many problems, so I recommend that you use ADO 2.1 and get the proper service packs associated with your particular Client Access release. The follow paragraphs list the service packs and fix files required to run ADO 2.1 on each Client Access release.
The OLE DB provider that comes with the V3R1M3 Windows 95/NT client supports ADO 1.0, 1.5, and 2.0. However, since these releases are no longer available, I suggest you install MDAC Release 2.1. You will need to download service pack SF56517 or later to use this provider with ADO 2.1. IBM is not planning to support ADO Version 2.5 on V3R1M3.
The OLE DB provider that comes with the V3R2M0 Windows 95/NT client supports ADO versions 1.0 to 2.1. To run ADO 2.1, you will need to download and install the fix files available at ftp://ftp.software.ibm.com/ as400/products/clientaccess/win32/v3r2m0/files/ado and install service pack SF55446 or later. IBM is planning to support Version 2.5 on V3R2M0.
Client Access Express for Windows V4R4M0 supports ADO 2.1 and requires the service pack SF58098 or later.
It sounds confusing, but once you coordinate your Client Access providers with ADO, developing with ADO is actually a lot easier than interpreting the ADO versions.
Application
ADO OLAP Data
OLE
DB
Figure 1: OLE DB can act as the interface to all types of corporate data sources.
ODBC AS/400 Email Data
Spreadsheet
OLE DB
AS/400
Application
ADO AS/400
OLE DB
ODBC Provider
OLE DB Provider
Figure 2: OLE DB uses either an OLE DB driver or ODBC to access AS/400 data seamlessly.
Company Product Web Location
ASNA Acceler8DB and www.asna.com
DataGate/400 HiT Software HiT OLEDB/400 www.hit.com/hitweb/daccess/dchome.htm IBM Corporation Client Access www.as400.ibm.com/clientaccess/oledb International Software Group ISG Navigator www.isgsoft.com/products/navigator MERANT SequeLink OLE DB Edition www.merant.com MetaWise Computing MetaWise DP www.metawise.com Microsoft Corporation Microsoft SNA Server www.microsoft.com/sna/default.asp
Figure 3: These different OLE DB driver providers may increase application flexibility or performance.
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online