Brief: Every ODBC driver supports a different level of functionality. Applications that use ODBC often depend on a certain level of capability from the driver. Unfortunately, which driver is used isn't necessarily up to you. This article presents information to help you code applications to decide if a driver will work and, if so, to use that driver to its maximum potential.

Have you ever watched the activity around an airplane before you take a commercial flight? The pilot always does a walk around or pre-flight check. The purpose is to make sure that there aren't any obvious problems with the airplane that would make flight unsafe. Because the pilot is ultimately responsible for the safety of the passengers, you will always see him do this personally.

As the programmer-or pilot-of your application, you are responsible for taking reasonable measures to protect the users from problems and bugs. When you write a client/server application that uses open database connectivity (ODBC), your pre-flight checks should include the ODBC driver.

ODBC is Microsoft's standard for accessing databases. Microsoft recognized that databases are not all created equal; different databases have different capabilities. For example, not all data-bases support outer joins, which allow you to join files and still have records returned when matching records might be missing in one of the files. The trick for Microsoft was to create a standard that allows vendors to supply drivers for almost any database.

ODBC Flight Manual

When Microsoft created the ODBC standard, they knew you would run into the dilemma of varying database support and designed the standard to help you handle those situations. The ODBC standard takes the differences between databases into account by allowing different levels of conformance.

In ODBC, there are two major types of conformance. The first is ODBC application program interface (API) conformance. The ODBC Version 2.0 standard defines 56 functions divided into three groups: core, level 1, and level 2.

The second major conformance type is SQL grammar. Like API conformance, there are three levels of SQL grammar conformance: minimum, core, and extended SQL grammar. Each level of SQL grammar conformance supports additional features in four areas: SQL data definition language (DDL), SQL data manipulation language (DML), expressions, and data types. The "ODBC Conformance" sidebar explains some of the details behind each conformance type.

A pilot doesn't do the maintenance on an airplane, and that's one of the reasons for a pre-flight check. Sometimes the user selects the ODBC driver, so you need to know how to test a driver to see if it supports the functionality you need. This article also focuses on helping you understand how to write programs to handle the different conformance levels. That way, your application won't try to take off without, say, the wings attached.

Microsoft defined a couple of functions in the standard to help resolve conformance issues: SQLGetInfo and SQLGetFunctions. I'll discuss these functions and supply you with a utility program to demonstrate how to use them. The program allows you to connect to a driver and displays some of the information you should gather in your programs. Pre-flighting your ODBC driver will help you write client/server programs that crash less often, work with more ODBC drivers, and end up making your passengers (I mean users!) much happier.

Wings, Propellers, and Conformance

No matter what you decide based on your application, don't select a driver unless it supports at least API conformance level 1. Most applications (for example, Microsoft Access) and many languages require level 1 conformance, whereas most don't require level 2 conformance. As for SQL grammar conformance, I would say that core level compliance should be your minimum standard.

Conformance support isn't an absolute. If a driver supports level 1 API conformance, for example, that driver may also support many of the level 2 API functions. The driver vendor shouldn't claim level 2 conformance, although the driver may support all the level 2 functions you need. As a word of warning, the ODBC standard operates on the honor system. I've seen drivers that report API level 2 compliance, yet don't support all level 2 functions.

In some situations, a database doesn't support some of the functions in a given conformance level. The driver should mimic the ability. If a back-end database doesn't support unions, the driver can implement the union functionality in its code. The driver vendor may choose to implement functions beyond those described in the conformance levels, when the back-end databases support them. You can find out about those functions in the driver documentation.

Some Microsoft Visual Basic (VB) programmers are probably wondering what ODBC functions and SQL grammar have to do with them. You can write sophisticated VB programs that access a database using ODBC without ever calling an ODBC function or writing a single SQL statement. The same holds true for Microsoft Access.

These languages and applications use ODBC, but can insulate you from the API coding; the API calls and SQL statements are still running underneath the covers. So a VB or Access programmer needs to keep ODBC conformance levels in mind just as much as a C programmer does.

What's the payoff? By determining the conformance level, you can write programs that use the driver to its maximum potential. If a driver supports unions, you might speed up your application by using them instead of running two SQL Select statements. You might decide to require that the driver supports unions or you may choose to write your application to support both drivers that do and don't support unions. This way the applications still work in situations where unions aren't available, but give the speed advantage to users who pick drivers with union support.

ODBC Flight 2 Now Boarding at Bill's Gate 1

In the introduction, I described SQLGetInfo and SQLGetFunctions-tools you need to support any ODBC driver. The SQLGetInfo function returns a wide variety of information about a driver including conformance, the driver version, the server name, the term used natively to refer to a table (e.g., file), and whether the driver supports outer joins, to name a few.

To use the SQLGetInfo function, you call it, passing an ODBC connection handle and a value that indicates what information you want returned about the driver. Microsoft's ODBC software development kit (SDK) has C include files which contain the constants for the various information types. The include files are SQL.H and SQLEXT.H. 1 illustrates the syntax for the SQLGetInfo and SQLGetFunctions function calls.

To use the SQLGetInfo function, you call it, passing an ODBC connection handle and a value that indicates what information you want returned about the driver. Microsoft's ODBC software development kit (SDK) has C include files which contain the constants for the various information types. The include files are SQL.H and SQLEXT.H. Figure 1 illustrates the syntax for the SQLGetInfo and SQLGetFunctions function calls.

To find out what API conformance level a driver has, pass SQLGetInfo the constant SQL_ODBC_API_CONFORMANCE supplied in the SQLEXT.H include file. The function returns a 16-bit integer value that tells the program the conformance level. Requesting other information about a driver returns different types of data. For example, request the database name and the function will return a string; request information about how the driver handles transaction isolation and the function will return a 32-bit integer that tells the program if the driver supports read uncommitted, read committed, and repeatable read.

SQLGetFunctions works in a similar way. For example, pass the function a constant that represents a specific API function (e.g., SQL_API_EXECUTE). The function will return a true or false flag depending on whether or not the driver supports the function.

The program can also pass the function the constant SQL_API_ALL_FUNCTIONS. In that case the SQLGetFunctions returns an array of values that indicate whether or not the driver supports each function. The program then uses the same constants (e.g. SQL_API_EXECUTE) as an offset to the array to test whether the driver supports the function (you'll see an example of this in the utility program).

For Those of You Who Always Want to Know How an Airplane Flies

To save you some effort, I have created a utility which displays some important information about an ODBC driver. You can obtain a copy of this utility by downloading and running the self-extracting file GETINPKG.EXE from the files area of MC-BBS. Once the executable file (GETINFOC.EXE) is on your system, create a Windows program item for the program. Then double click the icon to execute the application.

Once you run the program, it displays a dialog box with a list of installed ODBC drivers (See 2). Double click on one of the drivers listed and the program prompts for any additional information needed to attach to the ODBC driver, such as user name and password. 3 shows the connection window for the AS/400 ODBC driver from HiT Software.

Once you run the program, it displays a dialog box with a list of installed ODBC drivers (See Figure 2). Double click on one of the drivers listed and the program prompts for any additional information needed to attach to the ODBC driver, such as user name and password. Figure 3 shows the connection window for the AS/400 ODBC driver from HiT Software.

Once the program attaches to a driver, the application will query the driver for certain parameters and display them in a window (see 4). When you want to exit the program, double click the button in the upper left hand corner of the window or choose the File menu and select Exit.

Once the program attaches to a driver, the application will query the driver for certain parameters and display them in a window (see Figure 4). When you want to exit the program, double click the button in the upper left hand corner of the window or choose the File menu and select Exit.

I wrote the utility using Microsoft Visual C++ Version 1.5. In spite of appearances, it is a Windows program. Normally in a Windows C program, an experienced Windows programmer would expect to see a WinMain function, but I used a feature in Visual C++ called QuickWin. QuickWin creates a Windows program without having to write the supporting code. I wanted to demonstrate how to use the APIs, not how to write a Windows program.

If you choose to key the program in, you should call the project GETINFOC and link it to the ODBC.LIB file. The ODBC SDK and the Windows SDK-which you can get from Microsoft-have some of the include files that you will need. Visual C++ supplies the STDLIB.H and STDIO.H include files you need.

Let's Lift the Cowlings Off Those Engines and Take a Peek Inside

Now I'm going to walk you through the code. The first thing you'll see in 5 is five #include compiler directives. For the program to compile correctly, it needs some of the constant and function declarations in those include files. You will also see a series of variable declarations; the include files have definitions of all of the variable types used.

Now I'm going to walk you through the code. The first thing you'll see in Figure 5 is five #include compiler directives. For the program to compile correctly, it needs some of the constant and function declarations in those include files. You will also see a series of variable declarations; the include files have definitions of all of the variable types used.

First, the program makes three malloc function calls. Malloc is an ANSI C function that allocates memory. I use this function because I need to allocate a specific amount of string memory for later use.

Then the program uses the Windows API FindWindow function. The purpose of the FindWindow function is to find a window and return the handle to that window. The handle is a way of uniquely referencing a specific window-kind of like an address-and one of the ODBC functions that I use later will need the handle of the window as one of its parameters.

At this point, the program starts the process of actually connecting to an ODBC driver. There are three steps involved: allocating the environment handle using the SQLAllocEnv function, allocating a connection handle using the SQLAllocConnect function, and connecting to a driver using either the SQLConnect function or the SQLDriverConnect function. Each of the functions has an equivalent function to disconnect from the driver or free the handle: SQLDisconnect, SQLFreeConnect, and SQLFreeEnv. See 6 for a chart detailing the relationships of these function calls.

At this point, the program starts the process of actually connecting to an ODBC driver. There are three steps involved: allocating the environment handle using the SQLAllocEnv function, allocating a connection handle using the SQLAllocConnect function, and connecting to a driver using either the SQLConnect function or the SQLDriverConnect function. Each of the functions has an equivalent function to disconnect from the driver or free the handle: SQLDisconnect, SQLFreeConnect, and SQLFreeEnv. See Figure 6 for a chart detailing the relationships of these function calls.

I used the function SQLDriverConnect-the function to connect to an ODBC driver-that needs the window handle. Passing the function a null value for the connect string tells the driver manager that the program wants the user to select an ODBC driver. The program automatically displays a series of dialog boxes that allow the user to select an installed driver and connect to the database. (For an explanation of drivers, driver managers, and other related issues, see chapter 1 of the Microsoft ODBC 2.0 Programmer's Reference manual.)

After the program successfully connects to a driver, it calls the SQLGetInfo function four times, returning the name of the driver (the dynamic link library (DLL) file name), the driver version, the API conformance level, and the SQL grammar conformance level.

If the return code indicates that the program was able to get the driver name and driver version, it prints those to the window. If the program was able to get the API conformance level, it finds out what level the driver is and prints a text description. The program does the same thing for SQL grammar conformance.

If the driver is API function core level or level 1 compliant, the program calls the function SQLGetFunctions. The program has the function return an array with each element being a true or false value indicating whether the driver supports the corresponding API. I had the program test the functions SQLDrivers, SQLTable-Privileges, and SQLBrowseConnect, which are API level 2 functions. If the driver supports any of the functions, it prints a line for each one it supports.

At this point, the program is done and just needs to clean up after itself. The program calls the SQLDisconnect function to disconnect from the driver. It then calls the SQLFreeConnect and SQLFreeEnv functions to free the memory associated with the connection handle and the environment handle. The program also deallocates the string storage (using the free function) allocated earlier in the program, when it called the malloc function several times. The program ends at the return statement.

Have Many Safe and Happy Flights

The best place to go for more information on this topic is the Microsoft ODBC 2.0 Programmer's Reference manual. It contains a complete list of all the functions and SQL grammar you can test for using the SQLGetFunctions and SQLGetInfo functions. It also discusses additional considerations concerning compliance.

You've seen a C language example of how to use these two powerful functions, but the information applies to most other ODBC capable languages. The program can be translated to Visual Basic, for example. For additional information on translating C function calls to VB, see "PC Support Windows APIs," MC, November 1994.

It's surprising how easy it is to prevent many of the problems you run into when using ODBC. By adding just a few lines of code to your application, you can ensure that the people using the applications you create will be happy to fly the friendly skies of Information Systems Airlines.

Jim Hoopes is a senior technical editor for Midrange Computing.

Reference Microsoft ODBC 2.0 Programmer's Reference.

ODBC Conformance I'm going to discuss API conformance first. If your driver doesn't support the core API functions, you don't really have an ODBC driver, you have a proprietary driver. The core APIs comprise 22 of the 56 API functions and include allocate environment (SQLAllocEnv), execute an SQL statement (SQLExecute), and get a result row (SQLFetch).

Level 1 functions comprise 16 more of the 56 API functions and level 2 functions make up the remaining 18. Examples of level 1 functions include get information about supported data types (SQLGetTypeInfo), get the column names in a table (SQLColumns), and get a list of tables (SQLTables). Examples of level 2 functions include get a list of the installed drivers (SQLDrivers), return the native SQL statement (SQLNativeSql), and get a list of columns that make up the primary key for a table (SQLPrimaryKey).

For a vendor to claim conformance to a given API level, it's driver must support all functions in that level. Even if a driver supports 99 percent of the functions in level 2, it is still not a level 2 driver. The driver must also support all the functions in the previous conformance levels. If a driver vendor claims level 1 conformance, according to Microsoft's standard the driver must also support all core functions. Each API conformance level builds on the previous level.

The second type of conformance is SQL grammar. The minimum SQL grammar conformance level supports, for example, the CREATE and DROP TABLE data definition language (DDL) statements. The data manipulation language (DML) support includes statements like simple SELECT, INSERT, and UPDATE. The expressions supported include simple arithmetic and logical expressions. The data types supported include character (CHAR) and variable character (VARCHAR).

For a driver to be core SQL grammar compliant, it must support all of the minimum SQL grammar and data types. Additional DDL support includes ALTER TABLE, CREATE INDEX, and GRANT. The DML support includes the full SELECT statement. The expressions supported at the core level include sub-queries and functions like average (AVG), and maximum value (MAX), among others. The variable types supported include INTEGER, FLOAT, and REAL.

At the extended SQL grammar level, the driver must support all minimum and core level SQL grammar and data types. The additional DML support includes unions and positioned DELETEs, SELECTs, and UPDATEs. Supported expressions include date, time, and timestamp literals, and functions like absolute value (ABS) and substring (SUBSTRING). The data types supported include BIT, BINARY, and TIMESTAMP.

The ODBC Pre-flight

Figure 1 SQLGetInfo and SQLGetFunctions Syntax

 RtnCode=SQLGetInfo(HDBC, UWORD, PTR, SWORD, SWORD FAR *); HDBC: ODBC Connection Handle UWORD: ODBC Information Type PTR: Pointer To The Value Returned SWORD: Maximum Length Of The Return Value SWORD FAR *: The Number Of Bytes Returned By The Function RtnCode=SQLGetFunctions(HDBC, UWORD, UWORD FAR *); HDBC: ODBC Connection Handle UWORD: ODBC Function UWORD FAR *: TRUE or FALSE return value 
 
 The ODBC Pre-flight
 
 Figure 2 Select Driver Dialog Box
 
 UNABLE TO REPRODUCE GRAPHICS 
 
 The ODBC Pre-flight
 
 Figure 3 Sample ODBC Connection Window
 
 UNABLE TO REPRODUCE GRAPHICS 
 
 The ODBC Pre-flight
 
 Figure 4 The ODBC Driver Information Window
 
 UNABLE TO REPRODUCE GRAPHICS 
 
 The ODBC Pre-flight
 
 Figure 5 GETINFOC.C Program
 
 All components for this utility are contained in the self-extracting file: GTINFPKG.EXE This file can be found in the download area of the MC-BBS. 
 
 The ODBC Pre-flight
 
 Figure 6 ODBC Connection Function Calls
 
 UNABLE TO REPRODUCE GRAPHICS