MSQuery: How Microsoft Query Uses ODBC

• Microsoft Query for Windows, version 1.0

SUMMARY

This article provides general information on Open Database Connectivity (ODBC) and includes a list of the ODBC drivers that you must use in order to retrieve and access data with Microsoft Query.

Although ODBC interface is independent of the operating system, this article describes ODBC architecture as it pertains to Microsoft Windows.

NOTE: Microsoft Query is the specific example used in this article; however, much of the information also applies to other ODBC-compliant applications.

The following terms are used in this article.

Term                 Definition

Client-server        Client-server architecture divides the query
architecture         process and sends a query to the resource best
                     suited to handle it. The client, or front-end (for
                     example, Microsoft Query), displays the data to the
                     user, usually on a workstation. The server, or back-
                     end (for example, Oracle or SQL Server), stores,
                     retrieves, and protects the data the way a mainframe
                     computer does.

SQL (structured      The standard sublanguage used to retrieve, update,
query language)      and manage data from data sources.

Data source          Where the data is coming from (for example dBASE,
                     Oracle, Microsoft Excel, and so on).

MORE INFORMATION

WHAT IS OPEN DATABASE CONNECTIVITY?

Open Database Connectivity (ODBC) is a set of programming standards, or an application programming interface (API), that uses SQL to allow applications (such as Microsoft Query) to access data from relational and nonrelational database management systems (DBMSs). With ODBC, the client application asks what can be done and then delegates the tasks to a layer of dynamic link libraries (.DLLs).

To provide such flexible architecture, ODBC takes the hybrid design approach: first, ODBC defines the least common denominator to determine the functional areas common to all available DBMSs; once a least common denominator is established, the client application (such as Microsoft Query) can further query the server to determine what advanced functionality is available (an example of this type of functionality is outer joins).

The ODBC API is endorsed by the SQL Access Group (SAG) consortium, which includes companies such as Borland, Information Builders, Informix, Oracle and others. While the ODBC API is based on SAG's Call Level Interface (CLI) API, the ODBC API extends that specification into an open and vendor- neutral API (where open means the API is available for everyone to use and vendor-neutral means that the API is not limited to certain vendors, platforms, or DBMSs).

WHAT DOES IT MEAN TO BE ODBC COMPLIANT?

An application is said to be ODBC compliant when it conforms to the ODBC API. Compliance levels vary from application to application. The API has three compliance levels.

     Level             Definition of level
     --------------------------------------------

     Core API          Minimal compliance.

     Level 1           A few more functions are added to the Core API.

     Level 2           Every function in the API has been used.

                              HOW ODBC WORKS
                              ==============

The driver sits between the driver manager (discussed below) and the network. The driver processes ODBC function calls, manages all exchanges between the application and the data source, and may translate the standard SQL to the native SQL of the target data source.

ODBC provides two types of drivers: single tier and multiple tier.

The Driver Manager (ODBC.DLL)

In addition to the client application and the driver, ODBC includes a module called the driver manager (ODBC.DLL) that provides the application with a list of available drivers; the driver Manager loads and unloads these available drivers as required.

The driver manager is located in your Windows System directory. All ODBC- compliant applications, such as Microsoft Access and Visual Basic 3.0, use this .DLL.

NOTE: If you have Microsoft Access or another ODBC-complaint application installed on your computer, and you install Microsoft Query, you may be prompted to overwrite the existing ODBC.DLL. Because the ODBC.DLL file included with Microsoft Query is later version, you should update the file to this later version. Updating this file should not affect Microsoft Access or other ODBC-compliant applications.

SINGLE-TIERED DRIVERS

The single-tiered driver uses the indexed sequential access method (ISAM). This type of driver not only processes the ODBC API calls, but acts as an SQL engine as well. An SQL engine parses, validates, and optimizes SQL statements, and then retrieves the data from the underlying data source directly. This means that there is no "smart" back-end that the driver communicates with; instead, the driver communicates directly with the file.

FoxPro, dBASE, Btrieve Paradox, Microsoft Access, Microsoft Excel, and text files all use single-tiered drivers. Because implementing the SQL engine is a lot of work, these drivers are limited in the SQL they support and do not support advanced features such as transactions.

FoxPro, dBASE, Btrieve Paradox, and Microsoft Access have two common drivers: SIMBA.DLL and MSJETDSP.DLL. A third driver, called the installable ISAM, is specific to each data source. These drivers are all located in the Windows System directory.

The third driver for these applications are as follows:

     Application     Driver
     ----------------------------

     ACCESS          RED110.DLL
     Btrieve         BTRV110.DLL
     Fox, dBASE      XBS110.DLL
     Microsoft Excel XLSISAM.DLL
     Paradox         PDX110.DLL
     Text files      TXTISAM.DLL

Examples of How ODBC Works with a Single-Tiered Driver

Paradox:

1. Microsoft Query issues a query to the Paradox table.

2. The ODBC driver manager (ODBC.DLL) checks to see if the driver is available and loads the Query Engine (SIMBA.DLL).

3. The Query Engine then loads the Jet Dispatcher (MSJETDSP.DLL).

4. The Jet Dispatcher (MSJETDSP.DLL) then loads the installable ISAM driver (in this case, PDX110.DLL).

5. The installable ISAM is ready to query Paradox.

6. The Paradox table is queried.

1. Microsoft Query issues a query to the Text or Microsoft Excel file.

2. The ODBC driver manager (ODBC.DLL) checks to see if the driver is available and loads the Query Engine (SIMBA.DLL).

3. The installable ISAM driver is then loaded (in this case either

1. The Microsoft Excel file or Text file is queried.

TWO-TIERED DRIVERS (ORACLE, SQL)

The Oracle and SQL Server drivers are two-tiered drivers. Oracle and SQL Server are multi-user relational DBMSs. They contain powerful SQL engines to parse, validate, and optimize SQL statements and to retrieve the data. (Remember that the goal of client-server architecture is to divide the query process and delegate the pieces of the query to the resources best suited to handle them.) The role of the driver in this case is to translate the query request into a format (a data stream protocol) that the DBMS can understand.

The ORACLE driver enables applications to access data in an ORACLE (version 6.0.34 or later) database through the ODBC interface. It communicates with the network through SQL*Net, which must be obtained from Oracle Corporation. The example below shows the path a query would take to an ORACLE table on a LAN Manager network using the Named Pipes/Netbeui driver (SQLNMP.DLL). (Note that this article does not focus on network connections and, therefore, does not list all possible network configurations.)

Examples of How OBDC Works with a Two-Tiered Driver

Oracle:

1. Microsoft Query issues a request.

2. The ODBC driver manager (ODBC.DLL) checks to see if the driver is available, and then loads the ORACLE driver (SQORA.DLL).

3. The ORACLE driver translates the request into ORACLE language OCI (ORACLE Call Interface) and loads the OCI driver (ORA6WIN.DLL).

4. In the OCI Layer (which is similar to the SQL Server network library), the Client Router loads the appropriate SQL*Net .DLL or TSR.

1. SQL*Net Driver isolates the application from network issues and presents a common interface to the OCI Layer.

         Named Pipes/Netbeui: SQLNMP.DLL
         Novell:  SQLSPX.DLL
         TCP/IP:  SQLTCP.DLL
         DECNET:  SQLDNT.DLL
   

2. Network Software.

1. The SQL*Net Listener listens for requests coming to the database.

2. ORACLE Server.

1. Microsoft Query issues a request.

2. The ODBC driver manager (ODBC.DLL) determines if the driver is available then loads the driver.

3. SQL Server Driver (SQLSRVR.DLL).

4. Network Library (DBNMP3.DLL, DBMSSPX3.DLL, and so on).

5. Network Software (Microsoft LAN Manager, Novell NetWare, and so on).

6. SQL Server DBMS.

THREE-TIERED DRIVERS

A driver is three tiered if it sends the SQL request to a gateway process and the gateway sends it to the DBMS. An example of a gateway process involving a three-tiered configuration is an Open Data Services-based gateway that supports access to DEC RDB or IBM DB/2 databases.

For more information about ODBC compliance see the "Microsoft Query User s Guide," Appendix B, or Chapter 1 of the ODBC Programmer's Reference.

REFERENCES

"Microsoft Query User's Guide," Appendix B ODBC Driver Help Files ODBC SDK ODBC Support Forum