Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

Chapter 12
QueryServer

The PeopleWatcher Remote application described in Chapter 10 attaches remote database tables to a local database, allowing the program to treat the tables as if they were present on the local computer. Attaching tables gives an application quick and easy access to remote data, but it has some disadvantages.

First, processing a large number of records may be slow. Suppose a program computes a complicated function involving values contained in the records in a 1 million record database. If the program attaches to the remote table and then examines each record individually, the data for all 1 million records is transmitted across the network from the remote database. The program then examines the records to compute the complicated function. Figure 12.1 shows how the data flows from the remote database to the program.

FIGURE 12.1  Attaching to a remote table requires records to be transmitted across the network.

A much more efficient solution is to use techniques similar to those used by the TimeSync application to create a server on the remote computer. The server examines the 1 million records on the remote computer and then sends only the result over the network. Sending data across a network is relatively slow. By reducing the amount of data sent over the network, the server can greatly improve the performance of the application. It also reduces the overall traffic on the network, making other network applications faster. This solution is shown in Figure 12.2.

A second problem with attaching to remote tables is that there is no centralized control over the operations being performed. That makes changing the operations difficult. Suppose an application produces a list of employee names by querying a remotely attached table. A separate copy of the application runs on each of 100 different computers so every user can see a copy of the list. Now suppose management decides that the list should also include each employee’s telephone number. The application would need to be rewritten and reinstalled on all 100 of the users’ computers. Figure 12.3 shows this situation. Black boxes with white text show computers where the new version would need to be installed.

Once again, a server application on the database computer provides a better solution. The server generates the employee list and sends it to the user computers. If the way in which the list is generated must be changed, only the single server application must be updated. If the user computers display the list in the same way as before, the 100 user programs can be left unchanged. Figure 12.4 shows this new arrangement. The single black box with white text represents the only computer that must be updated.

The QueryServer application uses remote servers to avoid these potential problems while providing a set of predefined reports. It uses servers to minimize network traffic and to provide a centralized location for report definition.

The following section, “Using QueryServer,” explains the application from the user’s point of view.

FIGURE 12.2  A remote server processes database records and transmits only results across the network.

FIGURE 12.3  Updating 100 copies of an application that attaches to a remote database.

The “Key Techniques” section lists the main programming concepts demonstrated by QueryServer. The rest of the chapter describes those concepts in detail.

Using QueryServer

The complete QueryServer system consists of a family of client and server programs working together to provide reporting services. The ReportList client program presents a list of available reports. When the user selects a report, ReportList fetches it from a server and displays it. Figure 12.5 shows ReportList displaying a list of reports.

The Librarian server generates the list of available reports for ReportList to display. Librarian obtains the list by using a simple SQL query on the Reports table in the database Reports.MDB. To change the list of reports available, the only change necessary is to modify the records in this table.

The SQLServer program contains several classes, each of which implements a single report service. When the ReportList client is ready to generate a report, it creates an instance of the appropriate report class. That object uses parameters specified by the user to generate the report.

In the sample application, all of the reports are provided by a single server application, and they all come from the same database Purchase.MDB. They could just as easily be provided by different applications on different computers scattered throughout the network, each using a different database. They could even use other data sources such as text files.

FIGURE 12.4  Updating 100 copies of an application that attaches to a remote server.

Figure 12.6 shows one alternative arrangement of the ReportList, Librarian, and SQLServer applications. In this picture, the report servers lie on two different computers and access three different databases.

FIGURE 12.5  ReportList displaying the available reports.

Key Techniques

The QueryServer system uses the same client/server techniques described in Chapter 11. The new programming methods it uses are demonstrated by its three programs: ReportList, Librarian, and SQLServer. The following list describes these programs in brief. The rest of this chapter describes them in greater detail.

•  Building ReportList. The ReportList client displays a list of available reports. This section explains how ReportList lists the reports, allows the user to pick one, and then generates and displays the report.

•  Building Librarian. The Librarian server creates a list of reports for ReportList to display. This section shows how Librarian creates the list using a database query.

•  Building SQLServer. The SQLServer program provides reporting services. This section describes SQLServer and tells how it generates different reports requested by the user.

FIGURE 12.6  The pieces of QueryServer.

Building ReportList

The ReportList client program is the main user interface for the QueryServer system. It provides a list of reports, as shown in Figure 12.5. When the user selects a report, it uses an object from the SQLServer program to create the corresponding report. Figure 12.7 shows ReportList displaying a report.

The source code for the ReportList program is in the Ch12\RptList subdirectory on the compact disk. This program contains five forms and two .BAS modules. Splash.FRM contains a standard splash screen. SetPos.BAS declares the SetWindowPos API routine used by the splash screen.

Token.BAS contains the GetToken function described in earlier chapters. GetToken plays an important role in many of the system’s client/server interactions. Arguments and return values for many server routines are packaged in delimited strings. For example, three values could be packed into a semi-colon-delimited string, like this:

value1;value2;value3;

The routine that receives this string uses GetToken to separate the values. Using similar techniques, client and server programs can pass any number of values back and forth in a single string. This is particularly useful if the programs cannot know how many values they will need to exchange until run time.

The other forms used by ReportList are described in the following sections. Individually, each is quite simple. Together, they form a powerful application for accessing remote report services.

FIGURE 12.7  ReportList displaying report results.