SQLiteTbl, A C++ class that provides a CRecordSet like interface for SQLite

SQLiteTbl-src

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();
 
    //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);
}

int CarsTbl::ItemCount() { return 7; } //must match items listed above

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:

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, "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?