DataLayer - Freeware ODBC Library

• Download demo project (without DLL) - 14.6 Kb
• Download compiled library and header file - 53.5 Kb
• Download library source - 7.38 Kb

Introduction

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.

Background

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 CDatabase and 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 _ZConnection, _ZRecord, _ZColumnInfo, and _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.

#include "DataLayer.h"

Connect to the data-source

There is a simple class called _ZConnection that will provide you this:

// Connect to the data source

_ZConnection zConnection;
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 dsn and user (if it is required, otherwise it can be NULL). If authorization is not required, it can be NULL.

Disconnect from the data-source

To disconnect, simply use:

zConnection.Disconnect();

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:

_ZTableInfo tableInfo[100];
int tiSize;
BOOL bResult = zConnection.GetTableInfo(tableInfo, tiSize);

In the 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 */
struct _ZTableInfo
{
    SQLVARCHAR catalogName[1024];
    SQLVARCHAR schemaName[1024];
    SQLVARCHAR tableName[1024];
    SQLVARCHAR tableType[1024];
    SQLVARCHAR tableRemarks[1024];
};

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 INSERT, UPDATE, or 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:

_ZRecord zRecord(zConnection);
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

while (zRecord.Move())
{
      // 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 data1 and 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 (VOID).

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);

The _ZColumnInfo struct is shown below:

/* _ZColumnInfo struct definition */
struct _ZColumnInfo
{
    SQLCHAR columnName[256];
    SQLSMALLINT columnDataType;
    SQLSMALLINT columnNullable;
    SQLUINTEGER columnDataSize;
};

As you can see, in this way, you will have all necessary information about columns.

Conclusion

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.

History

• DataLayer Library v1.0.