Back to LiteX page

LiteX++ - C++ wrapper around native SQLite3 C API

LiteX++ is a simple C++ wrapper around SQLite3 C native API.
Sources of this library you can find in LiteX_pp subdirectory of LiteX package (see also library usage).
To use this library you must have basic knowledge about C++ language and SQLite3 native C API.


Table of contents.


Main features of LiteX++ library.


Class reference.

All classes and helper functions are groupped into litex namespace:

using namespace litex;

SQLiteException class.

This class is used by LiteX++ library to throw exceptions indicating error from SQLite3 library.

Methods:

Method(s)

Description

Sample code / Comments

int get_ErrorCode() const

Gets error code from SQLite3 library.
Look at sqlite3.h header to see error codes and their descriptions.

try

{

....

}

catch( SQLiteException& e )

{

tcerr << _T("Error code: ") << e.get_ErrorCode() << endl;

}

const _tstring& get_Message() const

Gets error mesage from SQLite3 library.
This is error text returned by sqlite3_errmsg(16) function.

try

{

....

}

catch( SQLiteException& e )

{

tcerr << _T("Error message: ") << e.get_Message() << endl;

}

static void Throw( int nErrorCode, sqlite3* pDb )
static void Throw( int nErrorCode )

Throws SQLiteException when error code is not equal to SQLITE_OK.
This methods are used internally by LiteX++ to throw SQLiteException exceptions when nessesary.

SQLiteConnection db;

...

SQLiteException::Throw( nErrorCode, db );



SQLiteRuntimeException class.

This class is used by LiteX++ library to indicate its own runtime errors.
Note that not all errors are indicated. For example parameters validation.
Parameters validation is prformed only in DEBUG mode by assert macro/function from <cassert> header.
This enables to produce fast code without unnessesary validation in RELEASE mode.

Methods:

Method(s)

Description

Sample code / Comments

_tstring get_Message() const

Gets error message.

try

{

...

}

catch( SQLiteRuntimeException& e )

{

tcerr << _T("LiteX++ exception: ") << e.get_Message() << endl;

}

static void Throw( const _tstring& sMsg )

Throws SQLiteRuntimeException with specified error message.




SQLiteConnection class.

This is a wrapper class around sqlite3* handle and represents connection to SQLite database.

Constructors:

Constructor

Description

Sample code / Comments

SQLiteConnection()

Default constructor.
Object initialization will be performed in future.

SQLiteConnection db;

db.Open( _T("some.db") );

if ( db )

{

db.Close();

}

SQLiteConnection( const _tstring& sDbPath )

Initializes object and opens sDbPath database file.
If database file cannot be open SQLiteException exception is thrown.


SQLiteConnection( const TCHAR* pszDbPath )

Initializes object and opens pszDbPath database file.
If database file cannot be open SQLiteException exception is thrown.

SQLiteConnection db( _T("some.db") );

SQLiteConnection( SQLiteConnection& db )

Copy constructor. Constructing object takes ownership of sqlite3* handle and db object is detached from this handle.

SQLiteConnection db( _T("some.db") );

if ( db ) tcout << _T("Database is opened.") << endl;

SQLiteConnection other_db( db );

if ( !db ) tcout << _T("Database is detached.") << endl;

if ( other_db ) tcout << _T("Databasse is attached.") << endl;

Methods:

Method(s)

Description

Sample code / Comments

bool Open( const _tstring& sDbPath )

Creates and/or opens sDbPath database file.

Returns true if database is created/opened.
In case of failure no exception is thrown and method return false.

Wrapper aroud sqlite3_open(16) function.

bool OpenInMemory()

Creates and opens empty in-memory database.
Contents of in-memory database is destoryed when database is closed.

If you want to create in-memory database in constructor use MEMORY_DB string:
SQLiteConnection db( MEMORY_DB );

void Close()

Closes previously opened database.

Wrapper around sqlite3_close function.

void Interrupt()

This function causes any pending database operation to abort and return at its earliest opportunity.

Wrapper around sqlite3_interrupt function.

sqlite_int64 get_LastInsertRowid() const

The following routine returns the integer key of the most recent insert in the database.

Wrapper around sqlite3_last_insert_rowid function.

int get_Changes() const

This function returns the number of database rows that were changed (or inserted or deleted) by the most recent executed statement.

Wrapper around sqlite3_changes function.

int get_TotalChanges() const

This function returns the number of database rows that have been
modified by INSERT, UPDATE or DELETE statements since the database handle was opened.

Wrapper around sqlite3_total_changes function.

void BatchExecute( const TCHAR* pszSql )
void BatchExecute( const _tstring& sSql )

Executes many SQL statements at once.

db.BatchExecute( _T("CREATE INDEX d ON Test( d DESC ); CREATE INDEX e ON Test( e )") );

void ExecuteNonQuery( const TCHAR* szSql )
void
ExecuteNonQuery( const _tstring& sSql )

Executes single SQL statement that doesn't returns any results.

db.ExecuteNonQuery( _T("CREATE TABLE Test( a INTEGER PRIMARY KEY, b, c, d, e, f )") );

int ExecuteScalarInt( const _tstring& sSql )
sqlite_int64 ExecuteScalarInt64(
const _tstring& sSql )
double ExecuteScalarDouble( const _tstring& sSql )
_tstring ExecuteScalarText(
const _tstring& sSql )

int ExecuteScalarInt( const TCHAR* pszSql )
sqlite_int64 ExecuteScalarInt64(
const TCHAR* pszSql )
double ExecuteScalarDouble( const TCHAR* pszSql )
_tstring ExecuteScalarText(
const TCHAR* pszSql );

Executes singleton SQL statement (statement that returs one row with one column) and returns its result.
If statement doesn't returns any row. SQLiteRuntimeException is thrown.

In fact statements may returs many rows witch many columns but only first row is fetched and and value from first column is returned.



int nMax = db.ExecuteScalarInt( _T("SELECT max(a) FROM test") );

SQLiteStatement Prepare( const _tstring& sSql)
SQLiteStatement Prepare( const TCHAR* pszSql )

Prepares SQL statement and returns SQLiteStatement object.


void BeginTransaction()
void CommitTransaction()
void RollbackTransaction()

Transaction begining, commiting and rollbacking.

db.BeginTransaction()
is shortcut to
db.ExecuteNonQuery(_T("BEGIN TRANSACTION"));

etc.

static _tstring get_VersionString()

SQLite3 library version string.

tcout << _T("Hello from SQLite3 version ") << SQLiteConnection::get_VersionString() << endl;

static int get_VersionNumber()

SQLitte3 library version number.

tcout << _T("Hello from SQLite3 version ") << SQLiteConnection::get_VersionNumber() << endl;



Operators:

Operator

Description

Sample code / Comments

operator sqlite3*() const

Access to sqlite3* handle.

sqlite3* pDb = db;

operator bool() const

Test if database is open.



SQLiteStatement class.

This is a wrapper class around sqlite3_statement* handle and represents prepared SQL statement.

In most cases you do not create this object explicitly but use SQLiteConnection::Prepare method to build this object.

Constructors:

Constructor

Description

Sample code / Comments

SQLiteStatement( SQLiteConnection& connection )

Initializes empty object.

SQLiteStatement stmt(db);

...

stmt.Prepare(_T("SELECT * FROM Test"));

SQLiteStatement( SQLiteConnection& connection, const TCHAR* pszSql )

Initializes object and prepares pszSql statement.
If statement preparation fails SQLiteException is thrown.

SQLiteStatement stmt( db, _T("SELECT * FROM Test") );

SQLiteStatement( SQLiteConnection& connection, const _tstring& sSql )

nitializes object and prepares sSql statement.
If statement preparation fails SQLiteException is thrown.


SQLiteStatement( SQLiteStatement& stmt )

Copy constructor.
Stmt object will be detached form sqlite3_stmt* handle.

SQLiteStatement stmt( db.Prepare(_T("SELECT * FROM Test")) );

Methods:

Method(s)

Description

Sample code / Comments

SQLiteConnection& get_Connection() const

Gets SQLiteConnection object reference associated with this object.

SQliteConnection& stmt_db = stmt.get_Connection();

void Prepare( const _tstring& sSql )
void Prepare( const TCHAR* pszSql )

Prepares SQL statement.
If statement compilation fails SQLiteException is thrown.

stmt.Prepare(_T("SELECT * FROM Test"));

void Reset()

Resets previously prepared statement to its initial state, ready to re-executed.

stmt.Reset()

void Finalize()

Deletes previously prepared statement.
Releases sqlite3_stmt* handle.

Destructor also finalizes prepared statement if you do not call this method.

int get_ParameterCount() const

Number of statement parameters.
Statement must be prepared.


_tstring get_ParameterName( int nParam ) const

Gets name of nParam-th parameter.
Use only when you use named parmaters.
Parameters are numbered from 1!

int nParamCount = stmt.get_ParameterCount();

for( int i=1; i<=nParamCount; i++ )

{

tcout << _T("Parameter ") << i << _T(": ") << stmt.get_ParameterName(i) << endl;

}


void BindBlob( int nParam, const void* pBlock, int nSize )
void BindBlob( const _tstring& sParam, const void* pBlock, int nSize )
void BindBlob( const TCHAR* pszParam, const void* pBlock, int nSize )

Binds BLOB to statement's parameter.
You may specify parameter by index (nParam) or by name (sParam,pszParam).

static const BYTE blob[4] = { 0x01, 0x02, 0x03, 0x04 };

...

stmt.BindBlob( 1, blob, 4 )

stmt.BindBlob( _T(":blob_parameter"), blob, 4 );

void BindDouble( int nParam, double val )
void BindDouble( const _tstring& sParam, double val )
void BindDouble( const TCHAR* pszParam, double val )

Binds floating-point value to statement's parameter.
You may specify parameter by index (nParam) or by name (sParam,pszParam).

stmt.BindDouble( 2, 1.7888888 );

stmt.BindDouble( _T(":double_parameter"), 1.7888888 );

void BindInt( int nParam, int val )
void BindInt( const _tstring& sParam, int val )
void BindInt( const TCHAR* pszParam, int val )

Binds integer value to statement's parameter.
You may specify parameter by index (nParam) or by name (sParam,pszParam).

stmt.BindInt( 3, 1234 );

stmt.BindInt( _T(":int_parameter"), 1234 );

void BindInt64( int nParam, sqlite_int64 val )
void BindInt64( const _tstring& sParam, sqlite_int64 val )
void BindInt64( const TCHAR* pszParam, sqlite_int64 val )

Binds 64-bit integer value to statement's parameter.
You may specify parameter by index (nParam) or by name (sParam,pszParam).

stmt.BindInt64( 4, 1234123456 );

stmt.BindInt64( _T(":int64_parameter"), 1234123456 );

void BindText( int nParam, const TCHAR* val )
void BindText( int nParam, const _tstring& val )
void BindText( const _tstring& sParam, const TCHAR* val )
void BindText( const _tstring& sParam, const _tstring& val )
void BindText( const TCHAR* pszParam, const TCHAR* val )
void BindText( const TCHAR* pszParam, const _tstring& val )

Binds text to statement's parameter.
You may specify parameter by index (nParam) or by name (sParam,pszParam).

tostringstream ss;

ss << _T("-=<") << hex << rand() << _T(">=-");

stmt.BindText( 5, ss.str() );

stmt.BindText( _T(":str_parameter"), SQLiteConnection::get_VersionString() )

void BindNull( int nParam )
void BindNull( const _tstring& sParam )
void BindNull( const TCHAR* pszParam )

Binds NULL value to statement's parameter.
You may specify parameter by index (nParam) or by name (sParam,pszParam).

stmt.BindNull( 6 );

stmt.BindNull( _T(":nil_parameter") );

int get_ColumnCount() const

Returns the number of columns in the result set returned by the prepared statement.


_tstring get_ColumnName( int nColIdx )

This function returns the column heading for the nColIdx-th column of prepared statement.

int nColumnCount = stmt.get_ColumnCount();

for( int i=0; i<nColumnCount; i++ )

{

tcout << _T("Column ") << 1 << _T(": ") << stmt.get_ColumnName(i) << endl;

}


_tstring get_ColumnDecltype( int nColIdx )

Returns declared type of nColIDx-th column.

tcout << _T("Declared column type: ") << stmt.get_ColumnDecltype(0) << endl;

bool Step()

One step execution of prepared statement. One step return one row.

Returns true if new row is fetched and flase when end of record set was reached.

SQLiteStatement stmt( db, _T("SELECT * FROM TEST") );

while( stmt.Step() )

{

// dump data here

}


void Execute()

Non-query statement execution.
After execution statement is ready to re-execute.



SQLiteStatement stmt( db, _T("INSERT INTO Table(a) VALUES(?)") );

for( int i=0; i<100; i++ )

{

stmt.BindInt( 1, i );

stmt.Execute();

}



Instead of calling Execute() method you can use following sequence:



stmt.Step();
stmt.Reset();


int get_DataCount() const

Returns the number of values in the current row of the result set.
You must call Step method before.


const void* get_ColumnBlob( int nColIdx, int& nBlobSize ) const

Returns BLOB and its size of nColIdx-th column in the current row of the result set.

int nBlobSize;

const void* pBlob = stmt.get_ColumnBlob(0,nBlobSize);

tcout << _T("Blob size: ") << nBlobSize << endl;

double get_ColumnDouble( int nColIdx ) const

Returns floating-point value of nColIdx-th column in the current row of the result set.


int get_ColumnInt( int nColIdx ) const

Returns integer value of nColIdx-th column in the current row of the result set.


sqlite_int64 get_ColumnInt64( int nColIdx ) const

Returns 64-bit integer value of nColIdx-th column in the current row of the result set.


_tstring get_ColumnText( int nColIdx ) const

Returns text value of nColIdx-th column in the current row of the result set.


int get_ColumnType( int nColIdx ) const

Returns type of nColIdx-th column in the current row of the result set.
Possible values are (values taken from sqlite3.h header):

SQLITE_INTEGER = 1
SQLITE_FLOAT = 2
SQLITE_TEXT = 3
SQLITE_BLOB = 4
SQLITE_NULL = 5

switch( stmt.get_ColumnType(n) )

{

case SQLITE_INTEGER:

tcout << _T("INTEGER: ") << stmt.get_ColumnInt(n) << endl;

break;

case SQLITE_FLOAT:

tcout << _T("FLOAT: ") << stmt.get_ColumnDouble(n) << endl;

break;

case SQLITE_TEXT:

tcout << _T("TEXT: ") << stmt.get_ColumnText(n) << endl;

break;

case SQLITE_BLOB:

tcout << _T("BLOB") << endl;

break;

case SQLITE_NULL:

tcout << _T("NULL") << endl;

break;

}


bool IsNull( int nColIdx ) const

Tests if nColIdx-th column in the current row of the result set has NULL value.

if ( stmt.IsNull(n) ) tcout << _T("NULL value") << endl;

Operators:

Operator

Description

Sample code / Comments

operator sqlite3_stmt*() const

Access to sqlite3_stmt* handle.

sqlite3_stmt* pStmt = stmt;

operator bool() const

Test if object is prepared.



Library usage.

Most of library stuff is included in LiteX.hpp header file. Few functions are implemented in LiteX.cpp file.
To use this library in your project simply add these two files to your project ad use #include "LiteX.hpp"
directive in every module you want to use this library. There's no LIB nor DLL file. That's because most of class methods are inline.
All classes and functions are groupped into litex namespace. Your project also must have access to sqlite3.h header from SQLite3 package.

All files you can find in LiteX package in LiteX_pp subdirectory. In this directory you can also find simple console application that demonstrates how to use LiteX++ library.

This library works only on Windows platform. Porting to other platforms is possible and requires text encoding routines change only.
This library was tested with Visual Stusio C++ 6.0 (project files included) and Visual Studio C++ .NET 2003/2005 compiler (only 2005 project files included).


Using LiteX++ together with LiteX Automation.

When you register LiteX Automation library information about location of sqlite3.dll library is stored into registry. LiteX Automation library exsports all native SQLite3 functions.

If you create application that uses dynamically linked native SQLite3 API you must put another copy of sqlite3.dll into the directory where this aplication resides (best solution) or into directory specified by PATH enviroment variable. This second version of DLL is unnessesary but your application must read LiteX Automation library location from registry. You may by hand call LoadLibray and then GetProcAddress functions but writing C++ applications you rather use header file with function definitions (sqlite3.h) and static import library (sqlite3.lib).

If you specify sqlite3.dll in your C++ project as delay-loaded library (/DELAYLIB linker option) you have control how to load delay-loaded DLLs by own notification hooks. LiteX++ library currently implements such simple notification hook that reads LiteX Automation library location and load this DLL if nessesary. This even works with Unicode version of LiteX Automation (sqlite3u.dll).
There are 3 functions into litex::delayload namespace:

Function

Description

Sample code

void set_handler();

Installs own delay-load DLL notification hook.When sqlite3.dll library is needed it tries to locate LiteX Automation library. If LiteX Automation library cannot be found standard search procedure will be used.

using namespace litex;

delayload::set_handler();

HRESULT load_library( bool bAutomation = true );

Loads sqlite3.dll library. If bAutomation is true set_handler() is called before. You may call this routine before any call to SQLite3 engine to make sure that this engine is accessible.
0 means success.
0x8007007e is the most common error code and means that sqlite3.dll library cannot be found.



Warning: Don't call this routine from DllMain function!

using namespace litex;

delayload::load_library(true);

bool free_library();

Unloads previously loaded sqlite3(u).dll library by load_library() function.
You don't need to call this function.

using namespace litex;

delayload::free_library();



These functions are defined only if _LITEX_WITH_DELAYLOAD macro is defined.
Note that using this functions makes only sense if you set sqlite3.dll library as delay-loaded DLL. Do not use these functions if SQLite3 API is linked statically in your project.
By using these functions you can still use header and import library without calling LoadLibrary and GetProcAddress by hand. All you need is to call set_handler() or load_library() function at the beginning of your application (library) before any call to SQLite3 API. Look into LiteX_pp subdirectory to see how this technique works.

Contact: roed@onet.eu