This article is about a simple ODBC library (DLL) which can be used in MFC or non-MFC applications for data-source access and executing SQL statements, without knowing the nature of the data-source (MS Access, FoxBase, SQL Server, MySQL etc.) for which an ODBC driver is available. Today, an ODBC driver is provided for all major database systems.
There is a huge heap of different solutions implemented just to perform this task. One can find them here on CodeProject but also on the Internet (some free, and some are not). MFC solutions for this task are the well-known
CRecordset classes which give us a lot of freedom when working with ODBC data-sources and are well documented in the MSDN. So, now comes the question, why use this library when you already have these classes? The fact is, if you work under MFC, you would probably be satisfied since you have all things served, but what if you don't? Next, for each table in the database you want to access, you will have to connect your project to it and the Class Wizzard will add a new class to your project each time. But what if I have a database with a 100 or more tables? And, the most important thing (the reason why the DataLayer library was developed), what if you modify your tables during the development of the project? You will probably have to hand-code all modifications to the generated classes that represent the database tables. It can be very boring, right?
What if you want to connect to some data-source at run-time and you want to know what tables there are, what columns the tables have, and finally, take a look at a different type of data that is inside the table rows? Maybe, you would like to create tables or alter them (or delete) at run-time?
You would need a tool for such data-source access and it is not a pleasant thing to use the ODBC SDK in your everyday job. This library represents such a wrapper, so you could perform all database tasks with very little effort. Sure, you must be familiar with the SQL syntax in order to execute queries.
Note about class naming inside the DataLayer Library
Please note that there isn't any good reason why I picked these names starting with _Z in the implementation. So, you will meet
_ZTableInfo classes and structures. Anyway, if you don't like it, you are free to rename it.
Using the code
To use this DLL library in your projects, you will have to include a header file DataLayer.h and copy the compiled module named DataLayer.dll where your executable output is. Or, you can copy it to the Windows folder. Anyhow, now you will be able to instantiate some (but not all) classes from the DataLayer library. The last thing you should do is link your project to the static library DataLayer.lib. You'll find the compiled library modules in the download section (above), and a test project with no additional modules, so you'll have to download both to build the test solution. Or, you can download just the compiled modules and build your own solution. You'll also find there a link to the source of the library to compile it yourself.
Connect to the data-source
There is a simple class called
_ZConnection that will provide you this:
// Connect to the data source
BOOL bConnected = zConnection.Connect(dsn, user, authorization);
Arguments in this call are the ODBC data-source name (
dsn), username (
user), and an authorization (
authorization). the method will return
TRUE if the connection is established or
FALSE in the other case. You are also free to check the status of the connection at any time by calling:
BOOL bConnected = zConnection.IsConnected();
The most important arguments in the connection method are
user (if it is required, otherwise it can be
authorization is not required, it can be
Disconnect from the data-source
To disconnect, simply use:
This will free all the resources allocated in the connection object. But, don't forget to call it.
Query tables in the data-source
Maybe, you would like to know what tables are there in the data-source. To find out, you will have to allocate some memory for the buffer of
_ZTableInfo type, and pass it as an argument, like in the example:
BOOL bResult = zConnection.GetTableInfo(tableInfo, tiSize);
tiSize variable, after this call, you will obtain the exact number of tables in the data-source, and
tableInfo buffer will be filled with information about each table present in the data-source. This structure is defined as follows:
/* _ZTableInfo struct definition */
This is a simple way for enumerating the data-source tables.
Direct execution of SQL statements
It makes sense if you would like to modify data in the database tables. It doesn't make any sense if you want to get a result set back. So, you could write something like this:
// sqlStatement variable is defined somewhere else
BOOL bResult = zConnection.ExecuteSQL(sqlStatement);
Primarily, this is meant to be used in
DELETE statements. You can also use it in
SELECT statements but there is a small problem: you won't see any result. So, now you have seen how you can add, update, or remove records from the database. Now, it is time for a new DataLayer library object to be used to retrieve data from the data-source.
Working with the result set
The object is called
_ZRecord and it is used for data retrieval, like in the following example:
BOOL bResult = zRecord.ExecuteSQL(sqlStatement);
I suggest you to use here a
SELECT statement. Then, if the query succeeds, the return value is
TRUE and you should be able to access the returned data. Use a simple loop like this:
// data1 and data2 are variables of the type that is compatible
// with the type of the data in the requested column
// columnName variable is a known name
// of the column in the returned result set
data1 = (data1_type)zRecord.ColumnByName(columnName);
// columnIndex variable is a known index
// of the column in the returned result set
data2 = (data2_type)zRecord.ColumnByIndex(columnIndex);
If you are confused after this piece of code, I will explain. You will keep calling the
Move() method of the
_ZRecord object until you reach the end of the returned result set. This is a straightforward method (
_ZRecord object does not behave like
CRecordset object in the manner that you can get the first, last, or the previous record, and after each call, you will have the next record from the result set) which means that you want to go from the beginning to the end of the result set in a single pass.
Next, if you want your data, you will have to provide variables to hold it. These are provided by
data2 in the above example. They must be of a type compatible to the type of the returned data in each column of the result set. Then you either call the
ColumnByName() or the
ColumnByIndex() method of the
_ZRecord object. In the first, you pass the exact (but not case-sensitive) name of the column in the result set, and in the second, you pass a column index which should be less than the number of returned columns in the result set. Don't worry, if you make a mistake, you will have a
NULL value. Anyway, an explicit conversion is required because these methods return an object on any type (
If you would like to know the exact number of columns in the returned result set, do this:
int columnNumber = zRecord.GetColumnNumber();
The above code will be fine if you know the name of the columns in the result set. But what if you don't? In that case, you can use one of these methods:
_ZColumnInfo columnInfo1, columnInfo2;
columnInfo1 = zRecord.GetColumnInfo(columnName);
columnInfo2 = zRecord.GetColumnInfo(columnIndex);
_ZColumnInfo struct is shown below:
/* _ZColumnInfo struct definition */
As you can see, in this way, you will have all necessary information about columns.
As you can see, this small library will provide you enough freedom to perform different database tasks with no trouble. It has almost all built-in functionality as MFC ODBC database classes, and some more. It is not of a cursor-type. It can be applied to all data-sources with an existing ODBC driver. I have tested it on MS Access and MySQL database systems, and I found no problems performing simple tasks, but keep in mind that there could be some exceptions, so in that case, write to me.
The library is also light-weight, because (you'll for sure notice this) there are no error descriptions in the return values of the different functions. It is built to be as simple as it can, but next time, I might complicate things a bit.
Points of Interest
Working on this project, I found out that the ODBC SDK is not such a pleasant thing to work with, but it can have its bright sides. Anyway, with this implementation, my code will shrink in future and I will have more freedom working with returned result sets. My next step will be implementing the same thing you have seen here, but this time using the OLEDB SDK.