Introduction
I work on an application that has a CRecordSet
interface to a Jet database engine through ODBC. It works correctly, and we have very few problems. But occasionally, we run into a customer that somehow corrupts their MDAC/ODBC installation, and it is a pain to get it corrected remotely.
With this in mind, I decided it would be nice to use a different database engine that was more under our own control. After doing some research, the choice of SQLite was obvious. The engine is completely free, and open source. It is also very fast, and can be compiled directly into your application. No more reliance on other system software. For more information, see this link.
SQLite is a free database. Here is their blurb from their website: "SQLite is an in-process library that implements a self-contained, server-less, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain, and is thus free for use for any purpose, commercial or private."
This all sounded euphoric, until I realized that it wasn’t going to be very easy to re-write our code to stop using the CRecordSet
interface. I looked around, but couldn’t find anything similar. With this in mind, I decided to create my own wrapper class.
I wanted SQLiteTbl
to be very similar, but not necessarily an exact replacement for CRecordSet. I created SQLiteTbl
from scratch. I did so without dependence on any other templates/libraries such as MFC or STL. I needed a container for the BLOB data as well as the string data, so I created my own SQLiteBlob
and SQLiteString
objects. SQLiteTbl
provides the ability to have data member access to the data values in the specified table. You can sort, filter, delete, and add new records. You can also execute SQL statements directly, if needed.
Using the code
In order to use SQLiteTbl
, you will need to download SQLite and add it to your project. SQLiteTbl
expects to find sqlite3.h and sqliteInt.h in your include path. I have a sample MFC Visual Studio project that shows how to use SQLiteTble
.
SQLiteTbl
is an abstract base class. You will need to instantiate a derived SQLiteTbl
in order for this work. You will need to have a derived SQLiteTbl
for each table in your database.
Example class
Here is an example for a Cars table in the database:
cars.h:
class CarsTbl : public SQLiteTbl
{
public:
CarsTbl();
virtual bool Open(const char* pFile);
virtual const char* TableName();
virtual int ItemCount();
SQLiteString m_make;
SQLiteString m_model;
int m_year;
double m_weight;
SQLiteString m_color;
SQLiteString m_license;
SQLiteBlob m_picture;
};
cars.cpp:
#include "SQLiteTbl.h"
CarsTbl::CarsTbl()
{
SetItem(0, "Make", MYDB_TYPE_TEXT, &m_make);
SetItem(1, "Model", MYDB_TYPE_TEXT, &m_model);
SetItem(2, "Year", MYDB_TYPE_INT, &m_year);
SetItem(3, "Weight", MYDB_TYPE_DOUBLE, &m_weight);
SetItem(4, "Color", MYDB_TYPE_TEXT, &m_color);
SetItem(5, "License", MYDB_TYPE_TEXT, &m_license);
SetItem(6, "Color Picture", MYDB_TYPE_BLOB, &m_picture);
}
int CarsTbl::ItemCount() { return 7; }
const char* CarsTbl::TableName() { return "DMV Cars"; }
bool CarsTbl::Open(const char* pFile)
{
if (SQLiteTbl::Open(pFile)) {
if (Query())
return true;
}
Close();
return false;
}
Here is some basic code for using it:
CarsTbl myTable;
If (myTable.Open("c:\temp\cars.sqlite")) {
myTable.MoveFirst();
const char* pMake = myTable.m_make;
const char* pModel = myTable.m_model;
int year = myTable.m_year;
...
myTable.AddNew();
myTable.m_make = "Ford";
myTable.m_model = "Focus";
myTable.m_year = 2002;
myTable.Update();
int count = myTable.GetCount();
myTable.SetSort("[Make] ASC, [Year] DESC");
myTable.Query();
myTable.SetFilter("[Make]=\’Ford\’, [Model]=\’Mustang\’");
myTable.Query();
}
Sample project, "SQLiteTbl Test"
I have included a sample project that will compile on Visual Studio 2005, or Visual C++ 6.0. You should get the latest version of SQLite from here. There is a readme.txt in the project that describes how to build the project. It is a simple dialog based application that uses the SQLiteTbl
class as CarsTbl
.
Points of interest
On a side note, there is a Firefox plug-in called "SQLite Manager", which I found very useful when working with SQLite databases.
If you haven't seen the SQLite license yet, here it is:
** The author disclaims copyright to this source code. In place of
** a legal notice, here is a blessing:
**
** May you do good and not evil.
** May you find forgiveness for yourself and forgive others.
** May you share freely, never taking more than you give.
**
****************************************************
Isn't that a great license?
History
- Version 1.3 - December 2, 2008.