CODBCAccess: a CDatabase and CRecordset Wrapper

• Download source files - 17 Kb
• Download demo project - 75 Kb

Introduction

Not too long ago, I was working on a project which required the use of a database as the main source of data. I was not very familiar with databases at the time, so I decided to learn more about them by writing a class to assist in using data sources and the MFC classes CDatabase and CRecordset. What I came up with was a simple, but useful wrapper for CDatabase and CRecordset which combined the two classes into a single class with the ability to manage a CDatabase object and recordsets. As of now, the class is geared towards the Microsoft Access Driver for datasources. In the future, perhaps I will look into making the class more generically oriented towards datasource drivers.

Overview of the Class

CODBCAccess manages a connection to a datasource using ODBC through the MFC class CDatabase. Once connected to a datasource, you can execute SQL commands on that database and manage transactions. In addition, you can manage up to 10 recordsets, each of which can be opened with a unique SQL statement. With that little introduction, on to the class!

Using the Class

Before you start using the class, you must add ODBCAccess.cpp and ODBCAccess.h to your project file. In addition, because this class uses 3 helper classes made by George Polouse, you need to add RecordsetEx.*, Columns.*, and Tables.* to your project file.

CODBCAccess is used like any other class: you declare an object and call functions on it. You should include the following line of code in your stdafx.h file to add database support (only if the line is not already in the file):

#include <afxdb.h>

Once you have included that file, make sure to #include "ODBCAccess.h" in the file in which you want to declare the object. To start using the functions, create the object on the stack:

CODBCAccess myDB;

The constructor of CODBCAccess only sets some internal variables, so you now need to connect to a datasource. You can connect to a datasource using the CODBCAccess::Connect function, as follows:

myDB.Connect("MyDatasource");

Once you are connected to a datasource, you can do several things: (1) send SQL statements using the function: CODBCAccess::SendSQL, (2) start or end a transaction using the functions CODBCAccess::StartTransaction and CODBCAccess::EndTransaction, and (3) open and manage recordsets. Since transaction functions are very obvious (they follow the basic idea of the CDatabase equivalent functions), I will attempt to explain the recordset operations.

The class manages up to 10 recordsets, each of which is an object of type CRecordsetEx. Each recordset has an identifier name, commonly referred to as a 'friendly name.' When you want to create a new recordset, you call the function CODBCAccess::OpenNewSet, which takes a friendly name as the first parameter, the SQL statement you want to be executed as the second parameter, and the name of the table which you want the result set to consist of as the third parameter. You must either specify an SQL statement or a table name, not both. Sample calls would look like this:

//With an SQL statement:

myDB.OpenNewSet("MyNewSet", "SELECT [Main].[ID] FROM [Main]");

//OR with a table name (this code would execute the SQL statement: 
// SELECT * FROM [Main])

myDB.OpenNewSet("MyNewSet", "", "Main");

The function CODBCAccess::OpenNewSet examines the friendly name passed and, if a recordset already exists with that name, it will return false. If the friendly name has not been used, it creates a new CRecordsetEx and opens it with the information passed.

Once you have an open set, you can retrieve the CRecordsetEx object associated with the friendly name you passed by calling the function CODBCAccess::GetSet. CODBCAccess::GetSet is overloaded, so there are two ways to get the pointer to the recordset. The first is to give the function the friendly name associated with the desired set and pass a pointer by reference to the function which the function will fill with the recordset. The second way is to pass the friendly name and have the function return a pointer to the desired recordset. Sample calls would be as follows:

//Passing an existing pointer for the function to fill:

CRecordsetEx *setPtr;
myDB.GetSet("MyNewSet", setPtr);

//Getting a pointer from the function:

CRecordsetEx *setPtr = myDB.GetSet("MyNewSet");

If you have a pointer to a recordset, you can execute any function you wish on that pointer. You can even close and reopen the set. However, it is important that you do NOT delete the data at the pointer's location, because CODBCAccess manages a list of those pointers. To close and free memory used by a set, call the CODBCAccess::CloseSet function, like the following example:

//Closes the set if it is open and deletes the memory used by the object:

myDB.CloseSet("MyNewSet");

CODBCAccess provides several functions to handle two special recordsets: one to enumerate all the tables in the open database, and another to enumerate all the columns in a given table in the open database. Courtesy of George Polouse and his awesome Interactive SQL Tool for ODBC, the classes CColumns and CTables are used by CODBCAccess to achieve this. To open a recordset which enumerates the tables in the open database, call CODBCAccess::QueryOpenTables as follows:

myDB.QueryOpenTables();

Once that function has been called, you can call CODBCAccess::GetTables to get the pointer to the recordset. Once again, this function has two overloads, shown as follows:

//Passing an existing pointer for the function to fill:

CTables *tablesPtr;
myDB.GetTables(tablesPtr);

//Getting a pointer from the function:

CTables *tablesPtr = myDB.GetTables();

To enumerate the columns in a specified table, you must call the function CODBCAccess::QueryOpenColumns and pass the name of the table you want to query:

myDB.QueryOpenColumns("Main");

Once you have called that function, you can get a pointer to the recordset by using the function CODBCAccess::GetColumns, which also has two overloads:

//Passing an existing pointer for the function to fill:

CColumns *colsPtr;
myDB.GetColumns(colsPtr);

//Getting a pointer from the function:

CTables *colsPtr = myDB.GetColumns();

One final note about querying for tables and columns. Because you might want to query columns/tables for multiple table names/databases, there is no function to 'close' these objects like there is a CODBCAccess::CloseSet for recordsets. This is because memory is always freed when the database is closed and the set is reused if you want to open a new set of columns or tables. All you need to do in order to re-query for a new set of tables or columns is call QueryOpenTables or QueryOpenColumns again.

When you are done using your database, you can call CODBCAccess::Disconnect to close the connection to the database and free all memory pertaining to recordsets. CODBCAccess::Disconnect is automatically called by the destructor of the class (which is called when the object goes out of scope).

What's with all the two step junk?! (A note about error-checking)

I wanted CODBCAccess to handle all of the annoying error checking which comes with ODBC databases, so I designed almost every function in the class which did anything to the database or returned anything from the database to return a BOOLean value indicating whether the function failed or succeeded. Any function in the class which returns a BOOL value and which operates on or gets something from the database will set two error variables indicating the result of the function. Each of these variables can be checked to find out what went wrong. Whether the function in question succeeded or failed, those variables will always be set for the last function. One variable is an error-code variable (see function list below for an explanation of codes) and the other is a CString containing an explanation of the error. You can get each of these by calling the following two functions:

//To get the error code:

int code = myDB.GetLastErrCode();

//To get the error string:

CString errorString = myDB.GetLastErrMsg();

Because almost every function error checks almost everything, many actions are performed in a two-step process; for instance, the process of getting a recordset. For those of you who can't stand to waste a line of code declaring a pointer to pass by reference (like myself =), I overloaded the set functions to give you a choice between a two-step or one-step process. If you opt for the one-step process, I recommend that you check that the returned pointer/object is not NULL. If you are using the two-step process, you can just check whether the function returned false (in which case the pointer you supplied will always be NULL) and then analyze the error codes.

The Functions in the Class

CODBCAccess::CODBCAccess

Description: Class constructor.

Returns: none.

Extra Info: The constructor only sets a few internal variables, so you will need to connect to a datasource separately.

CODBCAccess::CODBCAccess();

CODBCAccess::~CODBCAccess

Description: Class destructor.

Returns: none.

Extra Info: The destructor always calls CODBCAccess::Disconnect.

CODBCAccess::~CODBCAccess();

CODBCAccess::ConfigDataSource

Description: Adds, modifies, or deletes a datasource programmatically.

Returns: BOOLean indicating success or failure.

Extra Info: Two overloads, one provides Microsoft Access Driver datasource configuration, the other provides a generic driver datasource configuration.

//(Overload 1)

BOOL CODBCAccess::ConfigDataSource(
UINT nOperation, //What you want to do to the data source

const CString &sDSN, //The data source name (e.g "Payroll")

const CString &sDatabase, //The absolute path of the database

BOOL bConnectAfter, //Connects to the data source you just configured

const CString &sDescription = "", //Optional: description of data source

BOOL bReadOnly = false, //Optional: read only access

BOOL bExclusive = false); //Optional: one user at a time access


//(Overload 2)

BOOL CODBCAccess::ConfigDataSource(
UINT nOperation, //What you want to do to the data source

const CString &sDriver, //The driver name

const CString &sAttributes); //The attributes for the driver

Notes: UINT nOperation is one of three things: DATASOURCE_ADD, DATASOURCE_MOD, or DATASOURCE_DEL to add, modify and delete datasources, respectively.

CODBCAccess::Connect

Description: Connects to a datasource.

Returns: BOOLean indicating success or failure.

Extra Info: Two overloads, one allows you to provide the connection information, the other will display and ODBC dialog to let the user select a datasource.

//(Overload 1)

BOOL CODBCAccess::Connect(
const CString &sDSN, //The data source name (e.g. "Payroll")

const CString &sID = "", //User ID for the database

const CString &sPass = "", //Password for the database

BOOL bExclusive = false, //Exclusive access

BOOL bReadOnly = false); //Read only access


//(Overload 2)

BOOL CODBCAccess::Connect();

CODBCAccess::IsConnected

Description: Tells you whether you are connected to a database or not.

Returns: BOOLean indicating connected or not.

Extra Info: none.

BOOL CODBCAccess::IsConnected();

CODBCAccess::CreateDatabase

Description: Creates a new *.mdb file and optionally configures a datasource and connects to it.

Returns: BOOLean indicating success or failure.

Extra Info: This is useful for creating temporary databases or for the ability to completely control databases programmatically.

BOOL CreateDatabase(
   const CString &sFileName,	   //The filename of the new database (must 
                                   //contain '.mdb' extension)

   BOOL bConfigDataSource = false, //Configures a datasource for the 
                                   // just-created datasource

   const CString &sDSN = "",	   //The data source name (e.g. "Payroll")

   BOOL bConnectAfter = false,     //Connects to the data source you just 
                                   // configured

   const CString &sDescription = "", //Optional: description of data source

   BOOL bReadOnly = false,           //Optional: read only access

   BOOL bExclusive = false);         //Optional: one user at a time access

CODBCAccess::SetTimeouts

Description: Sets the timeout seconds for connecting and querying the database.

Returns: void.

Extra Info: none.

void CODBCAccess::SetTimeouts(
int nLoginSeconds, //# of seconds for login timeout

int nQuerySeconds); //# of seconds for query timeout

CODBCAccess::Disconnect

Description: Disconnects from the datasource and frees memory.

Returns: void.

Extra Info: If the database is already closed, the function does nothing.

void CODBCAccess::Disconnect();

Notes: Disconnect will free all memory allocated during the connection lifetime of the datasource. The function is also always called when the CODBCAccess destructor is called.

CODBCAccess::StartTransaction

Description: Begins a database transaction. For information about transactions, search the MSDN library for CDatabase::BeginTrans

Returns: BOOLean indicating success or failure.

Extra Info: If you are already in a transaction, the function will fail.

BOOL CODBCAccess::StartTransaction();

CODBCAccess::SendSQL

Description: sends an SQL statement to the database.

Returns: BOOLean indicating success or failure.

Extra Info: If you are in a transaction, the SQL statement executed will be able to be reversed.

BOOL CODBCAccess::SendSQL(const CString &sSQL);

CODBCAccess::Rollback

Description: Ends a database transaction, but reverses any changes that were made during the transaction.

Returns: BOOLean indicating success or failure.

Extra Info: none.

BOOL CODBCAccess::Rollback();

CODBCAccess::EndTransaction

Description: Ends a database transaction, which locks down any changes you made during the transaction.

Returns: BOOLean indicating success or failure.

Extra Info: If CDatabase::CommitTrans fails in any way, the fucntion will attempt to rollback the changes made.

BOOL CODBCAccess::EndTransaction();

CODBCAccess::IsInTrans

Description: Tells you whether you are in a transaction or not.

Returns: BOOLean indicating in transaction or not.

Extra Info: none.

BOOL CODBCAccess::IsInTrans();

CODBCAccess::GetCDatabase

Description: Returns the CDatabase object managed by the class.

Returns: CDatabase pointer.

Extra Info: NEVER close or delete the object.

CDatabase *CODBCAccess::GetCDatabase();

CODBCAccess::GetLastErrCode

Description: Returns the last error code.

Returns: int representing the last error code.

Extra Info: none.

int CODBCAccess::GetLastErrCode();

Notes: the returned code can be one of the following five: RETURN_SUCCESS (function succeeded), RETURN_ARGUMENT (there was a problem with one of the arguments passed to the function, RETURN_ERRMSG (examine GetLastErrMsg to figure out what went wrong), RETURN_DB (database was not opened), RETURN_CRITICAL (critical error encountered, the database was closed for safety).

CODBCAccess::GetLastErrMsg

Description: Returns the last error message.

Returns: CString representing the last error message.

Extra Info: none.

CString CODBCAccess::GetLastErrMsg();

CODBCAccess::OpenNewSet

Description: Creates/opens a new recordset based upon the values passed which is identified by the friendly name you used.

Returns: BOOLean indicating success or failure.

Extra Info: OpenNewSet will manage creation of the recordset.

BOOL CODBCAccess::OpenNewSet(
const CString &sFriendlyName, //the name by which to reference this set

const CString &sSQL = _T(""), //the SQL statement to execute for the set

const CString &sTableName = _T("")); //OR the table name

Notes: If you want the function to work, specify either an SQL statement or a table name, not both =)

CODBCAccess::CloseSet

Description: Closes and frees the memory of the associated recordset.

Returns: BOOLean indicating success or failure.

Extra Info: none.

BOOL CODBCAccess::CloseSet(const CString &sFriendlyName); 
//the name of the set (assigned on opening)

CODBCAccess::CloseAllSets

Description: Closes and frees the memory of the all the open recordsets.

Returns: BOOLean indicating success or failure.

Extra Info: none.

BOOL CODBCAccess::CloseAllSets();

CODBCAccess::GetSet

Description: Retrieves a pointer to the associated recordset.

Returns: BOOLean indicating success or failure or pointer to a recordset.

Extra Info: Two overloads: the first is a two-step process in which you pass a pointer by reference to be filled by the function; the second returns a pointer to the associated CRecordsetEx object.

//(Overload 1)

BOOL CODBCAccess::GetSet(
const CString &sFriendlyName, //the name of the set (assigned on opening)

CRecordsetEx *&dyna); //the pointer to recieve the set address


//(Overload 2)

CRecordsetEx *CODBCAccess::GetSet(
const CString &sFriendlyName); //the name of the set (assigned on opening)

CODBCAccess::GetNamesOfOpenSets

Description: Plugs in the friendly names of all the open recordsets into a passed CStringArray.

Returns: void. The CStringArray will be empty if the database was not open or there was another problem.

Extra Info: none.

void CODBCAccess::GetNumberOfOpenSets(CStringArray &array);
//The array to be filled with the names

CODBCAccess::GetNumSets

Description: Get the number of sets manages by the class.

Returns: The number of manages sets.

Extra Info: none.

int CODBCAccess::GetNumSets();

CODBCAccess::QueryOpenTables

Description: Opens a recordset which enumerates (one per row) the tables in the open database.

Returns: BOOLean indicating success or failure.

Extra Info: This function can be called multiple times in a row without having to close the recordset.

BOOL CODBCAccess::QueryOpenTables();

CODBCAccess::QueryOpenColumns

Description: Opens a recordset which enumerates (one per row) the columns in the specified table.

Returns: BOOLean indicating success or failure.

Extra Info: This function can be called multiple times in a row without having to close the recordset.

BOOL CODBCAccess::QueryOpenColumns(const CString &sTableName); 
//	the name of the table to get columns from

CODBCAccess::GetTables

Description: Gets the recordset for the enumerated tables (as a CTables pointer).

Returns: BOOLean indicating success or failure or a CTables pointer.

Extra Info: Two overloads: in the first, you pass a pointer by reference which is filled by the function; the second returns the pointer to the CTables pointer.

//(Overload 1)

//the pointer to recieve the set address

BOOL CODBCAccess::GetTables(CTables *&table); 

//(Overload 2)

CTables *CODBCAccess::GetTables();

CODBCAccess::GetColumns

Description: Gets the recordset for the enumerated columns (as a CColumns pointer).

Returns: BOOLean indicating success or failure or a CColumns pointer.

Extra Info: Two overloads: in the first, you pass a pointer by reference which is filled by the function; the second returns the pointer to the CColumns pointer.

//(Overload 1)

//the pointer to recieve the set address

BOOL CODBCAccess::GetColumns(CColumns *&cols); 
//(Overload 2)

CColumns *CODBCAccess::GetColumns();

Acknowledgements

A special thanks to George Polouse for writing that awesome "Interactive SQL Tool for ODBC" and for letting me use his classes in my class.

Conclusion

I would just like to include a quick note about the class. I know there will be bugs in the code. If you find a bug, you can tell me by a simple email to Alexander@wisemanweb.com. Also, I have made a special effort to root out any memory leaks, but there might be some. If you find one, please, please tell me, since I would like to make this class as useful and bug-free as possible. Thank you for taking the time to read my article, I hope the class is of some use to you!

History

• 6 Mar 2002 - updated downloads
• 12 June 2002 - updated downloads