I sometimes need access to a database just for internal record-keeping purposes. Other times, I might need to store alarms persistently in Oracle or I might want to use Access tables to store configuration data. I need something quick to implement and, if possible, without needing much manual setup. Something based on SQL would be nice because lots of programmers are familiar with it. Microsoft's ODBC API presents a possible solution.
Microsoft's ODBC Driver Manager (ODBC32.DLL) exports several functions that are collectively known as the ODBC API. They allow transparent access to data inside ODBC Data Sources using SQL through a C API. ODBC is very convenient because most database vendors support it. I've written ODBC programs to access such databases as Access, Oracle, SQL Server, and MySQL. Visual C++ even comes with ODBC drivers that can use plain text files as data storage. Almost exactly what I need — almost.
If you take a look at the code samples that come with the ODBC API, you'll notice that the API is somewhat hard to use. The general schema seems to be something like:
Allocate environment handle
if (ok) {
Allocate connection handle
if (ok) {
Connect to data source
if (ok) {
Allocate statement handle
if (ok) {
Do some SQL
Free statement handle
}
Disconnect data source
}
Free connection handle
}
Free environment handle
}</Pre>
<P>
<p>That's too much nesting for me and too easy to forget one of those free
calls. It looks like something we could make much easier and safer in C++.</p>
<h4>Avoiding the Burden of MFC</h4>
<P>
<p>Microsoft's MFC libraries ship with two classes that make use of the
ODBC API. You can look up the CDatabase and CRecordset classes in the MSDN to
get more information on them. There are three reasons why they don't fit
the bill. First, MFC is a heavyweight library, and not everybody is ready to
burden an application with it. I didn't want my classes to be tied to MFC.
Second, the MFC classes take a very high-level approach to the ODBC API and
require some advanced features (like cursors) to be present. I've spent
some very frustrating hours trying (and failing) to make the MFC classes work
with MySQL. Third, MFC is not easy to port to other platforms (unless you use
some additional third-party product). By not using MFC, I have at least some
hope that future releases of this library might work with ODBC for Unix.</p>
<P>
<P>
<p>It is a common C++ idiom to wrap a class around a resource that needs to be allocated and freed later on. In our case, the resources are the different ODBC handles.</p>
<P>
<p>I've written two classes to wrap the corresponding ODBC API calls: <B>DataSource</B> and <B>SqlStatement</B>. Here is how they are used: </p>
<P>
<pre class="brush: cpp; html: collapse;" style="font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; border: 1px dashed rgb(153, 153, 153); line-height: 14px; padding: 5px; overflow: auto; width: 100%;">
DataSource db;
db.Connect(dsn, user, pwd);
SqlStatement st(db);
st.Execute(sql);
st.WriteResultSet(cout);
First, connect to the data source using a DSN and optionally a user name, password, and timeout. Then execute an SQL statement against the data source and print the results to cout.
As I'll explain in more detail later, these functions may throw exceptions, so it might be wise to wrap the calls in a try-catch block, like this:
try {
DataSource db;
db.Connect(dsn, user, pwd);
SqlStatement st(db);
st.Execute(sql);
st.WriteResultSet(cout);
return true;
} catch (...) {
return false;
}
The real code, shown in Listing 1, will actually do something with the exception instead of just throwing it away. Either way, the code is much clearer and much easier to get right than the raw C API. There is no way to forget calling all of those free calls. The destructors will do it for us, even in the presence of exceptions.
Error Handling
For most functions in the SqlOdbc library, I decided to throw an exception if the underlying ODBC API reported an error. This is easily done, because most ODBC functions have the same return values, and I had already wrapped each low-level ODBC call with a function that checked this value. I only had to change the CheckStatus function in sqlobdc.cpp (available online--jan2002.zip/guisado.zip) to throw an exception in case of error.
ODBC functions have two different return codes that indicate success: SQL_SUCCESS and SQL_SUCCESS_WITH_INFO. The first one is unconditional success, everything right. The second one means that the operation was completed, but something happened that you should know about. A good example would be trying to retrieve some data that's too long for the space allocated to it. In that case, ODBC returns success with info and dutifully reports that the data has been truncated.
The SqlOdbc classes throw an exception if the result code is neither SQL_SUCCESS nor SQL_SUCCESS_WITH_INFO, but in the second case we need to know what happened. The current implementation (see DataSource::TraceError in sqlodbc.cpp) uses the OutputDebugString API function to display the information associated with the SQL_SUCCESS_WITH_INFO return code.
The exception I throw inherits from std::exception, which allows using a catch clause to target both ODBC errors and STL exceptions thrown by the STL classes I use in the implementation. I've redefined the exception::what function to show the ODBC error reported by the API. The exception also includes all the information returned by the ODBC API, so that you can make your own decisions based on the particular error codes.
Connect and Execute Errors
These two functions are special in that they take a string as input, which has possibly been specified by the user and might therefore contain errors. At first I thought it would be better not to throw an exception, but later on I thought that this library is actually targeted at applications that might be running without user interface at all (e.g., just console-based non-MFC applications). In this type of application, the connection parameters are normally specified through command-line options or registry settings, and if the settings are incorrect, that's serious enough to warrant throwing an exception. I'm happy about that decision. The resulting code reads much easier:
try {
DataSource db;
db.Connect(dsn);
SqlStatement st(db);
st.Execute(sql);
st.WriteResultSet(cout);
return true;
} catch (const exception& ex) {
cerr << ex.what() << endl;
return false;
}
A Command-Line Utility Using SqlOdbc Classes
I've written sqlodbc.exe, a small utility (around 40 lines of C++) that uses the SqlOdbc classes to execute SQL statements from the command-line. The command-line syntax is simple:
sqlodbc dsn "select * from t1"
Just specify a valid data source name and an SQL string to run on the data source. The output of this command might look like this:
col1 col2 col3 0 test100 1969-12-06 1 test101 1970-12-06 2 test102 1971-12-06 3 test103 1972-12-06 4 test104 1973-12-06 5 test105 1974-12-06 6 test106 1975-12-06 7 test107 1976-12-06 8 test108 1977-12-06 9 test109 1978-12-06 10 test110 1979-12-06
sqlodbc.exe (available online) is a console program and has been very useful to me because it allows running queries, inserts, and updates against an ODBC database without any special set up. (Of course you'll need to install the ODBC drivers that come with your database of choice.)
The full code required to implement this utility, main.cpp, is shown in Listing 1. sample.bat (available online) is an example batch file that uses the program to create a table and insert some data into it.
Getting Information About Your Tables
Apart from allowing the typical CREATE, INSERT, SELECT, UPDATE, and DELETE SQL commands through the SqlStatement::Execute method, I've found it useful to be able to get some information about the structure of the database you're working with (metadata you could call it). There are two ODBC functions called SQLTables and SQLColumns and the corresponding methods in SqlStatement are Tables and Columns. With Tables, you can get the names of all the tables in a particular data source. With Columns, you have to specify the name of a table and the function provides you with information about the fields for that table. Here are two usage samples:
DataSource db;
db.Connect(dsn);
SqlStatement st(db);
st.Tables("TABLE");
st.WriteResultSet(cout);
DataSource db;
db.Connect(dsn);
SqlStatement st(db);
st.Columns("t1");
st.WriteResultSet(cout);
If you look again at the code for the main function of the sqlobdc utility, you'll see that if you call it with just a data source name as argument (something like "sqlodbc my_dsn"), it will print to cout the result of calling the SQLTables function. The Tables function takes an argument specifying the type of tables to display. The values are strings like "TABLE", "VIEW", or "SYSTEM TABLE". You can also specify several table types by joining them like this "TABLE, VIEW".
Fine-Tuning the Error Output
The ODBC components on your system will probably be in your native language. That is, an error message from your Access ODBC driver on a Spanish NT box will probably be in Spanish. This means the error messages will use extended ASCII characters to represent accented or other language-specific characters. Let's try this code inside a console application:
try {
DataSource db;
db.Connect(dsn);
SqlStatement st(db);
st.Execute("nothing");
} catch (const exception& ex) {
cerr << ex.what() << endl;
MessageBox(0, ex.what(),
"Error", MB_OK);
}
The code tries to show the message using two different methods. Curiously enough, both messages don't look the same on my Spanish NT box. In the cerr version, all the accented characters look wrong.
The explanation is that Windows uses different codepages for GUI and console applications, and each codepage uses different ASCII codes for the extended characters. The ODBC messages use the Ansi codepage that is normal for Windows GUI applications. That's why MessageBox does OK, but cerr fails as it is using the Oem code page typical of console applications.
To fix this problem, the following code snippet (from DataSource::GetError in sqlodbc.cpp) detects if we are inside a console application and converts the message from the Ansi to the Oem codepage.
#ifdef _CONSOLE
::AnsiToOem(
(const char*)lpszMsg,
(char*)lpszMsg);
#endif
Simpler Than CRecordset
Both classes add up to 500 lines of code, of which over 100 are comments. Compare this to the much more comprehensive MFC CRecordset classes, where the dbcore.cpp source file already has almost 5000 lines of code. With these classes, you'll have easy access to ODBC data sources. ODBC is already installed on most desktops, so there is not much to do there. When you install your database of choice, you might have to tell the setup program to install the ODBC drivers, too. Apart from that, the SqlOdbc classes also allow creating and destroying data sources on the fly, so no manual data source setup is needed.
Ernesto Guisado works as C++ developer in the UK. You can contact him at [email protected] or visit his web site at http://triumvir.org.
