Chapter 9

Using ODBC to Access Databases

Visual Basic, with its powerful drawing tools and easy programming methodologies, is an excellent tool for creating front-end data-access programs. The Jet engine enables Visual Basic users to create powerful database applications. In the world of corporate computing with large networks and multiple users, client/server database applications are the required solution.

When creating database applications with the Jet engine, the Database Engine is a part of the application. The database files can reside on a shared file server, but each application that uses the database contains the Database Engine code. Such an application is called remote database application. Many users can share the database, but the methodology is actually that of sharing files. To enable users to share a database, all the applications that use the database must have access to the database files. Also, none of the applications can open the database in �exclusive� mode. When you open a database in exclusive mode, you prevent other applications from using the database.

In this chapter, you learn about the following:

• Considering performance issues when developing client/server applications
• Using ODBC to access database servers
• Using the Data Access Object to retrieve data
• Considering performance issues when using ODBC
• Using the ODBC API directly
• How to perform SQL (Structured Query Language) pass-through to access servers directly

Exploring Visual Basic and Client/Server Computing

In client/server applications, the Database Engine and the database files reside on a machine separate from the applications that access the database files. Therefore, the Database Engine can run on a powerful server platform while the client applications run on less powerful machines. The server can even be running a non-Windows operating system. Figure 9.1 depicts the differences between remote and client/server database applications.

Fig. 9.1

An architectural diagram of the client/server application environment.

A client/server application accesses data by having the client application send a query to the server. The client application usually sends the query through a networking (interprocess communications, or IPC) protocol. The protocol that the application uses is determined by the protocol stacks in the computing environment. Some common protocol stacks include NetBEUI, NetBIOS, TCP/IP, and IPX/SPX. Even though the query is being made across the computer network, the database server blocks the application calling it until the server retrieves the data and sends the data back to the client application.

Open Database Connectivity (ODBC) is a method of communication to client/server databases in Visual Basic. ODBC is part of Microsoft�s Windows Open Systems Architecture (WOSA), which provides a series of application program interfaces (APIs) to simplify and provide standards for various programming activities. The goal is to have all applications communicating through the same set of APIs. ODBC is just one piece of the WOSA picture. Other components include telephone services (TAPI), messaging services (MAPI), and open data services (ODS).

This chapter covers the basics and the advanced issues in developing client/server applications.

Although ODBC is intended for client/server applications, ODBC drivers are available for remote databases like Access and dBASE. In this chapter, these drivers are used to develop the sample application and code samples.

Understanding ODBC Operation

Before you can access ODBC databases, you must configure the ODBC data source names, the ODBC drivers, and the configuration values used in ODBC.INI. You also should understand the structure of an ODBC driver and the ODBC API as well as some ODBC-related terminology.

Defining ODBC Terminology

ODBC drivers are classified as either single-tier or multiple-tier:

• A single-tier driver is designed for use with remote desktop database management systems (DBMSs). Such drivers include Access, FoxPro, dBASE, spreadsheets, and text files. The SQL statement issued to the ODBC driver is converted into low-level instructions that operate on the database files directly.
• A multiple-tier driver uses a client/server database to process the SQL statement that the application sends. The remote database server processes the statement and returns the statement�s result.

Each ODBC driver conforms to one of three levels of capabilities: Core Level, Level 1, and Level 2.

The Core Level is the base set of capabilities that an ODBC driver must contain. All ODBC drivers must meet the requirements of this level. The Core Level capabilities for an ODBC driver are the following:

• Providing applications with database connections
• Preparing and executing SQL statements
• Returning results from queries
• Processing transactions, including rollbacks and commits
• Providing error information

Level 1 includes the capabilities of Core Level ODC drivers plus data-source connectivity through driver-specific dialogs. All ODBC drivers that the Jet engine uses must meet or exceed this level.

Level 2 includes the capabilities of a Level 1 ODBC driver plus the following capabilities:

• Browsing available connections to data sources
• Retrieving query results in array format
• Providing scrollable cursors
• Obtaining additional information such as stored procedures

Adding ODBC Drivers

Before accessing an ODBC database, you must install on your system the appropriate ODBC driver for that database. You install this driver by using the ODBC Manager applet in the Windows Control Panel. You can also use the ODBCADM.EXE program supplied with Visual Basic.

To install an ODBC driver, just perform the following steps:

1. Click on the ODBC Manager applet in the Control Panel as shown in figure 9.2.

Fig. 9.2

Opening the ODBC Manager application to maintain ODBC drivers and data sources.

2. The Data Sources dialog box, shown in figure 9.3, appears. This dialog box contains all the data sources listed in ODBC.INI.

Fig. 9.3

Browsing the available data sources with the ODBC Manager.

3. Select the Drivers button. The Drivers dialog box appears (see fig. 9.4), displaying all ODBC drivers installed on the system.

Fig. 9.4

Using the ODBC Manager to add a new ODBC driver to the system.

4. To add an ODBC driver, choose the Add button. The Add Driver dialog box appears (see fig. 9.5), prompting you to enter the ODBC driver disk to install.

Fig. 9.5

Completing the ODBC driver installation.

5. Insert the disk and follow the installation instructions. The installation process adds the ODBC driver to the system and updates the ODBCINST.INI file with the installed driver information.

The ODBC driver that you install must be at least Level 1 compliant. Most ODBC drivers meet this requirement. The driver�s manufacturer can tell you whether its has all the Level 1 capabilities.

Creating and Naming ODBC Data Sources

An ODBC data source is a named connection to a database. ODBC.INI stores the data-source entry, which consists of the information necessary for connecting to the database server. This information includes the database name and location, the ODBC driver to use, and various attributes specific to the ODBC driver that you are using.

To create an ODBC data source, two methods are available:

• Through the ODBC Data Manager
• Through the Data Access Object using the Database Engine�s RegisterDatabase method.

Adding an ODBC data source is easy. To add a data source through the ODBC Data Manager control applet, perform the following steps:

1. Open the Windows Control Panel and click on the ODBC Data Manager applet or start the ODBCADM.EXE program included with Visual Basic. The Data Sources dialog box appears, containing all the data sources listed in ODBC.INI.
2. Select the Add button. The Add Data Source dialog box appears as shown in figure 9.6. This dialog box lists all ODBC drivers installed on the system.

Fig. 9.6

Choosing an ODBC driver to use when adding a data source.

3. Select the appropriate driver from the list. A driver-specific dialog box appears for ODBC setup. For example, figure 9.7 shows the dialog box that you see if you select the dBASE driver in the Add Data Source dialog box.

Fig. 9.7

Setting the ODBC driver-specific options for a data source.

4. Enter the appropriate information, including the data source name and description along with the driver-specific information, and choose OK. The ODBC Data Manager then adds the data source to the data-source list and ODBC.INI.

To add a data-source name using the RegisterDatabase method in DBEngine, you first must examine the method to understand the information that it requires:

DBEngine.RegisterDatabase dbName , driver , silent, attributes

The parameters in this syntax are the following:

Listing 9.1 is a code example that demonstrates how to add a data source programmatically for the Address Book example in Chapter 8.

Listing 9.1 Adding a data source programmatically for the Address Book Example

Setting ODBC Time-Out Values

Because you use ODBC primarily for accessing client/server databases, delays are possible in database logins and queries, particularly when the database server resides on a mainframe or host computer connected by a modem or bridge. The delay is primarily due to the time necessary to issue calls to the database server through the local area network (LAN) or dial-up communication lines.

Visual Basic maintains a default database-login time of 20 seconds. This value is used when the user issues an OpenDatabase command. If Visual Basid cannot establish the connection within this time frame, the OpenDatabase call fails. You can alter this value through DBEngine�s LoginTimeout property, as in the following statement:

The default time-out value for database queries is 60 seconds. You can alter this value by changing the database QueryTimeout property or a QueryDef�s ODBCTimeout property. Be careful when performing queries on tables that contain BLOB (Binary Large Objects) information. Queries on such tables are typically very slow, so you might want to increase the time-out value according to the BLOB data�s size and the database server�s speed. If you specify 0, no time-out occurs. The following examples demonstrate alterations to the default time-out:

The ODBC section of VB.INI or APP.INI also maintains these time-out values, as you will see in the upcoming sections.

Examining ODBC.INI and ODBCINST.INI

As mentioned earlier, ODBC operation depends on two files that the ODBC Data Manager creates: ODBC.INI and ODBCINST.INI.

ODBCINST.INI is located in the Windows directory and contains information about the ODBC drivers installed on the system. Listing 9.2 shows a sample ODBCINST.INI file.

Listing 9.2 A Sample ODBCINST.INI File

At the top of the ODBCINST.INI file is the [ODBC Drivers] section. This section lists all installed ODBC drivers. The Installed Drivers dialog box of the ODBC Data Manager lists all installed ODBC drivers. Note that for client/server databases or multiple-tier drivers, the entries are simple. Each section contains a �Driver� and �Setup� entry. Single-tier drivers have additional parameters, such as the SQLLevel and APILevel that the driver supports.

The ODBC.INI file maintains a list of all defined ODBC data sources. The file contains all the information about the data source as specified through the Add Data Source dialog box (see fig. 9.6) or by the attributes parameter of the RegisterDatabase method. Listing 9.3 shows a sample ODBC.INI file.

Listing 9.3 A Sample ODBC.INI File

Using the ODBC Section in VB.INI or APP.INI

The last set of parameters that affect ODBC operation is contained in an ODBC section of VB.INI or your application�s initialization file, APP.INI. Table 9.1 indicates the valid entries, their purpose, and any default values.

Table 9.1 Application-Specific Parameters That Affect ODBC Operation

Converting ODBC Data Types

When applications read in external data types, a one-to-one correspondence between types does not always occur. Such is the case when reading external ODBC data sources. You need to understand how ODBC data types relate to Jet engine data types. Such an understanding is important when you attach an ODBC table to a database. The Jet engine maps ODBC data types to Jet data types. Table 9.2 describes these relationships.

Table 9.2 Comparing ODBC and Jet Data Types

Comparing the ODBC API and Jet�s Data Access Objects

When accessing ODBC databases, you can use two methods: directly through the ODBC API or with the Jet Data Access Object. Note that for Jet to access the ODBC databases, the Data Access Object calls the ODBC API internally.

The ODBC API

The ODBC API consists of approximately 30 functions. It supports field-by-field data retrieval and uses the SQL syntax for manipulating and defining data. The various ODBC drivers conform to one of the three levels of capabilities (Core Level, Level 1, and Level 2). To use the ODBC API directly, you must know the level of ODBC that the driver supports.

The Jet Data Access Object can use ODBC drivers that provide Level 1 support. Therefore, Jet does not support the complete set of ODBC functions. Table 9.3 lists all the ODBC functions that the Jet engine supports.

Table 9.3 ODBC API Functions Supported by the Jet Engine

Visual Basic does not include the ODBC constants and functions that Visual Basic developers need. However, this exclusion does not prohibit you from using the ODBC API. All the ODBC functions are in the file ODBC.DLL, which Visual Basic includes. The documentation for these functions, as well as the Visual Basic prototypes, are available in the ODBC Developer�s SDK.

Using the ODBC API directly has some advantages. The API is quite flexible because it is a very low-level API. In other words, the ODBC API is used for directly issuing commands to a database server. The developer must build all data structures for retrieving data. This gives the programmer maximum power in designing applications. Also, the API is fast, goes directly to the database server, and imposes no additional overhead. Finally, ODBC is designed to provide connectivity to all databases and thus is portable across languages and databases.

On the other hand, the ODBC also presents some disadvantages. First, you need an external development kit, the ODBC SDK, to get the prototypes and documentation required for using the API functions. Because the ODBC is a very low-level API, you find yourself building many �wrapper� or helper functions to provide a higher level of interface for your application. The API provides no object model on which you can build and use. Additionally, the ODBC API is subject to change, leaving the developer with the possibility of developing to a moving target. And finally, only rarely will an application be likely to need to access the ODBC API directly.

The Jet Data Access Object

Visual Basic�s Data Access Object is the preferred method for accessing ODBC databases because it uses the ODBC API internally, provides a higher-level interface, and is based on an object model.

The Data Access Object consists of two sets of functions: the Data Definition Language (DDL) and the Data Manipulation Language (DML). Table 9.4 lists the interfaces that comprise the Data Access Object and their purpose.

Table 9.4 Data Access Object Interfaces

When using the Data Access Object, your Visual Basic program can access ODBC databases in the same manner as desktop databases. The preferred method for accessing ODBC databases is to attach the tables in an ODBC data source to your application�s database. Listing 9.4 shows how you attach tables to an ODBC database.

Listing 9.4 Attaching Tables to an ODBC Database

In listing 9.4, the string passed into the CreateTableDef method is the TableDef name. The Connect string consists of the following items:

• The database type, which in this case is ODBC.
• A string of parameters specific to the ODBC driver that you are using. The string contains the database name or path, the user login ID, the user password, the Data Source Name as identified by ODBC.INI, and an optional Logintimeout.

Direct Access to the Database Server

Attaching a table as just described is one method for accessing an ODBC database with the Data Access Object. You also can use the Data Access Object by directly opening and using DBEngine�s OpenDatabase method and specifying an ODBC connection string. You shouldn�t use this access method, however, because it slows your application�s performance.

When attaching to an ODBC database, the Jet engine stores a great deal of information about the table locally, including table and field information as well as server capabilities. When an application opens an ODBC database directly, Jet asks the server for this information every time that the application performs a query.

Performance Concerns: dynasets versus snapshots

To access ODBC data from Visual Basic, you can use one of two methods: by writing directly to the ODBC API or by using the Data Access Object.

When developing client/server applications with ODBC, you must consider a different set of performance concerns than when working with remote databases.

Visual Basic 4.0 replaces Visual Basic 3.0�s dynaset, snapshot, and Table objects with one generic object, Recordset.

When creating a Recordset object, you specify the type of recordset that you are creating. The type can be dynaset, snapshot, or Table. When accessing remote data through an ODBC connection, you can create two types of Recordset objects: dynaset or snapshot.

A Recordset object of type dynaset or snapshot returns as the result of a data query. The dynaset type contains a �live,� updatable view of the data in the underlying tables. When the dynaset changes, the underlying tables are immediately updated; conversely, when the tables change, the dynaset is updated. A snapshot is a static or nonupdatable view of the data in the underlying tables.

To understand more clearly when to choose one type rather than another, you need to know how each type of recordset is populated. When you create a snapshot, Visual Basic retrieves all the data in the selected columns of the matching rows and places the retrieved data in the recordset. Conversely, when you create a dynaset object, Visual Basic retrieves only the primary key (or bookmark) of the query. For both recordsets, Visual Basic stores in memory the results of the query.

The Jet engine is optimized to return only as many records as it needs to fill the resulting display screen. The rest of the data is retrieved either in idle time or through user scrolling (scrolling indicates an on-demand situation where the records are retrieved only when the user moves to records not visible on the screen). Again, the snapshot recordset retrieves all the selected columns in the matching rows, but dynaset retrieves the matching primary keys.

When you need rows of information, the snapshot recordset has all the information in memory and readily accessible for use. Because a dynaset recordset contains only the primary keys in memory, the Jet engine sends a separate query to the server to request all the selected columns. Jet optimizes this process by requesting clusters of information rather than one row at a time. The dynaset retrieves about 100 records surrounding the current record to create the impression that data is being retrieved rapidly.

Because they use different methods to retrieve and cache data, the performance of snapshot and dynaset recordsets differs greatly. The following are performance considerations for each type:

• If your result set is small and you don�t need to update data or to see changes that other users have made, use a snapshot recordset. It is faster to open and scroll through. Also, if you need to make only one pass through the results, use the dbForwardOnly option when creating the recordset. This option prohibits copying the data to a scrollable buffer.
• When working with very large result sets, use a dynaset recordset. Unlike snapshots, dynasets retrieve only the primary key, so jumping from beginning to end to retrieve data from both the first and last groups of data, for example, is much faster.
• When using a dynaset recordset, limit the number of tables and columns that the query selects. Select only the columns that you need. (For example, don�t use the * indicator for all columns.)
• If your result set contains memo or OLE fields, using a dynaset is much more efficient. dynasets retrieve memo and OLE fields only if your application is to display them on-screen. A snapshot retrieves all columns selected for the matching rows, and thus retrieves results in the memo fields during the query.

Understanding SQL Pass-through

When accessing data, your application can use two methods: queries based on the Jet engine and pass-through queries. In a Jet engine query, the engine compiles the statement and then sends it to the server. In a pass-through query, your application sends directly to the database server the SQL statement that you enter.

Pass-through queries offer a few advantages over compiled queries:

• Instead of the client machine processing and sending the query in chunks, the server processes the query, thus reducing network bandwidth.
• You can log the informational messages and statistics that the server returns.
• You can access server-specific functionality. Often, no equivalent Jet function or statement corresponds to the server�s function.
• You can provide access to nonstandard SQL extensions that the server supports. You can also directly access data-definition, administration, and security commands.
• You can support joins of multiple tables in multiple databases.
• Action queries such as update, delete, and add are faster than compiled queries that are based on attached tables.

Using SQL pass-through statements also has some disadvantages:

• Such queries return snapshots only.
• The client must know the server�s exact SQL syntax. Therefore, the code is not portable.
• You cannot parameterize pass-through queries.

Using Stored Procedures

A stored procedure is analogous to a Jet engine QueryDef. Such a procedure consists of a set of SQL statements that the server stores. The application program accesses a stored procedure to retrieve and update data. In some environments, stored procedures perform all data requests and updates because the application programs have no direct access to the remote tables.

If your application must update data in an environment in which you have no direct access to remote tables, you must execute a SQL pass-through that calls a stored procedure.

From Here...

Client/server computing offers network users great power and flexibility. A powerful database server can reside on a fast CPU to provide information to a host of clients. As this chapter has shown, Visual Basic offers a variety of methods for accessing ODBC database servers. You can use the ODBC APIs for directly accessing the database, and use the Jet engine�s Data Access Objects to provide a higher-level object interface. You can combine this interface with SQL pass-through statements to access accessing a server�s functionality directly. Visual Basic showcases these capabilities in providing a quality database-development environment.

To learn more about the Jet engine, see Chapter 8, "Accessing Other Databases with the Jet Engine". This chapter provides more information about the Jet engine and demonstrates how to use it to bring external data into database applications.