In-Depth

Say Hello to ADO

• By Deborah Melewski
• June 15, 2001

Microsoft Corp. has a history of making things work. For example, the company is one of the few organizations that has established de facto standards in the data access market. The firm's Object Database Connectivity (ODBC) technology is an example of this success.

Initially targeted as an access path to relational data, ODBC let users break away from using any one particular database vendor. But the technology is now encountering some limitations, as many current environments encompass both relational and non-relational data.

This then is the goal of Microsoft's budding ActiveX Data Objects (ADO) format, which seeks to abstract-out some deep programmatic interface issues. Moreover, it supports many more diverse data types. As the Microsoft standard is still young, pledges of third-party support are only beginning to come online in the form of products.

"We found that data in the world is not living predominantly in relational databases," said Dave Mendlen, Microsoft Visual Studio product manager. The popularity of the Internet has only increased the need to access non-relational data.

Redirecting its focus to a broader base of data sources, Microsoft introduced OLE DB. For users, OLE DB works the same as ODBC, but in addition to relational data it can access non-relational data. According to Mendlen, an example of this is your mail system, which can have an OLE DB driver that can talk to an index server. Web site data is captured in this way, and the driver can then "cruise" through all of the data as though it were a relational source. OLE DB specifies a set of Microsoft Component Object Model (COM) interfaces that encapsulate database services.

In the process of moving toward OLE DB, however, users realized that there were thousands of ODBC drivers on the market. Those in proprietary formats had to be built by a developer, which required a great deal of work.

To help companies support both ODBC and non-relational data, Microsoft built an OLE DB driver for ODBC Providers, which it then included in its Visual Studio product. Also included in Visual Studio are ODBC drivers to Microsoft SQL Server, Microsoft Access, Microsoft FoxPro, Oracle and AS/400 Vsam databases. In addition, many third-party OLE DB Providers and ODBC drivers are now available for Informix, Sybase, IBM DB2 and others. The OLE DB Data Providers are actually direct interfaces between the application and native data.

"Conceptually, as a developer you can still get to an ODBC data source with OLE DB," said Mendlen. "We handle the translation. We do recommend you go through OLE DB for better performance, though, rather than using this extra layer."

ADO was born a little more than a year ago to simplify development by talking to data objects by default. ADO Version 2.0 is built into Microsoft's latest release of Visual Studio, Version 6.0. As a high-level interface, ADO can be used by Visual Studio or any development tool to write database applications.

But if ADO has been around for a year, why are companies just now getting into it? "[Microsoft] released it to the public a year ago, but we were careful about its positioning," said Mendlen. "We were straightforward about it being a first release; we kept the volume low because we were working toward a more strategic version."

ADO is a component of Microsoft's Universal Data Access strategy, a middleware-based solution that provides standards-based access to all types of data, including SQL relational, non-SQL and unstructured data in all types of applications. ADO is a high-level interface for data objects, and is expected to replace Data Access Objects (DAO) and Remote Data Objects (RDO) -- a COM interface that had become very successful in the Visual Basic community -- over time.

ADO isolates programmers from OLE DB technology. It can also be implemented across development tools, including Visual Basic, C and C++, Java and so on. "ADO's job is to simplify access to OLE DB for developers," explained Mendlen. "ADO is an abstraction of interfaces that make the difficult easy. But if a developer needs to talk to OLE DB directly, they can. For example, some applications may require direct interfaces because they are faster. On the other hand, those interfaces can be painful and hard to program."

For example, some developers do not want to use the OLE DB and ADO level of abstraction to get to data. The alternative, the native driver, may be faster than talking through ODBC or OLE DB. According to Mendlen, it depends a lot on how the driver was written. "Sometimes the OLE DB or ODBC drivers are faster," he said. "But some native drivers are built poorly. What it comes down to is that you can still have a huge tradeoff. You are locked into proprietary databases and vendors with native drivers," he explained.

Through ADO, built-in Remote Data Services (RDS) technology allows a Notebook user to connect an application to the network. RDS copies the database locally to the Notebook, then synchronizes changed data back up to the network.

"The concept behind ADO is to take the simplicity of RDO, but simplify it more," said Mendlen. "[RDO] wasn't as simple as it could be. There were too many objects to deal with and understand."

According to Mendlen, the ADO model consists of seven objects. These include:

• Command Object -- holds the SQL query.
• Connection Object -- information associated with connecting to a database. The Connection String has all of the data, while Open Connection makes the connection. There is also built-in support for transaction management.Error Objects -- anything that is broken is included here.
• Field or Collection Object -- handles the collection of fields; for example, the name of an individual field and the value of that field.
• Parameter Object -- used to pass parameters.
• Record Set Object -- handles the cursors.
• Property Object -- where you can see relationships, find out the length of a column and so on.

Mendlen admits that overhead was a problem in the initial release of ADO. "There will always be some overhead, but we worked to make it as small as possible in the new version," he said, adding that while ADO is an added layer for OLE DB data access the productivity gain and ease- of-use factors far outweigh any drawbacks.

Another problem may be the difficulty of actually getting into ADO from other data access mechanisms, such as ADIS, VBSQL and DAO. "It won't be as turnkey as it is in going from RDO, for example," said Mendlen. "It will require some work."

Microsoft has been working with partners, including Rockville, Md.-based Intersolv Inc., to help deliver on its Universal Data Access strategy. Intersolv has been building APIs for many years, and worked closely with Microsoft in developing ODBC and promoting SQL standards. Intersolv is now leveraging that ODBC and OLE DB experience to extend ODBC to provide seamless access to non-relational data.

"More than half the data in the world is not in Sybase, Oracle or DB2," said Brian Reed, director of product management for DataDirect at Intersolv. "There is a lot of data in Excel, on the desktop and so on," he added.

ADO fits into Intersolv's DataDirect Connect products, with OLE DB connectivity; and its DataLink SQL Link client/ server middleware products for large servers. "Both of these products are at Version 2.0, and both support the current version of OLE DB," said Reed.

Intersolv has also been working with COM technology for a long time. According to Reed, COM is not the easiest technology to learn or work with. "OLE DB with ADO is the way to do data access in COM environments," he said. What does this mean for cross-platform environments? According to Reed, organizations will be able to leverage OLE DB to get to all types of data from any Windows platform. "Many organizations must run Unix, too,
so they are cross-platform. We needed to make COM real for Unix, and OLE DB available across platforms," he said.

An advantage in using OLE DB is the component assembly. "If you go with pure COM components, we can provide data access components to third parties looking to go from proprietary data stores to more open OLE DB interface environments. You can mix and match components," said Reed.

For example, an organization can have interoperable components for SQL and Vsam. "This is where the world is going," said Reed. "More and more organizations are not building from scratch today; they want components."

When Visual Studio shipped in September, there was real interest in OLE DB from Intersolv's customer base. "Now, SQL Server 7 is generating interest, and Office 2000 shipped OLE DB enabled. We're seeing a huge burst of interest. Many customers want detailed information on OLE DB," Reed added. "We're at the forefront of seeing OLE DB and ADO explode onto the scene."

ADO is, however, still in the early adopter stage. "People are kicking it around, learning it," said Reed. "Issues seem to be more with how COM works. We're hearing good things about ADO, like [its] power and ease of use."

Platform interoperability

ISG International Software Group Ltd., Burlington, Mass., also developed its ISG Navigator multitiered, enterprise-wide database middleware solution to conform to the OLE DB specification. The product is component-based, and all APIs are OLE DB-based. Because ISG Navigator works directly with Microsoft Visual Studio, it can be used to generate Web applications that need to access an application. Customers are able to dial into a Web site and access the back-end system using ISG Navigator.

"We chose OLE DB and ADO because the specification itself was broad enough to cover both relational and non-relational data," said Steven Fisch, ISG president. Because ISG Navigator uses a single source approach to data no additional coding is required. "All the data sources appear the same," said Fisch. "This reduces the overall cost of ownership for companies."

According to Fisch, developers and end users are able to connect to ISG Navigator using a single interface to combine data platforms and data types. ISG Navigator helps developers build applications that integrate distributed, multiplatform data stores. The product also brings to the table the ability to run across Alpha and Intel NT platforms, as well as OpenVMS, Unix and Tandem.

ISG Navigator 1.6, the firm's newest version, is plug-compatible with ADO 2.0. This lets ISG Navigator provide a range of data access and platform interoperability support from a single interface. For example, Islandia, N.Y.-based Computer Associates (CA) International Inc. even opted to use the ISG Developers Kit to create its OLE DB Provider for CA Jasmine product.

Because many people have business objects they need to connect to, ISG lets application code be wrapped as an OLE DB Provider and go back to access data. ISG Navigator actually encapsulates that business rule as an OLE DB Provider.

Fisch has seen increased interest in ADO the last nine months. "A year ago, it was new to a lot of people. But we're seeing them get into it. On our own Web site, we see some 150 downloads a day. Fifty of those downloads are based on ADO interest from MSDN [Microsoft Developer Network]."

While Fisch has seen the many benefits of ADO, he has not seen much in the way of challenges. "When Microsoft released ADO 1.0 and then [Version] 1.5, they were using a big chunk of code and people did complain about overhead in terms of footprint and performance," he said. "ADO Version 2.0, we find, is as fast as writing C code, and it saves weeks of writing development code."

Early adopters

Turner Broadcasting Sales, Atlanta, writes sales applications including proposals, inventory control, order entry and stewardship applications that are used in the advertising side of the company. According to Matthew Drooker, the company's director of technology implementation, Turner Broadcasting Sales had been using Microsoft DAO and RDO technologies since 1992. When ADO was initially released, said Drooker, the firm chose to stay with RDO because it supported his need to handle disconnected record sets.

"The stewardship applications are very intensive, because they do much culling of data," he said. "They are used to take out a spot that ran. If we guarantee how many people will watch a commercial, for example, and it's not [true], we have reconciling to make up the spot."

Specifically, what Drooker required was the ability to pass record sets intrinsically between machine boundaries, or tiers. When Microsoft began to change the way to get at data, ADO took on the disconnected record set ability as well. "We then started to migrate from RDO to ADO, and used disconnected record sets to effectively, and easily, marshal the data in our tiers," said Drooker.

"We could take a record set, get data for the tables and pass it as a parameter without having to write code," he explained. "When we did three-tier with RDO, we had to disassemble and then rebuild it into a manageable component."

This new ability of ADO helped to shrink down a bit of code. Prior to that, Drooker was dealing with a lot of overhead, especially when working with large numbers of records. ADO has helped a lot in storing and moving data around, he said.

Today, Turner Broadcasting Sales is using SQL Server and Microsoft Access on the same local machine. Because it is all relational data, ADO's ability to access non-relational data has not been of significance to the company thus far. Drooker noted that the company does use Microsoft Exchange, and that perhaps several months out the non-relational aspect may be a consideration. Support for the Internet and/or JDBC has also not been a factor. "Our applications are rich in GUI and functionality, and they need a lot of state," said Drooker. "The Internet model doesn't fit for us."

Drooker is pleased with ADO's capabilities, and has had only one problem. "We had to be sure ADO 2.0 on all our clients could do binds before we installed it on the servers," he said. "We had some compatibility problems between Versions 2.0 and 1.5 on the client."

On the COM side, Drooker has found everything "under the covers" with ADO. "We're all COM here," he said. "With ADO, if COM jumps through hoops, I don't have to. It's very similar to OLE DB; ADO handles all of that.

"We're very happy with ADO so far," he added. "It makes sense to developers, and it cleaned up development."

On the radar screen

VIE Systems, Lyndhurst, N.J., provides the Copernicus messaging middleware product for integrating disparate applications. "Copernicus is a mature product, built five years ago," said Kevin Lupowitz, vice president of product development. "It was built without presumptions of standards, tools and applications. We built it on our own abstraction for accessing databases, then embedded SQL to get to SQL databases."

However, the company has made the decision not to go with ADO right now. "This decision was made for a number of reasons," explained Lupowitz. For example, he said, "we do most of our builds on Unix systems. ADO is a Microsoft product and more NT-centric."

VIE Systems' "initial feedback of ADO was that it was well thought out and definitely has a place in the market," said Lupowitz. "Because Microsoft is strongly backing ADO there is a good chance that down the line we will look to adopt it. It will replace DAO."

In the meantime, if a customer wanted ADO, according to Lupowitz, the company could easily bolt it in. "We haven't had any demand for it yet," he said. "But we are starting to work with some new customers that are building new applications, and they will want their application integration tool to support ADO."

Some of ADO's benefits, as cited by Lupowitz, include having one interface to support instead of a lot of APIs. "With one code base you can support all databases," he said. "It also takes away the complexity of coding so, for example, you don't have to have an Oracle expert, a Sybase expert and so on." For now, ADO remains on the company's radar screen.

ADO vs. DAO

Video Software, Emeryville, Calif., provides ADO and OLE DB components to Microsoft Visual Studio programmers. The company's VideoSoft VSFlex Grid Pro product integrates with any Visual Studio application and provides developers with a turnkey way for users to access all forms of stored data. It also provides them with a way to customize the layout of the data.

"We do components for other developers," said Gustavo Eydelsteyn, president of Video Software. "ADO is a simplified object model, and it is familiar to Visual Studio developers. But it has a broader reach than its predecessor, DAO, and uses one syntax."

According to Eydelsteyn, ODBC provided access to only certain databases. "With ADO, we can have non-traditional data sources; for example, Lotus Notes and Outlook. With ADO, we can have a universal data connection model, but we can do it all with the same model. This was
really needed," he said.

The most-requested item from Video Software's customer base was the ability to have disconnected record sets. "For example," said Eydelsteyn, "laptop and portable computer users can disconnect, and later work with it on the mainframe. This is a big plus for end users. Now programmers can add that functionality," he said.

"We can connect to any data provider of ADO, and show the data grid mode with our tools," said Eydelsteyn. The firm also licenses its FlexGrid technology to Micro- soft, which has added the product to the Visual Studio box as Microsoft FlexGrid.

Benefits outweigh problems

Observers agree that the benefits of ADO outweigh the few problem areas, such as overhead and binding issues. And Microsoft's overall goal with ADO -- to completely isolate developers from the underlying OLE DB and ODBC technologies -- has been eagerly embraced by developers.

"ADO is a revolution, a way to get to different types of data," said Intersolv's Reed. "Developers are happy to have one universal way to get to data without having to learn all [of the] different APIs." The end result, said Reed, is that it is easier to train staff and leverage skill sets.

Microsoft's Mendlen expects that more and more developers will begin to work with the latest version of ADO, now that Visual Studio has brought the technology to the forefront. "With ADO 2.0, which was released with Visual Studio 6.0, we turned the volume up to high," said Mendlen. "We have good, stable code now."