Visual Basic Expert SolutionsChapter 6Working with ODBCBy James A. Dooley |
In some ways, building a client/server application can be very similar to building a desktop application: most of the user interface development is the same, database design has to be done, and so on. Differences start to crop up when you consider the data access component of the application, the data is physically located on another machine, and, as a result, it will require a different data access strategy than that of a desktop application. The key to developing a successful client/server application is to recognize that there are two cooperative processes, the client and the server, involved in the delivery of a such an application. Accordingly, you design your application to take advantage of both features.
This chapter adopts a hands-on approach to working with ODBC. Where examples are used to demonstrate a point, they are done in the context of moving the now famous Northwind Traders database from MS Access to client/server using Visual Basic and MS SQL Server. In designing our solutions, we will aim to provide a reasonable performance on an overloaded network. We do this because more often than not, this is the type of environment that companies use to take a look at this "client/server stuff."
This chapter will cover the following information:
After completing this chapter, you will be able to design and implement client/server applications using Visual Basic and a Database Management System (DBMS) that supports an ODBC connection.
Broadly speaking, most business applications consist of three layers, as depicted in figure 6.1.
Fig. 6.1 The three application layers.
While most applications consist of these three layers, the architecture used to implement them can vary greatly ranging from a dumb terminal to a complex cooperative processing system. Figure 6.2 has been adopted from work done by the Gartner Group and depicts a wide range of architectures used to implement this three-layered model. In this figure, H represents a host system, and W represents a workstation or PC.
Fig. 6.2 This application architecture depicts a wide range of architectures.
On the left of this diagram, you see what might be described as a typical mainframe application, with the host managing the data, logic, and presentation. On the extreme right, we have a cooperative processing application where the work station is responsible for the presentation and also contains some for the processing logic or business rules. Between these two extremes we have various systems that represent transitions from one to the other. The first, titled UI, recognizes that the power of the workstation can be used to manage the user interface. C/S I is what is commonly termed client/server today. The workstation carries out the presentation and logic, and the back-end, or server, carries out data management. C/S II is almost a reversal of C/S I, whereby the logic and data is located on the server, and the workstation does the presentation and some light processing. What distinguishes C/S II from UI is the light processing being done on the workstation, such as calculating totals.
Among the key issues facing the designer of a client/server application is deciding how to split the processing and how to locate the data. The basic idea is for the Client and the Server to do the work that they are best suited to do. Thus, the client should concentrate on the user interface, whereas the server handles the data integrity, transaction management, and so on. We'll come back to this issue later, in the section entitled "Using Remote data", as we consider how to implement various aspects of the Northwind Traders System.
Over the years, corporations have invested in diverse platforms for strategic, technical, financial, and-dare we say-political reasons. Thus, we find in most cases a combination of mainframes, mid-range, and PC systems are being used to manage the corporation. Each system brings it's own method of data storage, ranging from VSAM files to hierarchical and relational databases. These systems store critical data for their corporations, and they can't be tossed aside in the race to client/server. Instead, they must be integrated into the new environment. But how? Each platform provides its own means of data access, and thus to integrate them would require considerable effort on our part.
This was the challenge that Microsoft set out to solve with it's Open Database Connectivity (ODBC) standard. In developing ODBC, Microsoft wanted to provide a single Application Programming Interface (API) that could be used to access data on a variety of database management systems. Two of the main advantages of using ODBC are as follows:
The documentation on ODBC runs to some 800 pages, most of which you need not concern yourself with as a Visual Basic developer. However, you should understand the basic principles of ODBC because it is the critical element enabling you to use Visual Basic for developing client/server applications.
The architect consists of four major components, as shown in figure 6.3.
Fig. 6.3 ODBC architecture consists of these four major components.
Unfortunately, the ODBC standard is not as straightforward as we would like. Instead of providing a single standard for drivers, it classifies drivers conformity along two lines, their support for the ODBC API and their support for the SQL standard. An understanding of this issue is critical because it determines whether you can use a particular driver with your Visual Basic application.
For API purposes, drivers are classified into three categories: Core, Level 1, and Level 2. Categories are incremental; thus, Level 1 supports Core plus Level 1, and Level 2 supports Core plus Level 1 and Level 2. As the name suggests, Core provides a very basic level of functionality, such as the capability to do the following:
In addition to the previous items, a Level 1 driver provides the following:
A Level 2 driver represents a top of the range ODBC driver. In addition to the previous items, it can do the following:
Note: Most applications that provide access to data via ODBC require a driver that supports the full Level 1 API.
As with API conformity, ODBC defines three levels for SQL conformity: Minimum, Core, and Extended. The differences between these three are defined in terms of the Data Definition Language (DDL), the Data Manipulation Language (DML), expressions, and data types they support. Table 6.1 highlights the differences.
Table 6.1 Table 6.1 SQL Conformity
In addition to classifying drivers based on their conformity to the ODBC standard, you can define drivers as single-tier or as multi-tier.
Having seen an overview of ODBC in the previous section, we'll look at how ODBC is implemented and how to configure it for use with our applications.
ODBC is implemented as a series of executable files, Dynamic Link Libraries (DLLs) and INI files, as follows:
Before we can access a database via ODBC, we must define the Data Source so that the ODBC knows how to establish the connection for us when we make the request. In our case, you can do this in the following two ways:
Regardless of which method you use, you'll need to know several things about the database before you can define the Data Source. The exact information required depends on the ODBC driver, so you'll need to refer to the vendor's documentation. In our case, because we're working with MS SQL Server, we need to know the following:
TB7 The name of the database.
In the following two subsections, we'll examine how to define the Data Source required to access the Northwind database. For our examples, the information required to define the Data Source is as follows:
To define the Data Source using the ODBC Administrator do the following:
1. Launch the ODBC Administrator applet by double-clicking it from the Control Panel.
2. From the ODBC Administrator main window (see fig. 6.4) click Add
to add the new Data Source.
Fig. 6.4 The ODBC Administrator is used to add data sources.
1. The ODBC manager provides you with the list of installed drivers, which you can use to connect to various databases (see fig. 6.5). Because we want to connect to MS SQL Server, select SQL Server and click OK.
Fig. 6.5 A list of installed drivers.
1. You are now asked to provide driver-specific information via the dialog box shown in figure 6.6. Complete this dialog box as shown and click OK to complete the task.
Fig. 6.6 Specify your ODBC driver information here.
After completing the task, you return to the main window, where you can see your Data Source displayed in the list box (see fig. 6.7).
Fig. 6.7 The SQL Server Data Source is defined in the ODBC Administrator main window.
Visual Basic provides the RegisterDatabase method so that developers can programmatically define Data Sources. The RegisterDatabase method takes the following four parameters:
The easiest way to determine all the values for the various parameters is to define the Data Source first using the ODBC Administrator and then examine the ODBC.INI file. Examining the INI file for our NWIND Data Source, you'll see the following entries:
From this, you can see that the Data Source name is NWIND, the driver is C:\WIN31\SYSTEM\sqlsrvr.dll, and that the remaining attributes are Description, Server, FastConnectOption, UseProcForPrepare, Database, and OEMTOANSI. Having obtained this information, you are now ready to create the code required to define the Data Source (see Listing 6.1).
Listing 6.1 REGDB.BAS Registering the Database
Note: In the previous code, you will notice the driver name being passed to the RegisterDatabase function is "SQL Server" and not sqlsrvr.dll. The driver is known to ODBC as "SQL Server", which is responsible for determining the DLL that matches the logical name. To obtain this entry, I had to examine the ODBCINST.INI file, where a list of installed drivers are maintained. The relevant entries are shown as follows:
The most common way to access data stored in tables located on a remote database is to attach the tables to a local Jet database and then use the DAO model to work with the tables. With Visual Basic, you can programmatically attach remote tables to a local database. In this section, we're going to examine these features by building a simple application that allows the user to attach remote tables to a Jet database and view the attached tables.
In the context of our examination of ODBC, there are two key features to the application:
1. Allow the user to select a Data Source from those defined on his workstation.
2. Attach the remote table to the database.
We'll begin by implementing the dialog box to display a list of the Data Sources defined on the user's workstation. The dialog box will provide the following functionality:
Let's begin by implementing the user interface, figure 6.8 shows the completed dialog box.
Fig. 6.8 The SQL Data Sources selection dialog box.
The form properties that need to be changed are set out in Table 6.2.
Table 6.2 Table 6.2 Data Source Selection Form Properties
Property Seting Auto3D True BackColor &H00C0C0C0& (light gray) BorderStype Fixed Double Caption SQL Data Sources ControlBox False MaxButton False MinButton False Name frmODBCDataSources
All other properties should be left at their default. Once you have set the form properties, you can add the required controls, as shown in figure 6.8. Details of the controls and their property settings are set out in Table 6.3 (label controls take default properties).
Table 6.3
Table 6.3 Control Settings for the Data Sources Dialog Box
Control Property Setting ComboBox Name cboDSN Sorted True Style Dropdown List TextBox Name txtDatabase TextBox MultiLine True Name txtDesc TextBox Name txtDriver TextBox Name txtUser TextBox Name txtPassword PasswordChar * CommandButton Caption OK Default True Name cmdOK CommandButton Cancel True Caption Cancel Name cmdCancel
Now that you've completed the user interfac,e it's time to start adding the code. The most difficult part of implementing this feature is to obtain a list of the Data Sources that are defined on the user's workstation. To do this, you'll have to drop down to the ODBC API. Included in the ODBC API is a function called SQLDataSources, which we can use to obtain a list of the Data Sources. To use this function, we need to allocate some ODBC environment space, which is done via another ODBC function called SQLAllocEnv. Listing 6.2 shows the necessary Visual Basic declarations.
Listing 6.2 SQLENV.BAS Allocating the Environment Space
Now that we're figured out the API calls we need to make, we're ready to use the code in Listing 6.3 to write a subroutine to load the list of Data Sources into the list box on the dialog.
Listing 6.3 GETDSRC.BAS Listing the Data Sources
After defining the required variables, you allocate the required ODBC environment space. Once this is done, you begin to obtain a list of the Data Sources by making successive calls to the SQLDataSources function. As you retrieve each Data Source, you add it to the list box until you have obtained all Data Sources.
Once you have written the routine for retrieving the Data Sources, you're in a position to use Listing 6.4 to complete the Form_Load method.
Listing 6.4 LOADFRM!.BAS Loading the Form
Once you have displayed the list of data sources, all that's left is to retrieve the information about each Data Source as the user selects it. This is done by reading the ODBC.INI file using the Windows API call GetPrivateProfileString. The code itself is added to the list box's click method as shown in Listing 6.5.
Listing 6.5 GETDSINF.BAS Getting Data Source Information
Having written the most difficult functions, you only need to add the supporting code.
Having provided the user with a means for selecting the Data Source, all that remains is to add the code for attaching the table to the local database and the supporting code. The function in Listing 6.6 attaches the table to the database.
Listing 6.6 TBLATTCH.BAS Attaching the Table
Most business applications can be split into types, transaction processing and decision support or reporting. While you will use the same elements of Visual Basic to build both types of application, they differ greatly in one aspect: the location of the data.
In a transaction type system, the data needs to be updated immediately on the server so that it reflects the current situation at any given time. For example, if I have only one item on a particular product in stock and I accept an order for it, I need to update the stork records to reflect this so that no one else will accept an order for the same item.
In a decision support type of system, the data tends to be at least a day old, if not longer. Normally, users are looking at trends, so up to the minute data is not required.
Using Visual Basic to develop such applications allows us to take advantage of this fact. In developing the application, we can copy the data once from the server to the users workstation before the user begins to work with it. This will free up the server to handle other requests and also means that the user will normally experience better performance in screen updates and so forth. In this section, we'll look at the techniques used to implement such an application.
In our example, you'll implement a screen for the Northwind Traders database, which allows users to view a graph of product sales for the year. In this case, you need to extract the product sales from the Orders and Order Details tables and store them locally. The first thing you need to do then is to create a table in our local database to store the data. The structure of this table is shown in Table 6.4.
Table 6.4Table 6.4 Structure of the Local Sales Table
Field Type Length Comment
Month Text 2 Month indicator for product sales
Product_id Number Product's id number
NetSales Number Net sales of product
In order to access the data stored in the remote database, you'll need to attach the tables to our local database.
Once you have created the local table and attached the remote tables, you need to write some queries to copy the data between the tables.
The first query needs to join the Orders table with the Order Details table to extract the date, the product, and the net sales. The SQL to do this is as follows:
Once you have extracted this information, you add a second query that summarizes the information and enters it in the local table. This query is as follows:
Now that you have all the basic components in place, you need to provide the user with the means to execute the copy. Each time you copy down the remote data, you need to do two things: first you need to delete the existing data and second you need to copy down the new data. To allow for future growth in the system, we'll develop a dynamic method of handling this. The idea is to create a batch job consisting of the several queries, which when executed in sequence, provides the required functionality. In order to store information about the batch process, you need to add two table to our local database: one to hold general information about the batch jobs and one to hold the detailed steps of each job. The structure of these tables is shown in Table 6.5 and Table 6.6.
Table 6.5 Structure of Batch Description Table
Field Type Length Comment BatchNo Text 3 Batch number BatchName Text 50 Name of batch BatchDesc Text 50 Description of what batch does
Table 6.6 Structure of Batch Detail Table
Field Type Length Comment BatchNo Text 3 Batch number StepNo Number Step in batch SQLQuery Text 50 Name of query to be executed
Having provided a means for storing the batch information, all you need to do now is to add a user interface that will allow the user to execute the batch. The user interface can be a simple dialog box, like the one in figure 6.9, which lists the batch jobs and its description.
Fig. 6.9 The batch execution dialog box.
When the user clicks OK, the batch is executed via the routine shown in Listing 6.7.
Listing 6.7 RUNBATC1.BAS Running a Batch Query
Once the data has been copied to the local table, we're back to standard Visual Basic to create the decision support screen.
Up to this stage, you have worked exclusively with Visual Basic's DAO model. Using the model, you have created record sets and worked on the data returned in these sets. While this method of working with data has been adequate in dealing with the Northwind situation, it does have one major disadvantage: it is not very well suited for carrying out operations on large tables. Consider the following problem.
On your server, you have a table that contains a summary of your company's product sales by day. This table contains over a 100,000 rows. Each day you receive a file containing 6,000 transactions, which either replace existing rows or addnew rows to the table. How would you go about performing the required updates/additions?
Using the DAO model, our first attempt might be to use a design such as this:
While this solution will work fine on a small table, it will fail in our case because our dynaset contains over a 100,000 rows and the FindFirst method performs a liner search. Thus, it will take hours to perform the required updates. (Take my word, I've tried it.)
Because we have identified the performance hog as such a large dynaset, our second attempt might be to try and reduce the size of the dynaset. One way we could do this is to retrieve all the sales summary records for a particular day, update them or add new ones, and move on to the next day. Our refined design might look like the following:
While this solution does improve the situation, it still does not reach our expectations. We're still moving thousands of records from the server to the workstation.
What this problem requires is the capability to carry out operations on the data located in the remote database without having to transfer the records to the workstation. The solution lies in an action query. The Execute method of the database object allows us to submit SQL commands to manipulate data stored in a table without first having to create a Recordset. Using the execute method, our final solution would be as follows:
For comparison, testing the first and final solution on my server showed that the final solution took only one-sixth the time of the first solution.
In addition to using action queries to insert or update records, you can use them to do the following:
At times, you will need to create tables and indexes on the server database from the desktop application. This section discusses how this is done, and the challenges associated with this activity.
Creating tables and indexes in a C/S environment is almost the same as doing so in a typical desktop database application, with one exception: you need to have been granted the correct security privileges on the server before you can perform the task.
In the first example, you'll build a simple utility to create tables on any database for which ODBC drivers are available.
When the user clicks on the New button, a dialog box is displayed which will enable him to create a new table.
Before building the application, let's take it for a test drive so that we understand how it works.
Fig. 6.10 The New button
allows you to and add a new table to a database.
1. Complete the dialog as shown in figure 6.11 and click the Create
button to add the new table.
Fig. 6.11 A application can be written to add a new table to a server database using Visual Basic.
Now it's time to start coding. We'll begin by developing the user interface. After opening a new project, set the form's properties to the values set out in Table 6.7.
Table 6.7
Table 6.7 The Form Property Settings
Property Setting Auto3D True BackColor &H00C0C0C0& (light gray) BorderStype Fixed Double Caption Table Example ControlBox False MaxButton False MinButton False Name fMainWnd
All other properties should be left at their default. Once you have set the form properties, you can add the required controls, as shown in figure 6.11. Details of the controls and their property settings are set out in Table 6.8 (label controls take default properties).
Table 6.8
Table 6.8 The Application's Controls
Control Property Setting ListBox Columns 3 Name lstTables Sorted True ListBox Columns 3 Name lstFields Sorted True Button Caption E&xit Name cmdExit Button Caption &New...
After building the main form, we turn our attention to the create tabel dialog box. Add a new form to the project and set it's properties as set out in Table 6.9.
Table 6.9
Table 6.9 The Dialog Box Settings
Auto3D True BackColor &H00C0C0C0& (light gray) BorderStype Fixed Double Caption Table Example ControlBox False MaxButton False MinButton False
Add controls to the form as shown in figure 6.11 and set the properties as shown in Table 6.10.
Table 6.10
Table 6.10 The Dialog Box Control Settings
Control Property Setting TextBox Name txtTable Grid Cols 4 Name grd Rows 20 Frame Name fraNewField TextBox Name txtName ComboBox List True/False Byte Integer Long Currency Single Double Name cboType TextBox Name txtLength Button Caption &Add Name cmdAdd Button Caption C&reate Name cmdCreate Button Caption &Close Name cmdClose
This completes the interface, now let's start adding the code. We'll handle the application start up first. The code executed at the start of the application is located in the main form's Form_Load, as shown in Listing 6.8.
Listing 6.8 DSPMAIN.BAS-Displaying the Main Form
The first block of code simply centers the form on the user's screen. The second block of code opens the database and displays it's list of tables. Note the way the OpenDatabase method is used. Entering just "ODBC;" as the last parameter causes the ODBC manager to display the list of data sources and enables the user to logon to any data source. The function DisplayTables simply loops through the database's TableDefs and adds the name of the table to the tables list box in the main screen. The code is provided in Listing 6.9.
Listing 6.9 DSPTBL.BAS Displaying the Existing Tables
Moving to the new table form, you use its load method to configure the grid and locate the form, as shown in Listing 6.10.
Listing 6.10 CNFGRID1.BAS Configuring the Grid and Locating the Form
In review of the code, you will note that the form uses two variables at the form level, iLastUsedRow indicates the number of rows used in the grid, whereas fFieldsAdded acts as a flag to indicate if fields have been added.
The most interesting code in the new table form is in the click method of the Create button (see Listing 6.11).
Listing 6.11 ADDTBL1.BAS Adding a Table
As you can see, there are a several steps involved in adding a table to the database. First, a new TableDef must be created. Then, the table's fields need to be created and added to the new TableDef. Finally, the new TableDef must be added to the database's TableDefs collection.
This completes the utility. As you can see, there really no difference in creating a table on a C/S database as opposed to a desktop database from a code point of view. From a process point of view there is one major difference: Security. Before you can create the table you must possess the required security level.
While server databases are normally managed by a Database Administrator, there are situations where users are allowed to add tables themselves. This section showed you how to use Visual Basic to accomplish this.
This chapter taught you how to use ODBC technology in Visual Basic applications. To round out your knowledge on using Visual Basic for developing client/server database applications, you should also review the material in the following chapters:
| Previous Chapter | Next Chapter | Search | Table of Contents | Book Home Page |
| Buy This Book | Que Home Page | Digital Bookshelf | Disclaimer |
To order books from QUE, call us at 800-716-0044 or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.