Bill Vaughn
Microsoft Corporation
March 19, 1997
Today, over 80 percent of the applications running on a Microsoft® Windows® platform access data. More and more of these applications are used where the client/server architecture is not only recommended, but is a requirement. Unfortunately, most of these applications fail to succeed for a variety of reasons, including poor planning, design, and implementation. Here, we examine the most common mistakes, and discuss the benefits of using the Active Platform. Developers use the Active Platform so that client/server applications work seamlessly over the Internet, an intranet, or corporate network.
There are many answers about what differentiates client/server architecture from some other design. There is no single correct answer, but generally, an accepted definition describes a client application as the user interface to an intelligent database engine—the server. Well-designed client applications do not hard code details of how or where date is physically stored, fetched, and managed, nor do they perform low-level data manipulation. Instead, they communicate their data needs at a more abstract level, the server performs the bulk of the processing, and the result set isn’t raw data but rather an intelligent answer.
Generally, a client/server application has these characteristics:
Exactly how this design is implemented is up to the systems architect. The design can be scaled into a single-tiered, two-, three-, or n-tiered design, or be implemented on the intranet, Internet, or on a local server.
With the advent of the Active Platform and its associated technologies, browser-based applications can also fall into the client/server category. The leading browsers from Microsoft and Netscape can host applications and packaged controls, and can easily act as a host for client/server applications. While these controls can be written in Java script, also called Java applets, they can also be written in Visual Basic® or Visual C++® by creating ActiveX™ Components.
Strictly speaking, the Web is a global, non-centralized network of computers operating with TCP/IP, HTTP, and HTML as a base set of protocols. However, removing the global aspect, Web technologies can also apply to local networks. Corporate intranets use Web technologies, meaning they can be non-centralized networks based on TCP/IP, HTTP, and HTML protocols. Computer applications exchange information on corporate intranets with the same core set of protocols as those used over the Web.
From one perspective, Web technologies simply offer another arena for development efforts. When a developer deploys ActiveX Components in conjunction with Web technologies, for example, the developer can go about it differently—incorporating HTML and Visual Basic Script code, providing security features, and so forth. But even with Web technology the developer still calls methods, sets properties, and handles events. In this way, all of the development team's knowledge about using Microsoft visual tools can be carried into the Web arena.
From another perspective, applying Web technologies enables an organization to extend development efforts in ways not otherwise possible. For example, benefits of blending ActiveX Components with Internet technology include:
When choosing architecture for a data retrieval application, the application architect is faced with a number of issues that really guide or focus the development process. For example, if many people are expected to use the application simultaneously, the choices of which data access technique to use are narrowed. If the application is planned to work over a WAN or the Internet, choices are further reduced. However, if support is only required for a dozen or so users, developers can chose from a broader set of options.
To start with, consider these goals as guidelines for any client/server development project. As the application is designed, consider that it should be easy to:
These goals seem simple enough, but designing a client/server application while considering the entire set of customer, developer, and systems constraints can be daunting.
On the other hand, because we are not designing a single application, but an entire system of company-wide applications to be used over the next ten years, the time invested in design can easily result in a more scalable, maintainable, deployable, and more usable system of applications. Both users and managers will thank the client/server architect for it.
Many application designs are flawed before the first line of code is written—although admittedly, developers working in the trenches can contribute to the fallibility of a design. With apologies to popular Top Ten List designers, take note of these top ten reasons for systems—especially client/server systems—not meeting expectations:
Sometimes we hear complaints that Microsoft Visual Basic or Microsoft Access is seemingly incapable of building a cursor against a giant table. The real problem was that building a cursor against a giant table was an inappropriate approach. For the most part, client/server applications that support dozens of dozens of users don't build Select * cursors on base tables—they use stored procedures, views, or server-side intelligent objects to return information—not rows.
In today's world of 500K e-mail messages broadcast to everyone announcing the birth of the new group administrator's twins, network bandwidth can be already taxed to the limit. Scalability of any application depends on the quantity of resources consumed by each individual application. This includes workstation resources, but also must include network packets sent and received, round trips to the server, and resources on the server. If a single application creates a 40MB TempDB sorted result set, how is the system going to support 50 or 500 instances of the same application—each consuming server resources like a shark in a feeding frenzy.
By building stored procedures or server-side ActiveX Components to gate access to the data, a developer can prevent the sharks from slithering through the aquarium nibbling at the trophy specimens. This design also offers a way to implement business rules on the server and simplify application design and dissemination. Since security procedures can change without recompiling applications, significant security enhancements can be made without changing a single installed application.
Sometimes, the decision to buy or make has more to do with people skills than technical merits. For example, for a team of COBOL programmers faced with implementing a PC solution, IS management can either retrain the staff and start from scratch or buy an outside solution leaving the team to shuffle 80 column cards. Neither is particularly easy to do. Companies that don't make these tough decisions eventually die or are absorbed.
ActiveX is a set of technologies that enables software components to interact with one another in a networked environment, regardless of the language in which they were created. ActiveX is built on the open Component Object Model (COM) and takes advantage of more than five years of application investment. Integrating ActiveX Components into a design can make what was once a nightmare an easily understood and workable alternative.
Microsoft ActiveX Scripting is the process of managing ActiveX Components with a scripting language, which through its interpreter enhances HTML to support a limited subset of the Visual Basic Scripting language. It makes the language dynamic by adding the ability to invoke ActiveX Components running on the server as DLLs. A growing set of ActiveX Server Components have been defined to do many useful tasks, including handling user preferences, user authentication, database access, and conducting full-text searches.
An ActiveX Server Page is built as an Internet Server Application Program Interface (ISAPI) application that runs on top of Microsoft Internet Information Server™ (IIS), or on a peer Web server relative of IIS. You can review a diagram of this relationship in Figure 1.
Figure 1. Infrastructure of an ActiveX Server Page.
Client requests are made using the HTTP protocol, and are dispatched to the ActiveX Server Page run-time code through ISAPI. The run-time module is coded to handle processing template files and operations such as callouts to ActiveX Components to create the resulting HTTP response—typically an HTML page. Then the response is passed back to the client. ActiveX technologies expand the power of client/server applications in a number of ways—some of which are implemented already with Visual C++ and for the first time in Visual Basic using Visual Basic version 5.0.
“Thin client” has become a fashionable term to describe HTML-based Web applications that leverage browser or downloaded functionality to build the interface for client applications. Actually, building thinner clients has been a goal of client/server developers for a while, as they code systems that implement business logic in server-side objects, stored procedures, and triggers—which are event-driven stored-procedures.
Use of server-side components is easier now when using the Microsoft Transaction Server (MTS) and using Visual Basic to build ActiveX Components that the MTS can manage. Previously, the skills of C++ programmers were comprehensive enough to create these advanced components. MTS can be used to manage multiple clients' use of shared headless ActiveX Components by handling thread pooling and security issues as well as permitting remote management of complex multi-operation transactions.
Historically, when a developer wanted inheritable control and forms, the developer employed complex languages such as C++. With Microsoft visual tools, the choice can be made to use Visual C++ or Visual Basic 5.0. This means a developer can create controls using built-in, OCX-based, or third party Visual Basic controls, and assemble additional controls built from these composite controls. When a member control is modified, the composite controls that include it are affected by the change.
A developer can also build entire forms using this same technology, to more easily create patterns for implementing specific business solutions. For example, it would be easy to implement a common name/address control form that could be used anywhere a control could be used. Visual Basic version 5.0 includes a powerful set of wizards designed to help extrude properties and implement methods as a developer constructs these controls and codes the property and method handlers. While a programmer might find that creating a grid control is more trouble than it is worth, there is nothing to prevent use of the existing built-in grid control to build a customized adaptation that meets specific needs.
New data binding technology makes it easier than ever to expose the composite control's properties to a data control. A developer can now bind any number of properties of any control to a data control, including custom composite controls. A developer can also implement custom data management routines for the control to avoid dependence on data source controls.
By using Remote Data Objects (RDO) and Visual Basic version 5.0, a developer can implement unique events and manage asynchronous operations and these user-developed custom controls can be coded into very sophisticated stand-alone database interfaces.
No, Visual Basic does not create complex hierarchies of controls when a developer builds custom controls. Each control component is managed independently, which makes deployment and integration easier and results in higher performance.
Once the architect addresses the design of the application, the next step is to focus on the languages and/or tools to implement the design. It takes more than a solid language to develop production-quality applications in today's amalgamation of components, connectivity, and development options. But without a reliable, efficient, and flexible set of development tools, this task can become daunting to impossible.
The development tool chosen to meet this challenge must coordinate its operations and work in harmony with many aspects of the whole system. Not only must the language work with third-party components and libraries, but it must be capable of developing and leveraging user-written controls and libraries. The language must be useful for developing fast, scalable applications, which can be used as basic building blocks for large and small systems.
Choosing the right tools for the job is an essential component in implementing any successful client/server application. Our experience has shown that the tool—or combination of tools—that delivers the product on time, on target, and on budget—is more likely to succeed than a tool that might be more sophisticated in its feature set, but does not implement the business solution within the business-case constraints. That is, if the new application works, is fast and feature rich, but two years late and over budget, it won't be too useful—and may doom the application from its inception. As not all client/server applications are successful, really, mitigation of risk is clearly a high-priority consideration when making a choice of development tools. The real question might be whether or not the granularity of control afforded by C++ is necessary, or the ease of use afforded by Visual Basic is preferred. Again, there is often a need for both—each to be used in the right places.
Another problem we face is that application consumers' needs and demands change faster than we can implement solutions. The key here is to build solutions that are flexible enough to accommodate constantly changing application requirements. Much of this can be done by building custom tools and components that themselves can be used to build customized solutions—leveraging what works, adapting existing tools, and building new components for what doesn't work. Both the rapid application development tools and Visual C++ can play important roles in creating and deploying developer-defined components and tools.
Basically, there are two approaches to building client/server applications using today's technology: 1) Rapid application development (RAD) tools such as Visual Basic and Visual FoxPro™ and 2) more traditional edit, compile, link, and test tools such as Visual C++. While Visual C++ provides some of the RAD features of Visual Basic, it is not suited for RAD development because of its steeper learning curve and longer development cycle. Today, the bulk of client/server applications are being built using RAD tools and C++. That is, when it is necessary to streamline a section of the logic, it sometimes makes sense to implement a lower-level C++ solution for selected procedures.
This approach can yield optimum performance and can better leverage the experience and skills of both the RAD and C++ teams. However, since the bulk of the client/server application is developed using RAD tools, here we focus on using Visual Basic—and then, how to best leverage C++ when necessary.
Developers familiar with the introduction of Visual Basic version 4.0 will agree that Visual Basic 5.0 Enterprise Edition is a more mature product. Besides NSTL-certified performance improvements, version 5.0 includes new language features, additional enterprise support applications, and more complete integration with ODBC and Microsoft SQL Server™ platforms.
Whatever implementation strategy is chosen for the client/server application, security must be considered. The loudest Internet advocates admit that the most troubling problem about this public network is security. Until the application can protect confidential business information and confidential data belonging to the organization or its customers, an application has no business sending such data over an unsecured wire. To some critics, it is the equivalent of displaying the information on the reader board in Times Square in New York City or hearing it from the pulpit in the Notre Dame cathedral in Paris.
Using Visual C++ or Visual Basic, a developer can code applications, in the form of executable files, DLLs, and ActiveX Components, and execute them virtually anywhere in the system. They can execute anywhere from local in-process modules to remote servers on the other side of the planet. SQL Server can execute them through extended stored procedures. These applications, controls, and libraries can become powerful extensions to the systems software—part of sophisticated n-tiered data access systems.
To developers and their managers this means:
Typically, single-tiered applications access the base tables on the server and, in many cases, treat the intelligent server as if it were a common file-based database server. This means a typical client application query fetches rows directly from the tables using hard-coded queries and references to the underlying data structures. In addition, single-tiered applications often depend on updateable cursors that prohibit the use of stored procedures.
Applications are designed this way because of being ported from ISAM architectures where it is common to create a table object against an entire table, choose an index, and use the Seek method to position to specific rows.
This approach to data access and retrieval presents a number of problems:
Applications designed this way don't leverage existing data access code, because they often evolve on an application basis instead of on a more generic level. This means that work for teams coding larger projects is made especially difficult. Projects must be serialized and constructed in a strict sequence to prevent changes at one level from affecting subsequent coding efforts. When evolved with a generic-level design, the code can be used in any application that accesses the same database.
Over the years, many successful database applications have been built using two-tiered architecture. Experience has taught us that while this design is easy to setup and implement—and is widely supported by easy-to-use database management tools—its inherent limitations get in the way of performance, scalability, and team development. When planning to build systems that must support more than a dozen or so users, or whose complexity calls for a larger staff of developers and analysts, a systems architect should consider designs that are more sophisticated.
A first step that most larger database management systems sites have already taken involves migrating much of the database-specific physical access logic out of the client application and placing it on the server where it belongs. When made available on a server, the logic can be more easily implemented, tuned, and reconfigured as needed. Most sophisticated remote servers, such as Microsoft SQL Server, are capable of managing and executing many stored procedures at once—each with their own thread.
Using this design, thin client applications can be built when this code is repositioned on the server. These client applications must be designed in a fundamentally different way. To begin with, for fetching and updating data thin clients should be written to access logical objects or remote procedures instead of physical objects, such as tables. This is not a new concept. SQL Server and other intelligent databases already use and promote this architecture. However, the new RAD tools are better equipped to directly address these remote business objects or stored procedures.
A two-tiered application typically exposes developer-written stored procedures to return rows of data values based on criteria received from client-side invocations. The procedures are also designed to limit the scope of the queries and protect the underlying data from unauthorized access or undisciplined access that can severely limit performance and throughput. For the most part, these stored procedures are designed as business objects. For example, AddCustomer, RemoveCustomer, FindCustomer, and ChangeCustomer procedures perform whatever low-level operations are necessary to perform these logical business operations.
Another variation on two-tiered applications exposes ActiveX Components that can be executed through extended stored procedure calls directly from stored procedure code. These components also can be intelligent business objects that use properties instead of parameters and methods to perform specific business tasks.
For example, a developer can implement a Customer object with a set of properties used to expose the various attributes of a specific customer. When it comes time to add, change, delete, or find customers, one or more methods can be defined for each of these operations. By using this approach, the client application simply knows about a Customer object and need not be concerned about how the data is stored, retrieved, or protected.
As business needs evolve (for example, credit cut-off comes when a customer is 60, not 90, days in arrears), changes to the logical business objects do not affect the base design of the client application. This means that even though business rules change, the client applications need not be rebuilt and redeployed. When changes occur, simply changing them on the server automatically implements the changes system-wide—regardless of the number of applications that use the objects or procedures.
Building applications out of components has tremendous appeal and was one early promise of object-oriented computing. It is particularly attractive for server applications because it provides a natural way to encapsulate business functions. However, engineering applications from components was harder than it first appeared. A fundamental weakness of early object systems was the lack of a common framework that developers could employ to integrate objects created by different parties into one application, either in the same process or across processes. The Component Object Model (COM) addressed this weakness.
However, simply having a common component object model is not sufficient for building server applications from components; the components must also use a common server framework. Developers who build their own server frameworks have limited opportunities to use components developed by others.
When a design spans multiple servers, this calls for expanding three-tier design concepts to additional levels. This is where the Microsoft Transaction Server (MTS) plays a critical support role—performing many complex operations that are, in many cases, simply impossible to perform without the extra help of the Transaction Server. MTM takes up where the Distributed Transaction Manager in SQL Server 6.5 leaves off. It not only deals with transaction issues between servers, but also manages thread pooling and object brokering.
It is critical that business systems accurately maintain the state of the business. For example, an online bookstore must reliably track orders or major revenue losses can result. Current orders could be lost or dissatisfied customers could take their business elsewhere.
Maintaining the integrity of business systems has never been easy, especially after failures. Ironically, although computers are becoming increasingly more reliable, systems as a whole are becoming more unreliable. Failures are common with systems composed of hundreds to thousands to millions of desktop computers connected by way of an intranet, and the Internet to tens, or hundreds, or potentially hundreds of thousands of server computers.
The problem is compounded by the demand for distributed applications. Business transactions, such as reserving a hotel room, increasingly involve multiple servers. For example, credit must be verified, confirmation letters mailed, room reservations managed, and customers billed. Updates must occur in multiple databases on multiple servers. Developers of distributed applications must anticipate that some parts of the application may continue to run even after other parts have failed. These failure scenarios are orders of magnitude more complicated than those of monolithic applications, which fail—when they do—completely.
Business applications are frequently required to coordinate multiple pieces of work as part of a single business transaction. For example, before booking a hotel room, an online travel agent wants to capture appropriate billing information and, conversely, would not bill a customer without booking a hotel room. Coordinating the pieces of work so that all of it happens or none of it happens is difficult without special support from the system. Guaranteeing atomic updates, even in the face of failures, is hard. It is especially difficult when an application is distributed across multiple databases or systems. Using multiple components, which by design hide their implementations, can compound the complexity.
Applications must also deliver consistent behavior when multiple clients access a component. Concurrent orders for the same book title should not result in sending two copies of the book to a customer, unless that's what the customer ordered. Unless the application is properly written, race conditions will eventually cause inconsistencies. These problems are difficult and expensive to resolve, and are more likely to occur as volume and concurrency increase. Again, using components compounds the problem.
Microsoft Transaction Server integrates transactions with component-based programming so a developer can code robust, distributed, component-based applications.
To get a better picture of what a business object really is and how a set of these objects could be implemented, lets look at a simple implementation in more detail. First, consider that the real benefit to business objects is the ability to access them from multiple clients. That is, once a logical business object is created a human interface front-end or a telephone switch computer can access it with equal ease. For the development team, this means that once the specification for the business object is published, developers from all over the company can code applications that leverage its logic as a portal to the database. It is also a carefully orchestrated portal so that business partner developers outside the company can also code applications that use it.
As an example, consider a simple hotel management system. In this case, we have implemented a number of business objects that are accessed by the front desk using the Desk Clerk application. This application collects data, verifies reservation information about arriving guests, and prints a bill when guests check out. The individual fields are pre-edited by the application by querying a field's description class stored on the server and applying edits as masks to incoming data.
The hotel phone operators use another interface to query the residents and have no access to more sensitive information. When housekeeping finishes making up a room, they too use the phone to call a special number to record the new room state—clean and ready or still out of order because of remodeling. When a guest places a telephone call, the same system, coupled with the hotel's telephone switch, records the number dialed and duration of the connection, and passes data sent from the telephone service provider to be recorded on the guest's folio. You can see the relationship among these events in Figure 2.
Figure 2. The Desk Clerk application.
Each of these subsystems use the same business objects to access the base tables in the database—but never open or access them. They are only opened by business objects and they use stored procedures to access the data. Because of this design, if the base tables change or some subtle—or not so subtle—change is made to the way the database works, it has no affect on the applications. Sure, the business objects affected will need to be adapted, but this is a straightforward task.
When it comes time to upgrade this system to access external systems or add more complex multi-user operations, use of the Microsoft Transaction Server will probably be called for. In the future, when the hotel wants to post room availability on the Web, adding a Web portal should not be a problem—as we discuss below.
Multi-tiered applications can be complicated to build, maintain, and enhance. It is probably not possible to reap many rewards from more sophisticated designs until the development team can leverage the business objects they create.
To make managing objects easier, Microsoft, in conjunction with Texas Instruments, developed a sophisticated object repository that can cut application development time because it reduces the need to create code from scratch for each new application. A repository is a database for storing software components or pre-constructed segments of code that perform specific operations. The repository not only keeps track of the code, but the documentation associated with it, so a developer can easily see if there is a suitable component to use in the newest design.
The Microsoft Repository stores components, functional documentation, and Web-related documentation so a development team has access to a gallery of organization-wide components. The repository is available while using a variety of development, modeling, management, and Web-specific tools. Each time a new component is created it is banked in the repository. When it comes time to build a new application, a developer can browse the repository and locate the appropriate pre-built routines to do the job. When an object needs adjustment to fit the new task, the new version is posted to the repository with the appropriate documentation.
You can read more about Microsoft Repository at http://www.microsoft.com/repository/.
Microsoft Transaction Server (MTS) is an essential element of the Microsoft Internet and intranet application strategy. MTS is based on proven transaction-processing methods, but its importance transcends the domain of transaction processing monitors. It defines a general-purpose programming model for distributed component-based server applications.
Microsoft Transaction Server is specifically designed to support server applications as they scale over a wide range of user volume, from small single-user systems to high-volume Internet servers. It provides the robustness and integrity traditionally associated only with high-end transaction processing systems.
The Microsoft Transaction Server application model is based on the Component Object Model (COM) and has these benefits:
Microsoft Transaction Server extends COM to provide a general server application framework. In addition to the inherent COM features mentioned in the previous list, Microsoft Transaction Server:
Read more about Microsoft Transaction Server at http://www.microsoft.com/Transaction/.
Another important consideration for three-tiered applications is the use of object modeling software. A few vendors, listed below, offer such products. Modeling tools have different functions and goals. Some, like Rational Rose, offer a pure object-oriented approach, while others may offer different approaches, including DDL generation or round-trip engineering. They include code generation, including C++ and Visual Basic, as well as reverse engineering.
The advantage of using object modeling software is this: developers working as a team can set requirements, problem domains, and application architectures. Many of these tools integrate with the Microsoft Repository as well, or the vendors have announced plans to add that support.
In this list, you'll find the name of the software vendor, the name(s) of the product(s), and the URL for their Web site.
TeamWork and ObjectWork
http://www.cayennesoft.com/products/datasheets/po-teamwork.html
Paradigm Plus
System Architect
Rational ROSE
Select Enterprise
Composer
Virtually all client/server applications deal with existing data one way or another. One story goes this way. When working for EDS many years ago, a fellow we know created a new IBM 5110 general ledger application that was basically a database of accounting data that replaced an existing paper system. When the application was delivered to the customer, they were happy but called later that day. It seems someone actually laid their old ledgers on top of the system—expecting that the computer would be able to read in their old data.
While most customers are somewhat more computer-aware than this, odds are any new application will still deal with legacy data. Porting this data to SQL Server or to a Jet database may be an unavailable luxury; the customer is invariably using live applications that cannot be switched off while they are being replaced. In this situation, here are a number of options:
The following sections discuss how to access data not stored in the central database, or external data.
When counting the lines of code in a typical client/server application, one can find that a relatively low percentage is directly related to data-access operations. However, the data access programming model chosen and the way it is used has significant impact on the scalability, performance, footprint, and user acceptance of the application. Even if the "right" data access programming interface is chosen, if the system design does not accommodate its features or it does not correctly manage the DBMS data, again, failure to create a successful application is likely.
Now that data access code can be implemented in headless components that reside on the server, choice of an efficient data access model is even more important. This is especially true when it is expected that the code will be cloned repeatedly as the transaction manager launches additional copies of the application.
The Data Access Objects (DAO) were initially created to provide a comprehensive programming interface for all applications—regardless of how or where data was stored. Over time, DAO became increasingly accepted as a native relational .mdb database and ISAM interface. DAO was implemented using the Microsoft Jet database engine—which is a relational data engine, but oriented toward ISAM data access, capable of accessing data from a variety of sources, as we have already discussed.
The strength of Jet is in its ability to join data from a variety of data sources and permit updates against this disparate data—no small feat. When an application must leverage existing ISAM data, one of the most powerful tools available is the Microsoft Jet database engine. In addition to its native .mdb database format, ISAM data from Microsoft FoxPro, Btrieve, Paradox, or any xBase format is also easily managed by Jet. Because the Jet database engine is exposed by way of DAO, writing code to access both native Jet databases and external ODBC data sources is especially easy. And Jet’s ability to access ODBC data sources also facilitates writing custom data import/export utilities to meet specific customer needs.
When data must be filtered and modified, DAO can be useful as the Jet query processor. The result set builder can do most of the work so a developer doesn't have to worry about row-by-row data management. The downside of this approach is that it requires a procedure be coded in Visual Basic or Visual C++ to build the result sets and manage the data.
Jet brings a number of important features to database designs:
For the most part, Jet is designed to work best with tables. That is, if a design calls for direct table manipulation, Jet is the ticket. However, if a design expects to use stored procedures or remote business objects, Jet might not be the best choice. While a developer can execute stored procedures from DAO, Jet does not handle complex result sets particularly well. That's where DAO ODBCDirect comes in. By using familiar DAO objects, a developer can throw a switch and activate the ODBCDirect mode to access stored procedures and the most complex result sets she can create.
If the application consumer does not want to hand-code a data export/import utility, or only intends to perform the operation once, it makes sense to use one of the interactive DBMS tools such as Microsoft Access or Microsoft Visual FoxPro. These tools are designed to make data transfer easy and foolproof using a graphical user interface. Built in wizards can make management of duplicate result sets or direct exportation of data tables to Microsoft SQL Server—using the Access upsizing wizard—as easy as point-and-click technology can make it.
Note that it is no longer necessary to use Microsoft Access to create queries to access database tables—or stored procedures. Microsoft Database Tools included with the Visual Basic version 5.0, Enterprise Edition, are designed for this specific purpose. This set of applications is designed to help a non-programmer create queries interactively and manage database schema.
If it is necessary to move data to and from delimited ASCII files when working with SQL Server, users will find that the Bulk Copy Program (BCP) utility delivered with SQL Server is often the easiest solution. BCP is provided in either executable form that can be run from a Windows 95 or Windows NT® command line, or through the use of the BCP API as exposed by VB SQL (in Visual Basic) or the DB-Library (in Visual C++). BCP can be configured to deal with a number of file and record formatting issues. SQL Server is designed to deal with the special uploading requirements of BCP so that data transfers can be made quickly. In many production systems, BCP is used in periodic data transfer schemes that upload and download data to base tables on a regular basis—often using interfaces that run completely unattended.
Corporations all over the world depend on legacy data stored in mainframe systems, and this data must be accessible from company-wide departmental and other external systems. In addition, many corporations are using PC and mini-based data concentrators to reduce mainframe loads, making them more productive for more client applications.
While many applications written for a mainframe are written in high-level languages, such as COBOL or RPG, these applications can usually be ported to Visual Basic with all of the existing business logic intact. There are a number of ways to access this data and the years invested in these applications. This is becoming an increasingly serious issue as software vendors move their support to PC-based client platforms. To support the companies converting to this new technology, a number of companies have implemented upgrade or legacy data leveraging paths to include:
Remote Data Objects (RDO) was introduced in Visual Basic version 4.0, Enterprise Edition, as a programming model specifically designed to deal with the special needs of client/server developers—especially those accessing SQL Server and Oracle databases. From the beginning it was clear that the ODBC API could deliver the performance required by client/server developers, but it was not particularly well suited for RAD development. The ODBC API required careful management of all aspects of data handling, and if a developer made a mistake, the result was often a general protection fault. When 32-bit platforms are added to the equation, the ODBC API became less attractive.
RDO was designed to act as a thin layer on the ODBC API—and originally it was. While RDO has grown more powerful in version 2.0, it is still substantially smaller and faster than DAO or other alternatives.
What RDO adds is productivity. No, RDO applications do not execute quite as quickly as ODBC API applications do, but they can be written faster, and are easier to maintain and deploy because of their employment of ActiveX technology. Unlike the ODBC API—or any API-based approach—RDO is exposed as a set of classes, methods, and properties. It handles all variable binding and memory management tasks as well as asynchronous polling and event management. RDO is also integrated into the User Connection Designer as well as the T-SQL debugger.
RDO's chief strength is its ability to handle complex result sets—especially those generated by stored procedures (SP). Since SPs form the core of three-tiered client/server architecture, the ability to invoke them, parse the returning rowsets, output parameters, and return values is vitally important.
RDO is also used by the UserConnection Designer to create UserConnection objects, a new feature in Microsoft visual tools. Using an interactive wizard, the UserConnection Designer creates a UserConnection object, a structure that identifies a specific connection, and selected stored procedures exposed by the selected database. In code, executing SPs is as simple as calling RDO methods—because the UserConnection object exposes each stored procedure as a method. Passing parameters to the stored procedure is accomplished through method parameters. What once took about a dozen lines of code to create rdoPreparedStatements, set up the parameters, prepare to set up a connection and open it, and create a result set, can now be done with less than half the code. To a developer that means fewer mistakes, to the manager that means more productivity, to a customer, that means a better, more stable application.
RDO also has added an entirely new cursor library that offers flexibility and speed to Visual Basic version 5.0 data access applications. The Client Batch cursor library has new features so a developer can:
The Client Batch cursor library was designed to provide many performance benefits of server-side cursors, but with the extra flexibility of a local cursor store. This new cursor library means that applications can create and manage local cursors more quickly and with more options than were supported with the ODBC cursor library.
The patience of an end user is a system resource that should not be overlooked. When an end user feels a system is unresponsive, he or she is likely to reset it—or try to—or give up on the application, although the only thing wrong is that the process takes longer than expected. If, however, the user can see something to indicate that the system is still working, the chances of the user abandoning the operation or the application decrease dramatically.
The trick is to use asynchronous mode for all operations that are expected—even remotely expected—to take a long time. This way an application can be written to provide a progress bar (even "guess-timate" progress) or some status indicator that gives a user some idea of how long to wait for a response.
RDO 2.0 now supports more asynchronous operations. In addition, instead of polling RDO, using the StillExecuting property, to see when an operation is complete, RDO supports a group of events that signal the completion and success or failure of each asynchronous operation. For example, a developer can use asynchronous operations to:
One of the most important features built into Windows 95 and Windows NT operating systems is support of true multitasking—meaning that an executable file can continue to use CPU cycles while other operations run, usually in the background. This feature does not necessarily mean that an application behaves differently, only that the end user—or an application—can continue to work while another application performs some operation. This also means that an application should not be forced to make an end user wait for a long time without giving the computer system an opportunity to perform other work, or to cancel the currently running operation.
The programming models discussed here work either synchronously or asynchronously. When an application executes a query, the data interface behaves in one of several ways:
If it takes more than a few seconds to execute a query, use one of the asynchronous techniques that returns control to the application immediately. In this case, the application periodically checks the data interface until the data is ready for processing.
Another aspect of asynchronous operations is reentrancy. Reentrant code is designed to be executed by more than one part of a program at the same time. For example, if code includes DoEvents statements, or if the operating system supports true multitasking, the end user can execute procedures that are already running. In addition, if an application can continue processing while the data access engine is building the result set, an end user can:
One important part of asynchronous operations is background population of result sets. Because the server can hold locks on pages being retrieved in a query, it is best to complete a query as quickly as possible to free these locks. This should only be a problem for non-cursor, or bulk, operations. For server-side cursors, the keyset is either populated in the background by the server or not populated, which is the case with dynamic cursors.
Not all of the Visual Basic data access programming models support all features of asynchronous operations. Often a developer will perform background result set population or polling logic in code.
The Remote Data Object (RDO) events are the first data access events to appear in Visual Basic. Not only do these events signal the end of asynchronous data access operations, but they can also be used to intercept data access operations to ensure that they execute according to plan. These same events are also implemented on the RemoteData control, so it is possible to execute stored procedures both to return rows and to perform updates. Both asynchronous and synchronous queries fire events when queries complete or are about to be executed. By creating procedures for these events, a developer can gain a higher degree of control over an application and prevent lock-up situations that often cause an end user to reboot the system.
Note RDO events are not exposed through ODBCDirect.
The RDO class libraries have also been incorporated into Visual C++. When working in any Microsoft Office application a developer accesses RDO's libraries whenever he or she uses the ODBCDirect mode from DAO. RDO also exposes the underlying ODBC handles so that when it becomes necessary to perform some function not supported by RDO methods or properties, a developer can simply call an ODBC API function using the appropriate handle.
You can review Remote Data Objects in Figure 3.
Figure 3. Remote Data Objects
DAO version 3.5, as provided in Visual Basic version 5.0 and other hosts, expands the flexibility of DAO and breaks the dependence on the Jet database engine when accessing ODBC data sources. While DAO applications are still capable of accessing ODBC data sources just as with previous versions, DAO applications can now opt to use the new ODBCDirect mode that relegates all data access operations to RDO. When an ODBCDirect Workspace is activated, the Jet database engine is not loaded.
ODBCDirect should not be considered as a panacea for applications designed to use ISAM concepts to access relational data. It does not automatically convert single-tiered applications to three-tiered designs. What ODBCDirect does offer is a way for a developer to work in a familiar programming paradigm while accessing remote data sources. ODBCDirect does not expose the table object or the Seek method on the Recordset object. However, it does give a developer access to most of the expanded functionality exposed by RDO. Missing from the list is the ability to trap RDO events and work with stand-alone rdoQuery and rdoConnection objects—the rest is there and usable via DAO objects with which a developer is already familiar.
The ODBC API is the lowest-level data access interface a developer should consider for new application development. Generally, use of the ODBC API should also be limited to adding additional flexibility to RDO—not as a base interface. That is, when a developer needs to manipulate a driver interface or server option that can only be set from the ODBC API, specific ODBC API functions using the ODBC handles generated by RDO objects can be executed. As stated earlier, developing applications directly in the ODBC API is possible, but not recommended. While the resulting application might run faster than its RDO equivalent—but not by much—the development process is more complex and the resulting applications are more difficult to maintain.
When SQL Server was first released, the only way to create client applications was to use the DB-Library API. Over the years Sybase eventually dropped support for DB-Library in favor of their proprietary CT-Lib API interface. While Microsoft has not chosen to abandon DB-Library, it is not planned to be enhanced for subsequent releases. Because of this, DB-Library is not recommended for new applications.
ADO is an important technology for the future of the Microsoft data access strategy. The Microsoft Database API group is currently working on two core APIs—ODBC and OLE DB. ODBC is the recognized industry standard database API for SQL data as supported by all major database and database application vendors. OLE DB represents the future data access API for Microsoft as exposed through Active Data Objects (ADO). ADO allows all data sources, not just full-fledged databases, to expose their data through a common set of interfaces. Application writers can consume information from all these different data sources with a common set of interfaces. As ADO gains momentum, the amount of data available through a common set of interfaces is predicted to grow tremendously.
One distinctive difference exists between OLE DB and the ODBC API. While it is possible to write applications that use the ODBC API from Visual Basic, OLE DB is not intended to be exposed as a programming interface from any language except C++—and then only when writing device drivers for ADO.
The ADO Automation Server provides standard, easy-to-use, efficient programmability to OLE DB. Significant effort has been made to keep the programming model, objects, and concepts very similar to DAO 3.x. ADO consists of three upward-compatible models:
ADO is shipped as part of the ODBC and OLE DB SDKs. Target customers for ADO include Microsoft Access, Microsoft Excel, Microsoft Office, Microsoft Visual Basic, Microsoft Visual C++, Active Data, Microsoft Visual InterDev™, Forms(3), and the OLE DB and ODBC SDKs. The end users of ADO will continue to be primarily developers who use Visual Basic, Visual C++ and the data access SDK, but there will also be significant core groups that only use ADO in the context of their host product, such as Microsoft Access or Microsoft Excel.