Visual C++ 6 Unleashed

Visual C++ 6 Unleashed

By MICKEY WILLIAMS and David Bennett

ODBC Architecture

ODBC applications can use the ODBC API to access data from a variety of different data sources. Each of the different data source types is supported by an ODBC driver specific to that type of data source—it is the driver that implements the meat of the ODBC API functions and performs the actual communication with the database.

The ODBC environment helps you manage connections to different data sources by providing the Driver Manager, which is implemented in ODBC32.dll. Your application need only link to the driver manager, which handles the ODBC API calls and passes them off to the appropriate driver. The driver manager also provides certain translations, such as converting between ODBC version 3 and 2 if the application and driver support different versions. Both the interface between your application and the Driver Manager, and the interface between the Driver Manager and individual drivers, use the same ODBC API.

The driver manager will load the appropriate driver when your app tries to connect to a data source of that type, so you don't need to worry about keeping track of separate driver DLLs for each data source type.

Figure 20.1 illustrates how the ODBC functions. An ODBC application makes ODBC database calls to the ODBC Driver Manager. The Driver Manager translates the ODBC call to the appropriate database call provided by the individual database (or data source).

20fig01.gif

Figure 20.1 Various components of the ODBC environment work together to provide flexible database access for your applications.

The figure illustrates how your application can use the ODBC API to access a variety of different data sources by linking to a single library—the driver manager.

ODBC Drivers

ODBC allows your applications to access different databases by using different drivers for each database. You can use ODBC to access any database for which a driver is supplied. Visual C++ 6.0 provides drivers for the following databases, although many other drivers are available either from Microsoft or from other database vendors:

In some cases, the database driver itself will manipulate the data, as is the case with text file drivers. This sort of driver is known as a single-tier driver.

In other cases, you may be using multiple-tier drivers, in which the database driver uses a database's proprietary client software to communicate to the actual database server. This is most often the case with traditional RDBMS systems, such as Oracle, Informix, or SQL Server.

Each of these drivers supports slightly different levels of functionality. Obviously, an RDBMS such as SQL Server or Oracle can support a wider variety of operations than plain old text files. Likewise, the drivers for these types of data sources support more of the functionality provided by ODBC.

In general, each ODBC driver conforms to one of three levels of conformance: the Core API level, and extension levels 1 and 2. However, many drivers may not implement some functions of their claimed conformance level, and most will implement several higher-level functions. In general, you should plan to test your application with each of the drivers that it may use, because there may be many subtle (or not so subtle) differences between drivers.

Core API Conformance

Core API conformance provides a minimum of ODBC functionality, including connecting to a database, executing SQL statements, and accessing the results of a query. This level also includes limited capabilities for cataloging a database and retrieving error information.

Extension Level 1 Conformance

Extension level 1 includes additional features, giving your applications more complete access to the schema of a database, supporting transactions and stored procedures, and supporting scrollable cursors.

Extension Level 2 Conformance

Extension level 2 provides additional features that are generally found only in complete client-server database implementations, such as more detailed access to the data dictionary, bookmark support, access to special columns, and additional optimization capabilities.

SQL Grammar Conformance

ODBC drivers must support a standard minimum SQL grammar. However, each driver may support a different set of extensions to this minimum standard. ODBC defines conformance levels based on the SQL-92 Entry, Intermediate, and Full levels, as well as the FIPS 127-2 Transitional level. In many cases, as you will see later in this chapter, advanced functionality can be provided via escape sequences, which are standardized in the ODBC API—the driver will translate these as needed for a particular data source.

ODBC Driver Manager

The ODBC Driver Manager provides several very useful features to your application. First, as you saw earlier, the Driver Manager takes care of loading individual drivers as needed (when your application tries to connect to a data source). It also maintains a table of the functions in that driver, based on the connection handle, so that any subsequent ODBC API calls you make are routed to the correct driver. The Driver Manager also provides several functions that aren't provided by drivers, allowing your application to see which drivers and data sources are available on the local system. In addition, the Driver Manager will do some preliminary error checking on many calls before passing them off to the appropriate driver.

Data Sources

In ODBC, a data source is just that—a source of data. It can be an individual file-based database used in desktop database applications, such as MS Access or FoxPro, or it can be a full-blown Relational Database Management System (RDBMS), such as Informix, Oracle, or SQL Server. It can even be a non-relational source of data, such as a text file or an Excel spreadsheet. The concept of a data source allows all the nitty-gritty details of a connection to be hidden from the user, who only selects a data source from a list of data source names and doesn't worry about network addresses or specific file locations. Data sources are made available on the local machine by using the ODBC Data Source Administrator, or they may be added programmatically.

ODBC Data Source Administrator

Your ODBC application connects to an ODBC data source, rather than directly to any particular database. The ODBC data sources available to applications on the local machine are configured with the ODBC Administrator application, which can be found in the Control Panel by clicking on the icon labeled 32-bit ODBC. This will open the tabbed dialog shown in Figure 20.2.

20fig02.jpg

Figure 20.2 ODBC Data Source Administrator.

The ODBC Drivers tab lists the drivers that are currently installed on the local machine. The data sources that are configured are shown on the User DSN, System DSN, and File DSN tabs:

TIP

At one time, File DSNs were rarely utilized. However, File DSNs are a good way to control ODBC access without forcing the user or the server administrator to implement a User or System DSN. If you are distributing an executable, or placing ActiveX programs on a Web site and can't get to your Web server, try using a File DSN for your file access.

Adding a Data Source

Before using an ODBC application, you must add the data sources that it will be using. You can add a data source to any of the DSN tabs by clicking on the Add button, which will present you with a dialog to choose the driver to use. Depending on the sort of database driver you choose, you will be presented with additional dialogs prompting for things such as the database name, server address, or other default settings for this data source.

ODBC Installation and Setup Programming

In addition to the utilities such as the Data Source Administrator, the ODBC SDK provides functions that you can use in your own installation and configuration utilities. This goes beyond the scope of this chapter, but is documented in the ODBC 3 SDK Programmer's Reference.

Share ThisShare This

Informit Network