Click here to Skip to main content
11,478,562 members (69,275 online)
Click here to Skip to main content

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

, 2 Dec 2008 CPOL 30.8K 4.4K 63
Rate this:
Please Sign up or sign in to vote.
A CRecordSet like interface for a SQLite database table.

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?

History

  • Version 1.3 - December 2, 2008.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Brad Kremer
Software Developer (Senior) Fluke Networks
United States United States
I live in beautiful Wenatchee, Washington. When I'm not writing software, you can find me mountain biking or snow skiing.

Comments and Discussions

 
Questionthank you Pin
Member 1114051922-Jan-15 17:32
memberMember 1114051922-Jan-15 17:32 
GeneralGood Utility for SQLite Pin
Vijay_vijay16-Aug-14 2:46
memberVijay_vijay16-Aug-14 2:46 
GeneralMy vote of 4 Pin
Vijay_vijay16-Aug-14 2:43
memberVijay_vijay16-Aug-14 2:43 
GeneralThank You Very Much!!! Pin
RODOLFO_SOSA20-Dec-13 8:43
memberRODOLFO_SOSA20-Dec-13 8:43 
QuestionSQLiteTbl for VC2010 implementation Pin
Patrik Källback3-Apr-13 1:08
memberPatrik Källback3-Apr-13 1:08 
QuestionSQLite? Pin
novice519-Mar-11 19:25
membernovice519-Mar-11 19:25 
GeneralGood Pin
loyal ginger19-Nov-09 11:21
memberloyal ginger19-Nov-09 11:21 
QuestionCould you add import to your class? Pin
avs431-Jul-09 7:25
memberavs431-Jul-09 7:25 
GeneralThank you Pin
Mister Transistor17-Dec-08 12:36
memberMister Transistor17-Dec-08 12:36 
Generalhello Pin
batsword14-Dec-08 15:30
memberbatsword14-Dec-08 15:30 
QuestionSecurity? Pin
li000013-Dec-08 6:29
memberli000013-Dec-08 6:29 
AnswerRe: Security? Pin
Brad Kremer15-Dec-08 14:30
memberBrad Kremer15-Dec-08 14:30 
GeneralSQLite Pin
Just someone else12-Dec-08 6:10
memberJust someone else12-Dec-08 6:10 
AnswerRe: SQLite Pin
John Crenshaw12-Dec-08 22:56
memberJohn Crenshaw12-Dec-08 22:56 
GeneralAutoIncrement data. [modified] Pin
Omar.Pessoa11-Dec-08 10:18
memberOmar.Pessoa11-Dec-08 10:18 
GeneralRe: AutoIncrement data. Pin
Omar.Pessoa12-Dec-08 2:15
memberOmar.Pessoa12-Dec-08 2:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150520.1 | Last Updated 2 Dec 2008
Article Copyright 2008 by Brad Kremer
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid