CppSQLite - C++ Wrapper for SQLite

• Download CppSQLite demo projects, source and sqlite.dll for SQLite 2.8.15 - 156 KB
• Download CppSQLite 3 demo projects, source and sqlite3.dll for SQLite 3.0.8 - 171 KB
• Download CppSQLite 3.2 demo projects, source and sqlite3.dll for SQLite 3.4.0 - 252 KB

Introduction

This article describes CppSQLite, a very thin C++ wrapper around the public domain SQLite database library.

A description of how to link applications with SQLite is provided, then an example program using CppSQLite is presented, and finally the CppSQLite classes are documented.

To set the scene, here is a quote from the SQLite author...

SQLite is a C library that implements an embeddable SQL database engine. Programs that link with the SQLite library can have SQL database access without running a separate RDBMS process. The distribution comes with a standalone command-line access program (SQLite) that can be used to administer a SQLite database and which serves as an example of how to use the SQLite library.

SQLite is not a client library used to connect to a big database server. SQLite is the server. The SQLite library reads and writes directly to and from the database files on disk.

Background

I am always on the lookout for simple yet powerful software development tools and ideas, and SQLite definitely falls into this category. In fact, the "Lite" name is a bit misleading, as it implements a large subset of the SQL standard, including transactions, and when projects such as PHP start to bundle it as standard instead of MySQL, you have to take a look.

I thought it would be fun to write a thin wrapper around the C interface to make it C++ friendly. There are already a number of C++ wrappers listed on the SQLite website, but one is commercial, another seemed a bit complex, and another is specific to the wxWidgets framework. After all, the author of SQLite looks to have gone to pains to keep things simple, so I thought a C++ wrapper for it should keep things simple as well.

Working With SQLite

SQLite is provided in 2 packages on the Windows platform, as a compiled DLL, and also in source form. Even if you only wish to use the DLL, you will still need to get the source code, as this contains the required header file.

If desired, the SQLite source could be compiled into a library (.lib) file for statically linking with your application, but this is not covered in this article. Compilation instructions can be found on the SQLite web site.

Linking dynamically still requires that a .lib file is built for linking with your application. This can be done using Microsoft's LIB command. On my system, this is located at D:\Program Files\Microsoft Visual Studio\VC98\Bin\lib.exe.

Unzip sqlite.zip which contains sqlite.dll and sqlite.def, and execute the following command to produce the lib file.

Collapse | Copy Code

c:\>lib /def:sqlite.def

sqlite.h needs to be visible to your application at compile time, as does sqlite.lib.

sqlite.dll needs to be available to your application at runtime.

CppSQLite Demo Code

The following code demonstrates how to use the main features of SQLite via CppSQLite, with comments inline.

Collapse | Copy Code

#include "CppSQLite.h"
#include <ctime>
#include <iostream>

using namespace std;

const char* gszFile = "C:\\test.db";

int main(int argc, char** argv)
{
    try
    {
        int i, fld;
        time_t tmStart, tmEnd;
        CppSQLiteDB db;

        cout << "SQLite Version: " << db.SQLiteVersion() << endl;

        remove(gszFile);
        db.open(gszFile);

        cout << endl << "Creating emp table" << endl;
        db.execDML("create table emp(empno int, empname char(20));");
        ///////////////////////////////////////////////////////////////
        // Execute some DML, and print number of rows affected by each one
        ///////////////////////////////////////////////////////////////
        cout << endl << "DML tests" << endl;
        int nRows = db.execDML("insert into emp values (7, 'David Beckham');");
        cout << nRows << " rows inserted" << endl;

        nRows = db.execDML(
         "update emp set empname = 'Christiano Ronaldo' where empno = 7;");
        cout << nRows << " rows updated" << endl;

        nRows = db.execDML("delete from emp where empno = 7;");
        cout << nRows << " rows deleted" << endl;

        /////////////////////////////////////////////////////////////////
        // Transaction Demo
        // The transaction could just as easily have been rolled back
        /////////////////////////////////////////////////////////////////
        int nRowsToCreate(50000);
        cout << endl << "Transaction test, creating " << nRowsToCreate;
        cout << " rows please wait..." << endl;
        tmStart = time(0);
        db.execDML("begin transaction;");

        for (i = 0; i < nRowsToCreate; i++)
        {
            char buf[128];
            sprintf(buf, "insert into emp values (%d, 'Empname%06d');", i, i);
            db.execDML(buf);
        }

        db.execDML("commit transaction;");
        tmEnd = time(0);

        ////////////////////////////////////////////////////////////////
        // Demonstrate CppSQLiteDB::execScalar()
        ////////////////////////////////////////////////////////////////
        cout << db.execScalar("select count(*) from emp;") 
               << " rows in emp table in ";
        cout << tmEnd-tmStart << " seconds (that was fast!)" << endl;

        ////////////////////////////////////////////////////////////////
        // Re-create emp table with auto-increment field
        ////////////////////////////////////////////////////////////////
        cout << endl << "Auto increment test" << endl;
        db.execDML("drop table emp;");
        db.execDML(
         "create table emp(empno integer primary key, empname char(20));");
        cout << nRows << " rows deleted" << endl;

        for (i = 0; i < 5; i++)
        {
            char buf[128];
            sprintf(buf, 
       "insert into emp (empname) values ('Empname%06d');", i+1);
            db.execDML(buf);
            cout << " primary key: " << db.lastRowId() << endl;
        }

     ///////////////////////////////////////////////////////////////////
     // Query data and also show results of inserts into auto-increment field
     //////////////////////////////////////////////////////////////////
        cout << endl << "Select statement test" << endl;
        CppSQLiteQuery q = db.execQuery("select * from emp order by 1;");

        for (fld = 0; fld < q.numFields(); fld++)
        {
            cout << q.fieldName(fld) << "(" << q.fieldType(fld) << ")|";
        }
        cout << endl;

        while (!q.eof())
        {
            cout << q.fieldValue(0) << "|";
            cout << q.fieldValue(1) << "|" << endl;
            q.nextRow();
        }

        ///////////////////////////////////////////////////////////////
        // SQLite's printf() functionality. Handles embedded quotes and NULLs
        ////////////////////////////////////////////////////////////////
        cout << endl << "SQLite sprintf test" << endl;
        CppSQLiteBuffer bufSQL;
        bufSQL.format("insert into emp (empname) values (%Q);", "He's bad");
        cout << (const char*)bufSQL << endl;
        db.execDML(bufSQL);

        bufSQL.format("insert into emp (empname) values (%Q);", NULL);
        cout << (const char*)bufSQL << endl;
        db.execDML(bufSQL);

        ////////////////////////////////////////////////////////////////////
        // Fetch table at once, and also show how to 
        // use CppSQLiteTable::setRow() method
        //////////////////////////////////////////////////////////////////
        cout << endl << "getTable() test" << endl;
        CppSQLiteTable t = db.getTable("select * from emp order by 1;");

        for (fld = 0; fld < t.numFields(); fld++)
        {
            cout << t.fieldName(fld) << "|";
        }
        cout << endl;
        for (int row = 0; row < t.numRows(); row++)
        {
            t.setRow(row);
            for (int fld = 0; fld < t.numFields(); fld++)
            {
                if (!t.fieldIsNull(fld))
                    cout << t.fieldValue(fld) << "|";
                else
                    cout << "NULL" << "|";
            }
            cout << endl;
        }

        ////////////////////////////////////////////////////////////////////
        // Test CppSQLiteBinary by storing/retrieving some binary data, checking
        // it afterwards to make sure it is the same
        //////////////////////////////////////////////////////////////////
        cout << endl << "Binary data test" << endl;
        db.execDML("create table bindata(desc char(10), data blob);");
        
        unsigned char bin[256];
        CppSQLiteBinary blob;

        for (i = 0; i < sizeof bin; i++)
        {
            bin[i] = i;
        }

        blob.setBinary(bin, sizeof bin);

        bufSQL.format("insert into bindata values ('testing', %Q);", 
                      blob.getEncoded());
        db.execDML(bufSQL);
        cout << "Stored binary Length: " << sizeof bin << endl;

        q = db.execQuery("select data from bindata where desc = 'testing';");

        if (!q.eof())
        {
            blob.setEncoded((unsigned char*)q.fieldValue("data"));
            cout << "Retrieved binary Length: " 
       << blob.getBinaryLength() << endl;
        }

        const unsigned char* pbin = blob.getBinary();
        for (i = 0; i < sizeof bin; i++)
        {
            if (pbin[i] != i)
            {
                cout << "Problem: i: ," << i << " bin[i]: " 
             << pbin[i] << endl;
            }
        }

        /////////////////////////////////////////////////////////
        // Pre-compiled Statements Demo
        /////////////////////////////////////////////////////////////
        cout << endl << "Transaction test, creating " << nRowsToCreate;
        cout << " rows please wait..." << endl;
        db.execDML("drop table emp;");
        db.execDML("create table emp(empno int, empname char(20));");
        tmStart = time(0);
        db.execDML("begin transaction;");

        CppSQLiteStatement stmt = db.compileStatement(
            "insert into emp values (?, ?);");
        for (i = 0; i < nRowsToCreate; i++)
        {
            char buf[16];
            sprintf(buf, "EmpName%06d", i);
            stmt.bind(1, i);
            stmt.bind(2, buf);
            stmt.execDML();
            stmt.reset();
        }

        db.execDML("commit transaction;");
        tmEnd = time(0);

        cout << db.execScalar("select count(*) from emp;") 
           << " rows in emp table in ";
        cout << tmEnd-tmStart << " seconds (that was even faster!)" << endl;
        cout << endl << "End of tests" << endl;
    }
    catch (CppSQLiteException& e)
    {
        cerr << e.errorCode() << ":" << e.errorMessage() << endl;
    }

    ////////////////////////////////////////////////////////////////
    // Loop until user enters q or Q
    ///////////////////////////////////////////////////////////
    char c(' ');

    while (c != 'q' && c != 'Q')
    {
        cout << "Press q then enter to quit: ";
        cin >> c;
    }
    return 0;
}

CppSQLite Classes

The following simple classes are defined to encapsulate the functionality of SQLite.

All the CppSQLite classes are contained in 2 files CppSQLite.h and CppSQLite.cpp, which will need to be added to your application.

CppSQLiteException

Encapsulates a SQLite error code and message. Nothing complicated here, and this class could easily be incorporated into an existing exception hierarchy, if required.

Error messages returned by SQLite need to be sqlite_freemem()'d by the programmer, and this class takes on that responsibility. Note that for error messages generated by CppSQLite, we don't want to free the memory, so there is an optional trailing parameter that dictates whether CppSQLiteException frees the memory.

Collapse | Copy Code

class CppSQLiteException
{
public:

    CppSQLiteException(const int nErrCode,
                    char* szErrMess,
                    bool bDeleteMsg=true);

    CppSQLiteException(const CppSQLiteException&  e);

    virtual ~CppSQLiteException();

    const int errorCode() { return mnErrCode; }

    const char* errorMessage() { return mpszErrMess; }

    static const char* errorCodeAsString(int nErrCode);

private:

    int mnErrCode;
    char* mpszErrMess;
};

CppSQLiteDB

Encapsulates a SQLite database file.

Collapse | Copy Code

class CppSQLiteDB
{
public:

    enum CppSQLiteDBOpenMode
    {
        openExisting,
        createNew,
        openOrCreate
    };

    CppSQLiteDB();

    virtual ~CppSQLiteDB();

    void open(const char* szFile);

    void close();

    int execDML(const char* szSQL);

    CppSQLiteQuery execQuery(const char* szSQL);

    int execScalar(const char* szSQL);

    CppSQLiteTable getTable(const char* szSQL);

    CppSQLiteStatement compileStatement(const char* szSQL);

    int lastRowId();

    void interrupt() { sqlite_interrupt(mpDB); }

    void setBusyTimeout(int nMillisecs);

    static const char* SQLiteVersion() { return SQLITE_VERSION; }

private:

    CppSQLiteDB(const CppSQLiteDB& db);
    CppSQLiteDB& operator=(const CppSQLiteDB& db);

    sqlite_vm* compile(const char* szSQL);

    void checkDB();

    sqlite* mpDB;
    int mnBusyTimeoutMs;
};

open() and close() methods are self explanatory. SQLite does provide a mode argument to sqlite_open() but this is documented as having no effect, so is not provided for in CppSQLite.

execDML() is used to execute Data Manipulation Language (DML) commands such as create/drop/insert/update/delete statements. It returns the number of rows affected. Multiple SQL statements separated by semi-colons can be submitted and executed all at once. Note: there is a potential problem with the way that CppSQLite returns the number of rows affected. If there are any other un-finalized() operations in progress the number of rows affected will be cumulative and include those from previous statements. So if this feature is important to you, you have to make sure that any CppSQLiteQuery and CppSQLiteStatement objects that have not destructed yet have finalize() called on them before you execDML().

execQuery() is used to execute queries. The CppSQLiteQuery object is returned by value, as this frees the programmer from having to delete it.

execScalar() is an idea I got from ADO.NET. It is a shortcut for when you need to run a simple aggregate function, for example, "select count(*) from emp" or "select max(empno) from emp". It returns the value of the first field in the first row of the query result. Other columns and rows are ignored.

getTable() allows for the SQLite feature which can fetch a whole table in a single operation, rather than having to fetch one row at a time as with a query. Actually, subsets of table rows can be fetched by specifying a query with a where clause, but the whole result set is returned at once. Again, the CppSQLiteTable object is returned by value for convenience.

compileStatement() allows for the experimental SQLite pre-compiled SQL feature. See CppSQLiteStatement below.

SQLite is typeless, which means all fields are stored as strings. The one exception to this is the INTEGER PRIMARY KEY type, which allows an auto increment field, much like the SQL Server's identity columns. The lastRowId() function is used to determine the value of the primary key from the last row inserted.

interrupt() is useful when multithreading, and allows one thread to interrupt an operation in progress on another thread.

setBusyTimeout() can also be useful when multithreading, and allows the programmer to dictate how long SQLite waits before returning SQLITE_BUSY if another thread has a lock on the database. The default value is 60 seconds, set when the database is opened.

The copy constructor and operator=() are made private, as it does not make sense to copy a CppSQLiteDB object.

Finally, the static method SQLiteVersion() returns the version number of the underlying SQLite DLL.

CppSQLiteQuery

Encapsulates a SQLite query result set.

Collapse | Copy Code

class CppSQLiteQuery
{
public:

    CppSQLiteQuery();

    CppSQLiteQuery(const CppSQLiteQuery& rQuery);

    CppSQLiteQuery(sqlite_vm* pVM,
                bool bEof,
                int nCols,
                const char** paszValues,
                const char** paszColNames,
                bool bOwnVM=true);

    CppSQLiteQuery& operator=(const CppSQLiteQuery& rQuery);

    virtual ~CppSQLiteQuery();

    int numFields();

    const char* fieldName(int nCol);

    const char* fieldType(int nCol);

    const char* fieldValue(int nField);
    const char* fieldValue(const char* szField);

    int getIntField(int nField, int nNullValue=0);
    int getIntField(const char* szField, int nNullValue=0);

    double getFloatField(int nField, double fNullValue=0.0);
    double getFloatField(const char* szField, double fNullValue=0.0);

    const char* getStringField(int nField, const char* szNullValue="");
    const char* getStringField(const char* szField, 
          const char* szNullValue="");

    bool fieldIsNull(int nField);
    bool fieldIsNull(const char* szField);

    bool eof();

    void nextRow();

    void finalize();

private:

    void checkVM();

    sqlite_vm* mpVM;
    bool mbEof;
    int mnCols;
    const char** mpaszValues;
    const char** mpaszColNames;
    bool mbOwnVM;
};

nextRow() and eof() allow iteration of the query results.

numFields(), fieldValue(), fieldName(), fieldType() and fieldIsNull() allow the programmer to determine the number of fields, their names, values, types and whether they contain a SQL NULL. There are overloaded versions allowing the required field to be either specified by index or name.

getIntField(), getFloatField() and getStringField() provide a slightly easier to program way of getting field values, by never returning a NULL pointer for SQL NULL, and there is a default 2nd parameter that allows the programmer to specify which value to return instead.

It is not possible to iterate backwards through the results. The reason for this is that CppSQLite is a thin wrapper and does not cache any returned row data. If this is required, CppSQLiteDB::getTable() should be used, or the application could inherit from this class.

finalize() frees the memory associated with the query, but the destructor automatically calls this.

CppSQLiteTable

SQLite provides a method to obtain a complete table's contents in a single block of memory, CppSQLiteTable encapsulates this functionality.

Collapse | Copy Code

class CppSQLiteTable
{
public:

    CppSQLiteTable();

    CppSQLiteTable(const CppSQLiteTable& rTable);

    CppSQLiteTable(char** paszResults, int nRows, int nCols);

    virtual ~CppSQLiteTable();

    CppSQLiteTable& operator=(const CppSQLiteTable& rTable);

    int numFields();

    int numRows();

    const char* fieldName(int nCol);

    const char* fieldValue(int nField);
    const char* fieldValue(const char* szField);

    int getIntField(int nField, int nNullValue=0);
    int getIntField(const char* szField, int nNullValue=0);

    double getFloatField(int nField, double fNullValue=0.0);
    double getFloatField(const char* szField, double fNullValue=0.0);

    const char* getStringField(int nField, const char* szNullValue="");
    const char* getStringField(const char* szField, const char* szNullValue="");

    bool fieldIsNull(int nField);
    bool fieldIsNull(const char* szField);

    void setRow(int nRow);

    void finalize();

private:

    void checkResults();

    int mnCols;
    int mnRows;
    int mnCurrentRow;
    char** mpaszResults;
};

setRow() provides a random access method for movement between rows, and can be used in conjunction with numRows() to iterate the table. This design decision was made for simplicity, as following the same model as for CppSQLiteQuery, would have required functions for bof(), eof(), first(), last(), next() and prev().

numFields(), fieldValue(), fieldName(), fieldIsNull(), getIntField(), getFloatField(), getStringField(), close(), and operator=() provide the same functionality as for CppSQLiteQuery.

CppSQLiteBuffer

Encapsulates SQLite "sprintf" functionality.

SQLite provides a function sqlite_mprintf() which is like the C runtime sprintf() except there is no possibility of overrunning the buffer supplied, as sqlite_mprintf() uses malloc to allocate enough memory. The other benefit over sprintf() is the %Q tag, which works like %s except that it will massage apostrophes so that they don't mess up the SQL string being built, and also translate NULL pointers into SQL NULL values.

Collapse | Copy Code

class CppSQLiteBuffer
{
public:

    CppSQLiteBuffer();

    ~CppSQLiteBuffer();

    const char* format(const char* szFormat, ...);

    operator const char*() { return mpBuf; }

    void clear();

private:

    char* mpBuf;
};

operator const char*() allows the programmer to pass an instance of this object to the functions defined on CppSQLiteDB.

CppSQLiteBinary

Because SQLite stores all data as NULL terminated strings, it is not possible to store binary data if it has embedded NULLs. SQLite provides 2 functions sqlite_encode_binary() and sqlite_decode_binary() that can be used to allow storage and retrieval of binary data. CppSQLiteBinary encapsulates these 2 functions.

These two functions are not currently provided as part of the pre-compiled DLL, so I have copied the entire contents of SQLite's encode.c file into the CppSQLite.cpp file. Should these functions be provided in the DLL at some future point, they can easily be removed from CppSQLite.cpp.

Collapse | Copy Code

class CppSQLiteBinary
{
public:

    CppSQLiteBinary();

    ~CppSQLiteBinary();

    void setBinary(const unsigned char* pBuf, int nLen);
    void setEncoded(const unsigned char* pBuf);

    const unsigned char* getEncoded();
    const unsigned char* getBinary();

    int getBinaryLength();

    unsigned char* allocBuffer(int nLen);

    void clear();

private:

    unsigned char* mpBuf;
    int mnBinaryLen;
    int mnBufferLen;
    int mnEncodedLen;
    bool mbEncoded;
};

CppSQLiteBinary can accept data in either encoded or binary form using the setEncoded() and setBinary() functions. Whichever is used, enough memory is always allocated to store the encoded version, which is usually longer as nulls and single quotes have to be escaped.

Data is retrieved using the getEncoded() and getBinary() functions. Depending on which form the data is currently in within the class, it may need to be converted.

getBinaryLength() returns the length of the binary data stored, again converting the held format from encoded to binary, if required.

allocBuffer() can be used to prevent data having to be cycled via a temporary buffer like in the example code at the start of this article. This function could be used as in the following example where data is read straight from a file into a CppSQLiteBinary object.

Collapse | Copy Code

int f = open(gszJpgFile, O_RDONLY|O_BINARY);
int nFileLen = filelength(f);
read(f, blob.allocBuffer(nFileLen), nFileLen);

CppSQLiteStatement

SQLite provides some experimental functionality for working with pre-compiled SQL. When the same SQL is being executed over and over again with different values, a significant performance improvement can be had by only compiling the SQL once, and executing it multiple times, each time with different values. CppSQLiteStatement encapsulates this functionality.

Collapse | Copy Code

class CppSQLiteStatement
{
public:

    CppSQLiteStatement();

    CppSQLiteStatement(const CppSQLiteStatement& rStatement);

    CppSQLiteStatement(sqlite* pDB, sqlite_vm* pVM);

    virtual ~CppSQLiteStatement();

    CppSQLiteStatement& operator=(const CppSQLiteStatement& rStatement);

    int execDML();

    CppSQLiteQuery execQuery();

    void bind(int nParam, const char* szValue);
    void bind(int nParam, const int nValue);
    void bind(int nParam, const double dwValue);
    void bindNull(int nParam);

    void reset();

    void finalize();

private:

    void checkDB();
    void checkVM();

    sqlite* mpDB;
    sqlite_vm* mpVM;
};

A CppSQLiteStatement object is obtained by calling CppSQLiteDB::compileStatement() with a SQL statement containing placeholders, as follows:

Collapse | Copy Code

CppSQLiteStatement stmt = db.compileStatement("insert into emp values (?, ?);");
stmt.bind(1, 1);
stmt.bind(2, "Emp Name");
stmt.execDML();
stmt.reset();

The CppSQLiteStatement::bind() methods are then used to set the values of the placeholders, before calling either execDML() or execQuery() as appropriate.

After the programmer has finished with the result from either execDML() or execQuery(), the reset() method can be called to put the statement back to a compiled state. The CppSQLiteStatement::bind() methods can then be used again, followed by execDML() or execQuery(). A typical use would be in a loop as demonstrated in the CppSQLiteDemo program.

Multithreading

SQLite is compiled as thread-safe on Windows by default, and CppSQLite makes use of some SQLite features to help with multithreaded use. Included in the source code accompanying this article is a 2nd demo program called CppSQLiteDemoMT, which demonstrates these features.

Each thread wishing to utilize CppSQLite on the same database file at the same time must have its own CppSQLiteDB object, and call open(). To put this another way, it is an error for more than 1 thread to call into a CppSQLiteDB object at the same time. The one exception to this is CppSQLiteDB::interrupt(), which can be used from one thread to interrupt the work of another thread.

The other change to CppSQLite for multithreaded use is to make use of the sqlite_busy_timeout() function which causes SQLite to wait up to the specified number of milliseconds before returning SQLITE_BUSY. By default, CppSQLite sets this to 60,000 (60 seconds), but this can be changed using CppSQLiteDB::setBusyTimeout() as required. Various examples of doing this are shown in the CppSQLiteDemoMT program.

SQLite Functionality Not Currently Wrapped

SQLite provides a mechanism that allows the application developer to define stored procedures and aggregate functions that can be called from SQL statements. These stored procedures are written in C by the application developer, and made known to SQLite via function pointers. This is how the SQL built in functions are implemented by SQLite, but this functionality is not currently catered for in CppSQLite.

SQLite provides some other variations on the functions wrapped, and the reader is encouraged to study the SQLite documentation.

Managed C++

It is possible to compile SQLite and CppSQLite into a managed C++ program, It Just Works (IJW). You will need to set the CppSQLite.cpp file so that it does not use pre-compiled headers and also not to use Managed extensions, i.e. don't use /clr.

There is a Managed C++ demo included with the CppSQLite downloads.

SQLite Version 3

At the time of writing, SQLite version 3 is in beta. See http://www.sqlite.org/ for further details. I have produced a port of CppSQLite to SQLite version 3, and the following notes explain the differences.

There are a new set of classes with the prefix CppSQLite3, for example CppSQLite3Exception. This allows programs to link with both versions of CppSQLite, as is possible with both versions of SQLite itself.

There is not support for UTF-16 initially, as it is not something I have experience of, and wouldn't know how to test. This can be added later with another set of classes, called for example CppSQLite3Exception16 etc. Note that some sqlite3 stuff such as sqlite3_exec() and sqlite3_get_table() do not appear to have UTF-16 versions, also sqlite3_vmprintf(), used by CppSQLiteBuffer.

Error messages are now returned by sqlite3_errmsg() and do not need to be freed. To keep consistency between CppSQLite and CppSQLite3 the code that throws exceptions with messages returned from SQLite version 3 has been changed so that it passes DONT_DELETE_MSG as the final parameter to CppSQLite3Exception. The exception to this is the messages returned by sqlite3_exec() and sqlite3_get_table().

SQLite version 3 now has direct support for BLOB data, and therefore no need to encode or decode it, and there would seem to be no job for CppSQLiteBinary. However, the SQLite version 3 change means that the only way to work with BLOB data would seem to be using prepared statements (CppSQLiteStatement). Not really a problem, but up until now, CppSQLiteBinary had allowed use of (encoded) binary data in calls to CppSQLiteDB::execQuery(), CppSQLiteDB::execDML() and on data returned from CppSQLiteDB::getTable().

sqlite_encode_binary() and sqlite_decode_binary() are still included in the SQLite version 3 source distribution, although it is not clear whether this is an error as they do not have the sqlite3 prefix, nor are they exported from the DLL. CppSQLite3 replicates the source to these 2 functions. This used to be the case with CppSQlite up to version 1.3 as up until version 2.8.15 of SQLite, they were not exported from the DLL. CppSQLite3Binary is an exact copy of CppSQLiteBinary, bundled with the source to sqlite_encode_binary() and sqlite_decode_binary(). This will allow easy porting between CppSQLite and CppSQLite3. Programs wishing to use sqlite3 BLOBs and their reduced storage space will not need to use CppSQLite3Binary, and will need to be rewritten anyway.

SQLite version 3 introduces changes to the data typing system used. See http://www.sqlite.org/datatype3.html . For this reason, CppSQLiteQuery::FieldType() has been replaced with 2 functions: CppSQLiteQuery::FieldDeclType() which returns the declared data type for the column as a string, and and CppSQLiteQuery::FieldDataType() whhich returns the actual type of the data stored in that column for the current row as one of the SQLite version 3 #defined vallues.

The demo programs have been changed slightly to demonstrate the new features, and also to account for SQLite version 3's different locking behaviour. See http://www.sqlite.org/lockingv3.html. Note that SQLite version 3.0.5 introduced a compile time option which changes locking behaviour, see http://www.sqlite.org/changes.html for more details.

The SQLite version 3 is available as a separate download at the top of this article.

Future Work

I may add support for the remaining SQLite features to CppSQLite. At the moment, this means stored procedures and aggregate functions.

Cross Platform Capability

Since version 1.2 of CppSQLite, I have tried hard not to do anything which is Microsoft specific, and have successfully compiled and run the demo programs on mingw32, as well as with Visual C++.

As mingw32 is based on GCC, there should be no major problems on Linux/Unix, although the multi threaded demo program CppSQLiteDemoMT uses the _beginthread() call, which will obviously not work. This can probably be easily fixed, using pthreads for example.

Contributions

Thanks to fellow Code Project members for suggestions and buf fixes for CppSQLite, and also to Mateusz Loskot for acting as a reviewer.

Conclusion

CppSQLite makes SQLite easier to use within a C++ program, yet doesn't provide significantly less power or efficiency than the flat C interface.

If nothing else, writing CppSQLite has provided the author with an insight into the power and simplicity of SQLite. It is hoped that readers of this article also benefit in some way.

History for CppSQLite (Targets SQLite 2.8.n)

• 1.0 - 3rd Mar 2004 - Initial version.
• 1.1 - 10th Mar 2004
  • Renamed CppSQLiteException::errorMess() to CppSQLiteException::errorMessage().
  • 2nd constructor to CppSQLiteException().
  • Now decodes error codes to strings in CppSQLiteException.
  • Call sqlite_finalize() immediately to get error details after problems with sqlite_step().
  • Added CppSQLiteBinary class.
• 1.2 - 2nd Apr 2004 - Not released.
  • Updated article.
  • Removed use of Microsoft specific extensions (I hope)
  • Check for NULL pointers
  • Updated for SQLite 2.8.13
  • Utilized sqlite_busy_timeout() and sqlite_interrupt() to help with multithreaded use
  • 2nd demonstration program for multithreaded use
  • Added support from pre-compiled SQL statements
  • Added ability to determine column types from CppSQLiteQuery
  • Added CppSQLiteDB::execScalar()
• 1.2.1 - 15th Apr 2004
  • Updated article following review
  • Use of C++ rather than C standard headers following review
• 1.3 - 21st May 2004
  • Added "BSD Style" License notice to source files
  • Fixed bugs on bind()
  • Added getIntField(), getStringField(), getFloatField()
  • Added overloaded functions to access fields by name
  • CppSQLiteDB::ExecDML() implemented with sqlite_exec() so multiple statements can be executed at once.
  • Added note in article about potential problem with return value from CppSQLiteDB::execDML()
  • Added managed C++ example program
• 1.4 - 30th August 2004
  • Upgraded to SQLite 2.8.15
  • Removed source for sqlite_encode_binary() and sqlite_decode_binary() as there are now exported from the SQLite DLL
  • Added article section on Managed C++

History for CppSQLite3 (Targets SQLite 3.n.n)

• 3.0 - 30th August 2004
  • Initial version to work with SQLite version 3.0.6
• 3.1 - 26th October 2004
  • Upgraded to vSQLite 3.0.8
  • Added CppSQLiteDB3::tableExists() function
  • Implemented getXXXXField using SQLite3 functions instead of atoi(), atof(), etc.
• 3.2 - 24th June, 2011
  • Bundled with SQLite3 version 3.4.0
  • CppSQLite3DB::SQLiteHeaderVersion(), CppSQLite3DB::SQLiteLibraryVersion(), CppSQLite3DB::SQLiteLibraryVersionNumber()
  • Fixed execScalar to handle a NULL result
  • Added Int64 functions to CppSQLite3Statement
  • Added CppSQLite3DB::IsAutoCommitOn(), can be used to test if a transaction is active
  • Throw exception from CppSQLite3DB::close() on error
  • Trap above exception in CppSQLite3DB::~CppSQLite3DB()
  • Bigger buffer size 256 in table
  • sqlite3_prepare replaced with sqlite3_prepare_v2
  • Fix to CppSQLite3DB::compile() as provided by Dave Rollins
  • Binds parameters by name as suggested by Dave Rollins

License

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

About the Author

Rob Groves Web Developer United Kingdom Member

Software developer using C/C++, ASP, .NET and SQL Server/Oracle relational databases.

Article Top

Rate this:

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

FAQ
Profile popupsNoise levelVery High High Medium Low Very LowLayoutNormal Expand Posts only Expand Posts & Replies Thread View No Javascript No JS + PreviewPer page10 25 50

New Message

Refresh

First PrevNext

My vote of 5

CrystalSkySea

3:31 29 Dec '11

It's useful to me! Thank you!I hope continue Update the ultility code!
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

Maximum length of a string or BLOB

youmin Kim

18:24 11 Dec '11

hi, above all, thanks for your sharing of this project. I am putting image blob (about 1MB) to db, but there is an error like this: "String or BLOB exceeded size limit" I think there is limitation though this link: http://www.sqlite.org/limits.html[^] and I'd like to ask 1. there is related settings in compiling this c++ wrapper. 2. there is any way to use sqlite3_limit() although this function is not declared in your sqlite3.h. thanks again.

Reply·Email·ViewThread·Permalink·Bookmark

Rate this message: 1 2 3 4 5

Re: Maximum length of a string or BLOB

Rob Groves

0:22 13 Dec '11

Hi, How are you trying to store BLOBs? In answer to your questions: 1: There are no related CppSQLite compiler settings. 2. Calls to sqlite3_limit could easily be added to the CppSQliteDB class, but as the default sqlite blob is 1GB I don't think this is the problem you are seeing. Look forward to hearing back from you. Rob.
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

Re: Maximum length of a string or BLOB

mocad_tom

11:58 19 Dec '11

I had the same problem. It depends on the maximum heap and/or stack size (I don't remember which one it was). The default settings are quite limiting. The easy way is to extend this settings in the Project Settings under the "Linker"-Tab. The hard way is to replace the allocBuffer- and clear-function with this: unsigned char* CppSQLite3Binary::allocBuffer(int nLen) { clear(); // Allow extra space for encoded binary as per comments in // SQLite encode.c See bottom of this file for implementation // of SQLite functions use 3 instead of 2 just to be sure ;-) mnBinaryLen = nLen; INT64 zwfusie = 257; INT64 zwfuvie = 254; INT64 biglen = nLen; INT64 bigtemp = 3 + ( (zwfusie * biglen) / zwfuvie ); INT64 upperlimit = 1000000000; // 1GB, you can make this bigger, // but you have to recompile sqlite3 with bigger settings if( bigtemp < upperlimit ) { mnBufferLen = bigtemp; } else { throw CppSQLite3Exception(CPPSQLITE_ERROR, "Cannot allocate memory", DONT_DELETE_MSG); } mpBuf = (unsigned char*)LocalAlloc( LMEM_FIXED ,mnBufferLen); if (!mpBuf) { throw CppSQLite3Exception(CPPSQLITE_ERROR, "Cannot allocate memory", DONT_DELETE_MSG); } mbEncoded = false; return mpBuf; } void CppSQLite3Binary::clear() { if (mpBuf) { mnBinaryLen = 0; mnBufferLen = 0; LocalFree(mpBuf); mpBuf = 0; } } With LocalAlloc you can get bigger chunks of memory and it don't affect the maximum heap size of your application. You have to include windows.h Best regards, Thomas

Reply·ViewThread·Permalink·Bookmark

Rate this message: 1 2 3 4 5

What's difference CppSQLiteTable AND CppSQLiteQuery one page query?

tangversion

9:21 21 Nov '11

Hello! In WinCE, use this class query one datebase, the records about above 86000 records. The Query App use page mode (first, prev, next, last). if use CppSQLiteQuery, It's no first,prev,Getcount; must like this 0. GetCount= replace "Select * from" with "Select count(*) from), And ReQuery Select * from to Get result. 1. first()= select * form log limit 1 2. prev() next()= select * form log limit 50 offset page*50 If use CppSQLiteTable, It's have getcount() and SetRow()=first, prev, next, last. But In embedded devices, CPU and Memory is very small. CppSQLiteQuery: need more CPU usage. CppSQLiteTable: need more memory. If CppSQLiteQuery have SetRow(), It's will powerful!

Reply·Email·ViewThread·Permalink·Bookmark

Rate this message: 1 2 3 4 5

New Version and blobs

BigAlBuchanan

11:54 31 Oct '11

Could I please get a copy of your new version as well please ? I have been experimenting with blobs larger than 1 mb and get an issue with the .SetBinary() function. When stepping through the code it will exit the program when doing the memcpy part of the function. Works fine with data under 1 mb so i am hping the new version will remedy this. Thanks and the wrapper excellent and helping me learn sqllite. Regards, Alan Buchanan

Reply·Email·ViewThread·Permalink·Bookmark

Rate this message: 1 2 3 4 5

Re: New Version and blobs

BigAlBuchanan

7:53 1 Nov '11

I ended up reading your full article and managed to get the Blobs working using the CppSQLIte3Statment methods as you suggested. I would still apprecaite your new version. Thanks for all your excellent work on this project Regards, Alan Buchanan
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

Re: New Version and blobs

Rob Groves

10:19 3 Nov '11

Hi, The latest version is now included in the article. Rob.
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

Can you, please, send me the latest version?

infal

16:12 25 Oct '11

Hi Rob! Can you please send me the latest version of your wrapper or tell me where to download? So, may be you have aready an client/server support within your wrapper - it would be very nice. I've found some good example for client/server here[^], but this one is written for an other wrapper. Of couse, it's possible to rewrite to use your wrapper (bacause we already use some older version of wrapper), but it maybe an unnecessary job. My mail is mailto:[e-mail]. Many thanks in advice! Alex

Reply·Email·ViewThread·Permalink·Bookmark

Rate this message: 1 2 3 4 5

Re: Can you, please, send me the latest version?

Rob Groves

10:23 3 Nov '11

Hi, The latest version is now included in the article. As for client server support, an interesting project, and I have buily very similar things in the past (not sqlite related), but just don't have the time right now. Rob.
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

Re: Can you, please, send me the latest version?

infal

11:32 25 Nov '11

Hi Rob, already i have done 80% of client/server code for our company, and this rocks. But I've rewrite much of wrapper code. The idea is following: wrapper does not call any sqlite3 code directly and does not hold any references such as to prepared statement. Moreover, this hols and maintains ID's of statements/queries. The wrapper uses internally stubs, and is generally attached to an proxy. Stubs calls proxy methods. It should be at least two proxies - direct and network. The direct proxy communicates directly with SQLite and calls sqlite3 command, additionally this proxy should maintain a map with prepared statement/queries and generate ID's. The network proxy tells to network client which in turn communicates withs server. The server is connected to direct proxy and routes client calls to that proxy and sends data back. A've found code for simple SQLIte client/server communication. This implement some sqlite3 command, but is far not completed and has some bugs. This is already improved by me and integrated together with wrapper. As soon as I will be completelly ready, I will ask our management, if it would be possible to share the code back to community. If it would be allowed, this would be a really great possibility to join mulitple projekts and works together to one relly great projekt. And probably this should be referenced from SQLite projekt page.

Reply·Email·ViewThread·Permalink·Bookmark

Rate this message: 1 2 3 4 5

unicode version request

Sand Wen

18:41 30 Jul '11

Hi Rob: Sorry to bother you, I can't find the unicode version, do you wrapper it? Waiting for answer?
Reply·Email·ViewThread·Permalink·Bookmark	2.00/5 (1 vote) Rate this message: 1 2 3 4 5

Re: unicode version request

elha58

13:20 26 Sep '11

Have a look at http://softvoile.com/development/CppSQLite3U/ Hope it helps.
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

New version

Member 8055108

0:41 4 Jul '11

Hi Rob, could you send me the latest version, too? [e-mail] Thanks in advance Alejandro
Reply·Email·ViewThread·Permalink·Bookmark	2.00/5 (2 votes) Rate this message: 1 2 3 4 5

New version request

thready

15:19 23 Jun '11

Hi Rob, sorry to bother but your code is too good not to bother! Can I please have the new version as well? [e-mail] Thank you very much for sharing!
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

Please make the new version available on the website or confirm its not there

Wesseldr

15:10 9 Jun '11

Hi Rob, Thanks for your great work on the wrapper. As it looks like right now there is confusion whenever there is a new version available or not. If there is could you please make it available for download on the website? And if not please drop an post as a reply on this site that there is no new version and perhaps a link to an alternative. Many thanks in advance, Wessel
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

Re: Please make the new version available on the website or confirm its not there

Rob Groves

0:05 24 Jun '11

I have emailed all those who asked for the latest version (sorry for the delay). I have submitted an update to CodeProject but do not have the time to edit the HTML, hopefully they will be able to just update the download file and version history for me.
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

Re: Please make the new version available on the website or confirm its not there

wilsonsuryajaya

19:55 18 Jul '11

Can I have a copy of the latest library as well? My email address is [e-mail]. Thanks.
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

Re: Please make the new version available on the website or confirm its not there

milesyou

20:20 9 Sep '11

Can I have a copy of the latest library as well? My email address is [e-mail]. Thanks.
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

Re: Please make the new version available on the website or confirm its not there

zhangruijing

5:45 26 Sep '11

Hi Rob, could you send me the latest version, too? my email is : [e-mail]. thanks a lot
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

New version request

Schwerin

16:45 1 Jun '11

Hi Rob, could you send me the latest version, too? --> codeproject(at)sonowin.de Thank you very much for your great work. Christian
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

New version request

eitanl

13:01 21 Apr '11

Hello Rob ========== Thank you very much for sharing your work with us. Can you send the latest version to me? [e-mail] Thank you. Eitan
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

New version

manuel muñoz

0:04 26 Mar '11

The best SQLite3 wrapper classes. Can you send the latest version to me? [e-mail] Thanks for your great work.
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

new version

kendrickchn

4:32 9 Mar '11

Hi, thanks for your work. I'm novice with SQLLite and your code seems very usefull. Could you please send me the new code too? Kendrick email:kendrickchn at gmail dot com
Reply·Email·ViewThread·Permalink·Bookmark	Rate this message: 1 2 3 4 5

[Fix] Table is locked after running the CppSQLiteBinary test in the example

titusmagnus

1:47 8 Mar '11

Hello, In the example posted on the main page: CppSQLite - C++ Wrapper for SQLite[^] An problem occurs after the CppSQLiteBinary is ran. The reason is that the internal VM generated when calling: q = db.execQuery("select data from bindata where desc = 'testing';"); ... never gets released. To fix this problem, just add the following at the end of the CppSQLiteBinary section: q.finalize(); Cheers, -- Tito

Reply·Email·ViewThread·Permalink·Bookmark

5.00/5 (1 vote) Rate this message: 1 2 3 4 5

Last Visit: 13:26 31 Dec '11 Last Update: 13:26 31 Dec '11

1234567891011 Next »

General News Suggestion Question Bug Answer Joke Rant Admin

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

Permalink | Advertise | Privacy | Mobile
Web04 | 2.5.111208.1 | Last Updated 24 Jun 2011

Article Copyright 2004 by Rob Groves
Everything else Copyright © CodeProject, 1999-2011
Terms of Use

Layout: fixed | fluid

dotConnect for SQLite
dotConnect for SQLite is an enhanced database connectivity solution...
www.devart.com

dotConnect for SQLite Standard Edition
dotConnect for SQLite Standard Edition is a free of charge database...
www.devart.com

See Also...

Another Embedded Database For C++

SQLite3 MFC Wrapper

Simple C# Wrapper for SQLite

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

LinqToSQL: Comprehensive Support for SQLite, Microsoft Access, SQServer2000/2005

A SQLite Server/Client Implementation

SQLite Membership, Role, and Profile Providers

An Easy to Use Wrapper for SQLite3 (Totally Free Embedded Database Engine!)

Using SQLite in your C# Application

Audiobook Player for iPhone

SQLite GUI

Using SQLite Embedded Database with Entity Framework and Linq-to-SQL

Convert SQL Server DB to SQLite DB

Disconnected Client Architecture

Case-Insensitive Sort of UTF8 Data Using System.Data.SQLite

The Daily Insider

30 free programming books

About the Author

Rob Groves Web Developer United Kingdom Member

Software developer using C/C++, ASP, .NET and SQL Server/Oracle relational databases.