SQLiteTbl, a C++ Class that Provides a CRecordSet-Like Interface to SQLite

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 http://www.sqlite.org/features.html.

SQLite is a free database. Here is their blurb from www.sqlite.org: “SQLite is a in-process library that implements a self-contained, serverless, 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 a very similar, but not necessarily 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 capability to have data member access to the data values in the specified table. You can sort, filter, delete, and add new records. You also can execute SQL statements directly if needed.

Using the Code

To use SQLiteTbl, you will need to download SQLite as 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 for this work. You will need to have one 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();

   //data members of the table
//0
   SQLiteString m_make;
   SQLiteString m_model;
   int m_year;
   double m_weight;
   SQLiteString m_color;
//5
   SQLiteString m_license;
   SQLiteBlob m_picture;
};

cars.cpp

#include "SQLiteTbl.h"

CarsTbl::CarsTbl()
{
   //these must match table in .h
//0
   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);
//5
   SetItem(5, "License",       MYDB_TYPE_TEXT, &m_license);
   SetItem(6, "Color Picture", MYDB_TYPE_BLOB, &m_picture);
}

//must match items listed above
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;
   }
   //anything goes wrong, close and return false
   Close();
   return false;
}

Here is some basic code for using it now:

CarsTbl myTable;
If (myTable.Open("c:\temp\cars.sqlite")) {
   //access data
   myTable.MoveFirst();
   const char* pMake = myTable.m_make;
   const char* pModel = myTable.m_model;
   int year = myTable.m_year;
   ...
   //add new item
   myTable.AddNew();
   myTable.m_make = "Ford";
   myTable.m_model = "Focus";
   myTable.m_year = 2002;
   myTable.Update();
   //get the table count
   int count = myTable.GetCount();
   //sort
   myTable.SetSort("[Make] ASC, [Year] DESC");
   myTable.Query();
   //filter
   myTable.SetFilter("[Make]=\'Ford\', [Model]=\'Mustang\'");
   myTable.Query();
}

Sample Project: “SQLite 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 www.sqlite.org. 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.

Point of Interest

On a side note, there is a Firefox plugin called “SQLite Manager.” I found it very useful when working with SQLite databases.

Point of Interest 2

If you haven’t see the SQLite license, 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