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






4.81/5 (29 votes)
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:
-
int Open( LPCTSTR strFileName)
-
void Close()
-
bool IsOpen()
Is a database open? (Safe function)
-
sqlite3 * GetPtr()
-
int GetLastError()
-
void CleanError()
-
Table QuerySQL( LPCTSTR strSQL )
-
TablePtr QuerySQL2( LPCTSTR strSQL)
-
int ExecuteSQL( LPCTSTR strSQL)
-
bool IsSQLComplete()
-
int GetLastChangesCount()
-
int ReadBlob( LPCTSTR strSQL, BYTE ** pData, int * iLenBlob)
-
int WriteBlob( LPCTSTR strSQL, BYTE * pData, int iLenBlob)
-
sqlite_int64 GetLastInsertRowID()
- Functions
Open()
,GetLastError()
, andExecuteSQL()
return aSQLITE_
error, see sqlite3.h in the library for error definitions.
Opens a new database, creating it if it does not already exist.
Closes the database...
Returns the handle to the sqlite3
database.
Returns the last error...
Cleans the last error...
Executes a SQL query, returning the results in the Table
class.
The same as above, except it returns a pointer to the Table
class, wrapped in a TablePtr
.
Executes a SQL query, without expecting any results.
Checks if a SQL statement is complete (by the SQLite3 ideas and specs...).
Returns the count of changes made by the last SQL statement.
Reads a BLOB value into pData
, and the length of the BLOB goes into iLenBlob
. Expects only 1 record, 1 column!
Writes a BLOB value using an INSERT
or UPDATE
SQL statement.
Gets the row-ID of the last row inserted using INSERT
. See the SQLite documentation for more information.
In the Table
class, there are the following methods:
-
int GetColCount()
-
int GetRowCount()
-
int GetCurRow()
-
LPCTSTR GetColName( int iCol)
-
bool GoFirst()
bool GoLast()
/GoNext()
/GoPrev()
- Navigates through the records...-
bool GoRow()
-
LPCTSTR GetValue(LPCTSTR lpColName)
-
LPCTSTR GetValue(int iColIndex)
-
LPCTSTR operator [] (LPCTSTR lpColName)
-
LPCTSTR operator [] (int iColIndex)
-
void JoinTable(Table & tblJoin)
Returns the number of columns. Safe function.
Returns the number of rows. Safe function.
Returns the currently selected row. Safe function.
Returns the name of the column at index iCol
.
Navigates to the first record, returning false
if no records exist. Safe function (returns false
if Table
is null
).
Navigates to a specific record. Safe function (returns false
if Table
is null
or out of bounds).
Gets the value of the current row, in the column specified by lpColName
(name of column).
Gets the value of the current row, in the iColIndex
column (by column index).
Same as GetValue()
, for using with []
.
Same as GetValue()
, for using with []
.
Adds the rows of tblJoin
to this table, if the column count is equal (or empty).
In the TablePtr
class:
-
Table * m_pTable
-
Table * Detach()
-
void Attach( Table * pTable)
-
operator ()
-
operator bool()
public
member which points to a Table
class.
Detaches the class from the Table
, and returns the Table
that was just detached...
Frees the current Table
, and attaches the pTable
.
Now you do not have to use TablePtr::m_pTable
, you can just use TablePtr()
.
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 string
s... 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 theTable
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 theTablePtr
. - Also,
GetLastInsertRowID()
is implemented now. - 20/11/2007
- Added the simple
GetCurRow()
to theTable
class... - Also added the documentation about
GoRow()
. - 23/12/2007
- Added the
bool
operator forTablePtr
, so you can "if (TablePtr) {...}
" to check if them_pTable
member is valid.