The ODBC Standard

The Microsoft Open Database Connectivity (ODBC) standard defines not only the rules of SQL grammar but also the C-language programming interface to any SQL database. It's now possible for a single compiled C or C++ program to access any DBMS that has an ODBC driver. The ODBC Software Development Kit (SDK), included with Visual C++, contains 32-bit drivers for DBF files, Microsoft Access MDB databases, Microsoft Excel XLS files, Microsoft FoxPro files, ASCII text files, and Microsoft SQL Server databases.

Other database companies, including Oracle, Informix, Progress, Ingres, and Centura Software, provide ODBC drivers for their own DBMS's. If you develop an MFC program with the dBASE/Xbase driver, for example, you can run the same program with an Access database driver. No recompilation is necessary—the program simply loads a different DLL.

Not only can C++ programs use ODBC but other DBMS programming environments can also take advantage of this new standard. You could write a C++ program to update a SQL Server database, and then you could use an off-the-shelf ODBC-compatible report writer to format and print the data. ODBC thus separates the user interface from the actual database-management process. You no longer have to buy your interface tools from the same company that supplies the database engine.

Some people have criticized ODBC because it doesn't let programmers take advantage of the special features of some particular DBMS's. Well, that's the whole point! Programmers only need to learn one application programming interface (API), and they can choose their software components based on price, performance, and support. No longer will developers be locked into buying all their tools from their database suppliers.

What's the future of ODBC? That's a difficult question. Microsoft is driving the standard, but it isn't actually "selling" ODBC; it's giving ODBC away for the purpose of promoting other products. Other companies are selling their own proprietary ODBC libraries. Meanwhile, Microsoft has introduced OLE-based DAO, which relies on the Jet database engine from Microsoft Access. (Chapter 32 describes DAO and compares its features with the features of ODBC.) And if that isn't enough, Microsoft is in the process of introducing OLE DB, an alternative to ODBC based on the Component Object Model (COM). Chapter 33 covers Visual C++'s new templates for wrapping OLE DB consumer and provider code.

The ODBC Architecture

ODBC's unique DLL-based architecture makes the system fully modular. A small top-level DLL, ODBC32.DLL, defines the API. ODBC32.DLL loads database-specific DLLs, known as drivers, during program execution. With the help of the Windows Registry (maintained by the ODBC Administrator module in the Windows Control Panel), ODBC32.DLL tracks which database-specific DLLs are available and thus allows a single program to access data in several DBMSs simultaneously. A program could, for example, keep some local tables in DBF format and use other tables controlled by a database server. Figure 31-1 shows the 32-bit ODBC DLL hierarchy.

Note from this figure that many standard database formats can be accessed through the Microsoft Access Jet database engine, a redistributable module packaged with Visual C++. If, for example, you access a DBF file through the Jet engine, you're using the same code that Microsoft Access uses.

ODBC SDK Programming

If you program directly at the ODBC C-language API level, you must know about three important ODBC elements: the environment, the connection, and the statement. All three are accessed through handles.

First you need an environment that establishes the link between your program and the ODBC system. An application usually has only one environment handle.

Next you need one or more connections. The connection references a specific driver and data source combination. You might have several connections to subdirectories that contain DBF files, and you might have connections to several SQL servers on the same network. A specific ODBC connection can be hardwired into a program, or the user can be allowed to choose from a list of available drivers and data sources.

Figure 31-1. 32-bit ODBC architecture.

ODBC32.DLL has a built-in Windows dialog box that lists the connections that are defined in the Registry (under HKEY_LOCAL_MACHINE-\SOFTWARE\ODBC). Once you have a connection, you need a SQL statement to execute. The statement might be a query, such as this:

SELECT FNAME, LNAME, CITY FROM AUTHORS
WHERE STATE = 'UT' ORDER BY LNAME

Or the statement could be an update statement, such as this:

UPDATE AUTHORS SET PHONE = '801 232-5780'
WHERE ID = '357-86-4343'

Because query statements need a program loop to process the returned rows, your program might need several statements active at the same time. Many ODBC drivers allow multiple active statement handles per connection.

Look again at the SQL statement above. Suppose there were 10 authors in Utah. ODBC lets you define the query result as a block of data, called a rowset, which is associated with an SQL statement. Through the ODBC SDK function SQLExtendedFetch, your program can move forward and backward through the 10 selected records by means of an ODBC cursor. This cursor is a programmable pointer into the rowset.

What if, in a multiuser situation, another program modified (or deleted) a Utah author record while your program was stepping through the rowset? With an ODBC Level 2 driver, the rowset would probably be dynamic and ODBC could update the rowset whenever the database changed. A dynamic rowset is called a dynaset. The Jet engine supports ODBC Level 2, and thus it supports dynasets.

Visual C++ includes the ODBC cursor library module ODBCCR32.DLL, which supports static rowsets (called snapshots) for Level 1 drivers. With a snapshot, a SELECT statement causes ODBC to make what amounts to a local copy of the 10 author records and build an in-memory list of pointers to those records. These records are guaranteed not to change once you've scrolled through them; in a multiuser situation, you might need to requery the database periodically to rebuild the snapshot.