Features

Wrapping the Windows ODBC API

Giovanni Bavestrelli

Who needs yet another layer of software between program and database? Maybe you do.

ODBC (Open Database Connectivity) is a C-language API that allows programmers to write applications that are independent of any particular DBMS (Database Management System). The ODBC standard defines an API; and database/driver vendors who want to support the API implement ODBC drivers for a particular DBMS. In Windows, most relational databases today support the ODBC standard. ODBC uses a standardized version of SQL (Structured Query Language) to interact with the underlying DBMS. If you program using ODBC, you should, with some effort, be able to access any database that has an ODBC driver [1]. ODBC allows you to build applications that work on many DBMSs. Recompiling your application to access a different database is unnecessary; your application can dynamically load the correct ODBC driver at run time.

ODBC alone is no longer the hottest database access technology for Windows programs. Microsoft has developed a number of database access technologies over the past few years, including the MFC ODBC classes (CRecordset and CDatabase), DAO, RDO, OLE DB, and ADO. Many OLE DB providers, though not all, still use ODBC. ODBC remains vital for Microsoft's connectivity strategy. If you need to access a DBMS and want a simple and fast solution that gives you total control with SQL, ODBC is still a good way to go. A small wrapper can take away most of the complexity of the API without adding any significant overhead.

This article presents a small set of simple C++ classes that encapsulate some tedious details and peculiarities of the ODBC API and make the C++ compiler work for you to provide an easier and more intuitive interface. These simple C++ classes are thin wrappers. They don't change the programming metaphor, like DAO, RDO, OLE DB, ADO, and even MFC's ODBC classes; they don't use COM; and they don't throw exceptions. You will still have to write SQL statements yourself. This is the main reason I chose to develop these classes instead of using MFC's ODBC classes. I wanted to have total control of the SQL statements [2]. These classes make it simpler to work with ODBC, in a number of ways:

A secondary effect of these classes is that ODBC API functions, which originally are all at the same level, get grouped together into the areas in which they belong: environments, connections, or statements.

An Approach to Encapsulation

In Windows, ODBC operations seem to revolve around Windows handles. In a typical database access, using ODBC 2.0 requires first allocating an ODBC environment with a call to SQLAllocEnv, which will return a handle of type HENV. The application needs only one of these handles to establish a link between the program and ODBC. Using this handle, you must create a connection handle of type HDBC for each database to which you want to connect, by calling SQLAllocConnect. This connection handle will be used to connect to the database, using SQLConnect or SQLDriverConnect. This connection handle must also be passed to SQLAllocStmt to allocate each statement handle, of type HSTMT, which will be used to call other ODBC APIs to do the real work with SQL. Upon completion, you must free statement handles with a call to SQLFreeStmt, free connection handles with a call to SQLFreeConnect, and free environment handles with a call to SQLFreeEnv.

All of these handles call for encapsulation. When I see a handle in a C API, I try to think of a C++ class. If a good class is not available, then I develop one. C++ classes, with constructors, destructors, data members, encapsulation, inline functions, function overloading, and default arguments can hide some details without adding significant overhead.

To stay close to the ODBC API, my main abstractions are classes ODBCEnvironment, ODBCConnection, and ODBCStatement (see Figure 1). These classes are simple wrappers around the relative environment (HENV), connection (HDBC), and statement (HSTMT) handles. To keep these wrappers thin, most of the member functions just encapsulate the ODBC API functions of the same name (except for the SQL prefix of the ODBC API functions, which are consistently dropped from the member function names). Keeping the same names means you can refer to the ODBC documentation to figure out what the member functions do. However, you still have to know how ODBC works (just as using MFC's CWnd class requires that you know what a window is and how to use it).

Classes ODBCEnvironment, ODBCConnection, and ODBCStatement

Each application needs only one environment handle, so class ODBCConnection contains a static ODBCEnvironment object. That's about all there is to say about the ODBC environment. Class ODBCConnection handles it automatically through ODBCEnvironment's interface. That leaves classes ODBCConnection and ODBCStatement.

Class ODBCConnection is very simple. Its main member functions are Connect and Disconnect for connecting and disconnecting to and from a database. To connect to a database, you must supply a database connection string, like "DSN=TRYODBC;UID=USER;", where DSN stands for Data Source Name, UID for User ID, PWD for Password, etc. Other ODBCConnection member functions can be used to set options, to get information relative to the connection, or to handle transactions. Classes ODBCEnvironment and ODBCConnection get rid of a little complexity, plus automatically handle errors.

ODBC statements do the real work of ODBC. The most common operations done with ODBC statements are compiling and executing SQL statements, setting parameters in the SQL statements, fetching records from the result set of a SELECT query, and retrieving values from the columns of the fetched records. To do any of these, an ODBC statement object must first be created, which is easy enough:

ODBCStatement Sql;

This ODBC statement, named Sql, is not bound to any database. To bind it to a particular database, you can either pass an ODBCConnection object to the constructor of the ODBCStatement or pass it to the ODBCStatement::AllocStmt member function if you want to know the result of the allocation. Either way, if the ODBCConnection object has been successfully connected to a database (with the ODBCConnection::Connect member function), the ODBC statement is ready for use. At this point, you can set some statement options (with the SetStmtOption member function), and you're ready to use SQL [3].

You can now run your SQL command by using the Prepare and Execute member functions or by using ExecDirect, which does both actions in one step. If you use the Prepare and Execute functions, you can call Prepare only once, but then call Execute many times with different parameters, increasing performance.

As described in the sidebar, "Simple SQL Statements," the main SQL commands are SELECT, INSERT, UPDATE, and DELETE. SELECT is different from the other three in that it generates a result set, which is a set of records that allows you to extract the requested data.

The following code does a simple SELECT statement using the ODBC classes:

void DoSomethingWithData(int, float, char *);

void DoSimpleSelect()
{
   ODBCConnection Dbms;
  
   if (!Dbms.Connect("DSN=TRYODBC;UID=USER;"))
      return;
 
   ODBCStatement Sql(&Dbms,"DoSimpleSelect");
          
   char * SqlStatement =
      "SELECT [IntegerField], [FloatField],
      [CharField] FROM [TryODBC]"; // broken to fit on page
 
   int   IntegerField;
   float FloatField;
   char  CharField[256];

   Sql.BindCol(1,&IntegerField);
   Sql.BindCol(2,&FloatField);
   Sql.BindCol(3,CharField,sizeof(CharField));

   if (Sql.ExecDirect(SqlStatement))
      while (Sql.Fetch())
        DoSomethingWithData(IntegerField,FloatField,CharField);
}

Note that this code had no error handling, since it is all done inside the ODBC classes. If a warning or an error is encountered, a function that you provide is called, passing information about the event that occurred. The optional second argument to the ODBCStatement constructor is part of this information. I use this to identify the name of the function where the error occurred.

Most member functions in the ODBC classes, such as BindCol, ExecDirect, or Fetch, return Boolean values. The BindCol function binds a buffer of a specific data type to a column in the result set. After a successful fetch, these buffers will contain the retrieved data. The compiler selects the type of data, since BindCol is overloaded for many of the allowed types. Make sure these buffers remain valid until you have finished retrieving data from the result set. GetData, an alternative to BindCol, puts the data into a buffer that you provide after you have fetched the record. Both BindCol and GetData have an optional last parameter, pcbValue, that can be used to retrieve extra information from the field, such as whether or not it is NULL. This parameter is passed directly as the last parameter of the ODBC API functions SQLBindCol and SQLGetData. (See the documentation for these two functions for an explanation on how to use the parameter.) GetData is one of the few member functions that does not return a Boolean. Instead, GetData has a more intelligent return type. It can be used to retrieve data from a field one piece at a time, so you need to know if more data is available after your call. An overloaded GetData function that takes a CString * as a parameter retrieves all the data automatically, by calling SQLGetData repeatedly until the entire string has been retrieved.

The previous code did not use parameters in the SQL statement; here is an insert statement that does:

BOOL DoSimpleInsert()
{
   ODBCConnection Dbms;
  
   if (!Dbms.Connect("DSN=TRYODBC;UID=USER;"))
      return FALSE;

   ODBCStatement Sql(&Dbms);

   char * SqlCommand="INSERT INTO [TryODBC]
      ([IntegerField], [FloatField], [CharField])
      VALUES (?,?,?)"; // broken to fit on page

   int IntegerField=35;
   float FloatField=180.0F;
   char * CharField="Eric"; 

   Sql.BindParameter(1,&IntegerField);
   Sql.BindParameter(2,&FloatField);
   Sql.BindParameter(3,CharField);

   return Sql.ExecDirect(SqlCommand);
}

BindParameter works similarly to BindCol; it associates a buffer of a particular data type to a parameter in the SQL command. BindParameter is overloaded for many of the allowed data types, and the buffer must remain valid until the SQL command has been executed. Whereas BindCol defines a buffer from which data will be retrieved, BindParameter defines a buffer that must contain the data for the parameter when the command is executed. Note that the contents of the buffer at the time BindParameter is called are unimportant; it is the contents just before the SQL command is executed (in Sql.ExecDirect here) that count. Keep this in mind when you set the parameters. BindParameter also has an optional last argument, pcbValue, which is passed directly to the SQLBindParameter API function. For a description of the parameter's use, see the documentation of SQLBindParameter.

There are many more member functions in the ODBCStatement class than described here, but most of them are just simplifications of the related ODBC API functions of the same name (without the SQL prefix). If you look at the ODBC documentation, you will find these functions easy to use.

Other Features of the ODBC Classes

Managing ODBC Types

ODBC defines many types, such as SWORD, SDWORD, RETCODE, etc. Where possible, I prefer to deal with standard Windows types, such as int, long, BOOL, etc. It makes the interface more readable and makes things simpler for users. Where possible, I used the standard types in the interface code, but left the ODBC types in the implementation code. A parameter that has a type short in the interface declaration might have the same parameter defined as SWORD in the function implementation. This is certainly a questionable approach, but I like it because the compiler will tell me when my assumption about any two types actually being the same is no longer valid. Simple types in C have a notorious problem: their size and signedness are not strictly defined by the language. Consequently, I had to use #ifdef WIN32 in some situations.

In class ODBCStatement, functions BindCol, GetData, and BindParameter are overloaded for many types, but I did not overload them for binary data (LPVOID). Instead, I defined a function of a different name (i.e., BindParameterBinary) that takes an LPVOID. This way is safer, because you are forced to be explicit when using binary data. A similar approach is also used for very long strings.

One word about dates. ODBC uses the structure TIMESTAMP_STRUCT as an exchange buffer for date and time data, so this structure will have to be used in BindCol and BindParameter statements when dealing with date/time. If you normally use MFC's CTime, two simple functions can do the conversion: CTime_to_TIMESTAMP_STRUCT and TIMESTAMP_STRUCT_to_CTime. If you use GetData, there is an overloaded function that works directly with CTime.

Error Handling

ODBC defines various return codes from API functions. I use mostly BOOL in my classes. The various return codes are divided into errors and warnings. The error/warning information is sent to the following function, which must be defined in your code or the program will not link:

BOOL
ReportODBCProblem(BOOL isError,
   LPCSTR ApiFunction,
   LPCSTR ErrorDescription,
   LPCSTR State,LPCSTR Retcode,
   LPCSTR LastCommand,LPCSTR Extra,
   long NativeError,long Row);

This function will receive the error/warning status, the name of the ODBC API function that caused the error, the ODBC driver error description, the ODBC error state, the ODBC API return code, the last SQL command compiled, the native error of the DBMS, the row number in the recordset (where meaningful), and some additional information. I usually display this information or log it somewhere. It is possible to disable this error reporting for an object by calling EnableErrorHandling, passing FALSE as a parameter. For convenience, some functions have a BOOL parameter named DisableErrorNotification that will disable error handling only for that particular function invocation. It is also possible to get some information about the previous error by calling some of my classes' member functions.

Using the ODBC Classes

Using these ODBC classes in your applications is very easy. Include ODBCGIO.H in your source files where you want to use the classes and ODBCGIO.CPP in your project. You will also need to define function ReportODBCProblem to log or show the errors. If you are creating your application with App-Wizard, select "no database support," because the App-Wizard database support creates MFC's ODBC classes. My classes also tell the linker to link with ODBC32.DLL (or ODBC.DLL for 16-bit Windows).

A sample application, which is available for download from the CUJ ftp site (see p.3 for downloading instructions), uses these classes to do simple SQL queries (see Figure 2). Before running the sample application, you must register the TRYODBC System Data Source name by using the ODBC 32 application in Control Panel. (The Microsoft Access database TRYODBC.MDB is included with the source code of the sample application.)

Conclusion

ODBC is still a very useful API, but it is a bit tedious to use. A simple wrapper can take away most of the complexity without sacrificing performance or changing the programming paradigm. The wrappers presented here do not wrap the whole of ODBC, but the classes expose the ODBC handles so that ODBC API functions can be called easily while working with these classes. Since the level of abstraction of these classes is similar to the raw ODBC API, it would be very easy to extend them to wrap other parts of the API that users need to access. Indeed, the main advantage of these classes is simplicity.

The presented classes use ODBC 2.0. Visual C++ 6.0 was shipped with ODBC 3.0, and the new online help for many ODBC 2.0 functions states that these functions have been deprecated in the new version of ODBC. Still, if you look at the source code of the CRecordset class (Microsoft's ODBC wrappers) shipped with Visual C++ 6.0, you'll find that it still uses ODBC 2.0.

Notes

[1] I say "with some effort" because ODBC is no panacea; it does not hide all the peculiarities of each DBMS. If you try to build an application that is meant to be truly independent of any particular DBMS, you will run into table and column naming conventions. Some DBMSs accept spaces in table/column names; others don't. Some have limitations on the length of such names. Case sensitivity might be handled differently. Some data types might not be fully supported on certain DBMSs.

[2] This is not to say that you should not use CRecordset classes. They are very well implemented, robust, and well-documented. However, if you would like more direct control and a smaller overhead, but do not want to tamper with the raw ODBC API, my classes might be just what you need.

[3] In my opinion, one of the main advantages in using these classes is that they let you write your own SQL statements. If you don't feel very comfortable with SQL, you can use the Microsoft Access query designer and then view the SQL, copy it, and paste it into your code.

Acknowledgments

Thanks to Lodovico Gandini for giving me the first serious opportunity to work with this wonderful language.

Giovanni Bavestrelli lives in Milan and is a software engineer for Techint S.p.A., Castellanza, Italy. He has a degree in Electronic Engineering from the Politecnico di Milano, and writes automation software for Pomini Roll Grinding machines. He has been working in C++ under Windows since 1992, specializing in the development of reusable object oriented libraries. He can be reached at giovanni.bavestrelli@pomini.it.