Click here to Skip to main content
15,867,453 members
Articles / Desktop Programming / MFC

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

Rate me:
Please Sign up or sign in to vote.
4.81/5 (32 votes)
23 Dec 2007CPOL4 min read 120.7K   2.4K   104   38
I have created classes to wrap the SQLite3 library, to remove many headaches...

Introduction

Recently I was looking for a solution for an embedded SQL/database library so I won't have to redistribute DAO/Jet/SQL Server Express/ODBC or some other database driver...

I ran into SQLite3, which is a portable library, written completely in C, that allows you to create/manage databases, with SQL statements. It is Open Source, does not need any kind of license, has a compact footprint (size depending on compiler optimization, but just about 200 KB!), and it has no external dependencies.

I downloaded SQLite3 (http://www.sqlite.org) and wanted to immediately start using it.

Wait a moment, no OOP??? (Object oriented programming, C++...)

As I mentioned before, the library is completely written in C. And not only that - it also works with multi-byte UTF-8 encoding...

I found out immediately that I needed to create some classes to wrap the library so I will be able to use it without any headaches...

SQLite is a database engine that you can download, add to your project, and run! There's absolutely zero-configuration needed!

Using the Code

There are basically two classes: Database and Table, under the namespace SQLite.

Later update: Another class TablePtr to wrap a pointer to Table class.

Both classes use TCHAR to support Unicode. The use is very simple, everything is documented inside the code. The classes expect sqlite3 in folder .\SQLite\. You can change it in the source code for wherever you want.

When a function is specified as 'Safe', it means that even if it is a null object (e.g., Table * pTbl=0) the show will go on...

In the Database class, there are the following methods:

  • C++
    int Open( LPCTSTR strFileName)
  • Opens a new database, creating it if it does not already exist.

  • C++
    void Close()
  • Closes the database...

  • C++
    bool IsOpen()

    Is a database open? (Safe function)

  • C++
    sqlite3 * GetPtr()
  • Returns the handle to the sqlite3 database.

  • C++
    int GetLastError()
  • Returns the last error...

  • C++
    void CleanError()
  • Cleans the last error...

  • C++
    Table QuerySQL( LPCTSTR strSQL )
  • Executes a SQL query, returning the results in the Table class.

  • C++
    TablePtr QuerySQL2( LPCTSTR strSQL)
  • The same as above, except it returns a pointer to the Table class, wrapped in a TablePtr.

  • C++
    int ExecuteSQL( LPCTSTR strSQL)
  • Executes a SQL query, without expecting any results.

  • C++
    bool IsSQLComplete()
  • Checks if a SQL statement is complete (by the SQLite3 ideas and specs...).

  • C++
    int GetLastChangesCount()
  • Returns the count of changes made by the last SQL statement.

  • C++
    int ReadBlob( LPCTSTR strSQL, BYTE ** pData, int * iLenBlob)
  • Reads a BLOB value into pData, and the length of the BLOB goes into iLenBlob. Expects only 1 record, 1 column!

  • C++
    int WriteBlob( LPCTSTR strSQL, BYTE * pData, int iLenBlob)
  • Writes a BLOB value using an INSERT or UPDATE SQL statement.

  • C++
    sqlite_int64 GetLastInsertRowID()
  • Gets the row-ID of the last row inserted using INSERT. See the SQLite documentation for more information.

  • Functions Open(), GetLastError(), and ExecuteSQL() return a SQLITE_ error, see sqlite3.h in the library for error definitions.

In the Table class, there are the following methods:

  • C++
    int GetColCount()
  • Returns the number of columns. Safe function.

  • C++
    int GetRowCount()
  • Returns the number of rows. Safe function.

  • C++
    int GetCurRow()
  • Returns the currently selected row. Safe function.

  • C++
    LPCTSTR GetColName( int iCol)
  • Returns the name of the column at index iCol.

  • C++
    bool GoFirst()
  • Navigates to the first record, returning false if no records exist. Safe function (returns false if Table is null).

  • bool GoLast() / GoNext() / GoPrev() - Navigates through the records...
  • C++
    bool GoRow()
  • Navigates to a specific record. Safe function (returns false if Table is null or out of bounds).

  • C++
    LPCTSTR GetValue(LPCTSTR lpColName)
  • Gets the value of the current row, in the column specified by lpColName (name of column).

  • C++
    LPCTSTR GetValue(int iColIndex)
  • Gets the value of the current row, in the iColIndex column (by column index).

  • C++
    LPCTSTR operator [] (LPCTSTR lpColName)
  • Same as GetValue(), for using with [].

  • C++
    LPCTSTR operator [] (int iColIndex)
  • Same as GetValue(), for using with [].

  • C++
    void JoinTable(Table & tblJoin)
  • Adds the rows of tblJoin to this table, if the column count is equal (or empty).

In the TablePtr class:

  • C++
    Table * m_pTable
  • public member which points to a Table class.

  • C++
    Table * Detach()
  • Detaches the class from the Table, and returns the Table that was just detached...

  • C++
    void Attach( Table * pTable)
  • Frees the current Table, and attaches the pTable.

  • C++
    operator ()
  • Now you do not have to use TablePtr::m_pTable, you can just use TablePtr().

  • C++
    operator bool()
  • Now you do not have to check if m_pTable is valid. Just "if (TablePtr) {...}".

Points of Interest

You may have noticed that you can only get values as strings... That is because SQLite3 does not restrict any field to its data type... so you can put a float instead of a string or an integer, or a string instead of an integer for example...

But SQLite3 tries to convert to the 'preferred' datatype when it is possible.

So you can always expect a string, then if you know that it should be an integer, use _ttoi() to convert, or _tstof() to convert to float...

Comments

If you have any comments, requests, bugs... feel free to contact me, and I will update as soon as I can.

Updates

  • 20/08/2007
    • Just added a basic interface for BLOB reading and writing, see the comments in the source code.
  • 25/08/2007
    • Added the TablePtr class to wrap a pointer to the Table class. Suggested by Douglas R. Keesler, thanks!
  • 01/10/2007
    • Made some modifications to make the code more safe.
    • Also implemented JoinTable() for joining two table classes, and a () operator for the TablePtr.
    • Also, GetLastInsertRowID() is implemented now.
  • 20/11/2007
    • Added the simple GetCurRow() to the Table class...
    • Also added the documentation about GoRow().
  • 23/12/2007
    • Added the bool operator for TablePtr, so you can "if (TablePtr) {...}" to check if the m_pTable member is valid.

License

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


Written By
Software Developer (Senior)
Israel Israel
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionLarge warnning prompting when compiling Pin
MFC20088-Mar-13 16:13
MFC20088-Mar-13 16:13 
GeneralMy vote of 5 Pin
Eduardo_David5-Jun-11 4:42
Eduardo_David5-Jun-11 4:42 
GeneralTransaction management Pin
softwareguy7417-Oct-10 4:31
softwareguy7417-Oct-10 4:31 
GeneralMy vote of 4 Pin
naresh.3140547-Oct-10 20:28
naresh.3140547-Oct-10 20:28 
GeneralDoesn't work with path with exotic characters (Chinese for example) Pin
chachatof4-Nov-09 23:48
chachatof4-Nov-09 23:48 
GeneralRe: Doesn't work with path with exotic characters (Chinese for example) Pin
Daniel Cohen Gindi4-Nov-09 23:52
Daniel Cohen Gindi4-Nov-09 23:52 
GeneralRe: Doesn't work with path with exotic characters (Chinese for example) Pin
chachatof5-Nov-09 0:00
chachatof5-Nov-09 0:00 
GeneralRe: Doesn't work with path with exotic characters (Chinese for example) Pin
Daniel Cohen Gindi5-Nov-09 0:32
Daniel Cohen Gindi5-Nov-09 0:32 
GeneralRe: Doesn't work with path with exotic characters (Chinese for example) Pin
chachatof23-Nov-09 23:39
chachatof23-Nov-09 23:39 
GeneralRe: Doesn't work with path with exotic characters (Chinese for example) Pin
loyal ginger19-Nov-09 8:57
loyal ginger19-Nov-09 8:57 
GeneralGood Pin
dxlee8-Oct-09 10:37
dxlee8-Oct-09 10:37 
QuestionMemory Leakage in wrapper for SQLite3 Pin
VijayChavan00725-May-09 5:50
VijayChavan00725-May-09 5:50 
Generaldoes NOT compile under VC6 Pin
Mister Transistor17-Dec-08 10:46
Mister Transistor17-Dec-08 10:46 
GeneralDetail in CSocketStack Pin
KarstenK28-Jan-08 1:34
mveKarstenK28-Jan-08 1:34 
why do you use m_pStreamHeader an instance of the parent class? Does the inheritance not the job?

Greetings from Germany

GeneralRe: Detail in CSocketStack Pin
Daniel Cohen Gindi28-Jan-08 7:59
Daniel Cohen Gindi28-Jan-08 7:59 
GeneralLack of Samples Pin
yarp15-Jan-08 5:19
yarp15-Jan-08 5:19 
GeneralRe: Lack of Samples Pin
Daniel Cohen Gindi28-Jan-08 8:05
Daniel Cohen Gindi28-Jan-08 8:05 
GeneralRe: Lack of Samples Pin
P.Pronk15-Dec-08 5:26
P.Pronk15-Dec-08 5:26 
GeneralComing soon, SQLite server/client! Pin
Daniel Cohen Gindi23-Dec-07 9:01
Daniel Cohen Gindi23-Dec-07 9:01 
GeneralRe: Coming soon, SQLite server/client! Pin
Member 238825723-Dec-07 10:55
Member 238825723-Dec-07 10:55 
GeneralGuys, theres an update! Pin
Daniel Cohen Gindi1-Oct-07 0:37
Daniel Cohen Gindi1-Oct-07 0:37 
GeneralRe: Guys, theres an update! Pin
rajas2-Oct-07 5:11
rajas2-Oct-07 5:11 
GeneralRe: Guys, theres an update! Pin
Daniel Cohen Gindi2-Oct-07 6:40
Daniel Cohen Gindi2-Oct-07 6:40 
GeneralRe: Guys, theres an update! Pin
gnk3-Oct-07 13:50
gnk3-Oct-07 13:50 
GeneralRe: Guys, theres an update! Pin
Daniel Cohen Gindi4-Oct-07 6:30
Daniel Cohen Gindi4-Oct-07 6:30 

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

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