Article Index
SQLite Tutorial
Logging All Inserts, Updates, and Deletes
UTC and Localtime
The ATTACH Command
The Power of the Sign Function
Creating a Permanent Sign Function
Spreadsheet Format to Normalized Data
C and C API
A C Program -- Building a Class to Do the Work
Defining SQLite User Functions
Aggregate Functions
Reading Images (Blob data)

A C++ Program -- Building a Class to Do the Work

It is possible to build a class, SQLITE3 (defined below), which reads the returned data into a vector. Note that instead of using the sqlite3_exec() function, sqlite3_get_table() is used instead. It copies the result of the SQL statement into the variable array of string result. Note this variable must be freed with sqlite3_free_table() after it has been used to copy the returned SQL headings and data into the vectors vcol_head and vdata. Note that the first row is the heading.

class SQLITE3 {
private:
sqlite3 *db;
char *zErrMsg;
char **result;
int rc;
int nrow,ncol;
int db_open;

public:

std::vector vcol_head;
std::vector vdata;

SQLITE3 (std::string tablename="init.db"): zErrMsg(0), rc(0),db_open(0) {
rc = sqlite3_open(tablename.c_str(), &db);
if( rc ){
fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
sqlite3_close(db);
}
db_open=1;
}

int exe(std::string s_exe) {
rc = sqlite3_get_table(
db, /* An open database */
s_exe.c_str(), /* SQL to be executed */
&result, /* Result written to a char *[] that this points to */
&nrow, /* Number of result rows written here */
&ncol, /* Number of result columns written here */
&zErrMsg /* Error msg written here */
);

if(vcol_head.size()<0) { vcol_head.clear(); }
if(vdata.size()<0) { vdata.clear(); }


if( rc == SQLITE_OK ){
for(int i=0; i < ncol; ++i)
vcol_head.push_back(result[i]); /* First row heading */
for(int i=0; i < ncol*nrow; ++i)
vdata.push_back(result[ncol+i]);
}
sqlite3_free_table(result);
return rc;
}

~SQLITE3(){
sqlite3_close(db);
}
};

The complete program can be found in this example or viewed here: simplesqlite3cpp2.cc.

With the class defined above, it can be used in main or in a function as follows:

std::string s;
SQLITE3 sql("database.db");


sql.exe("create table notes (t text)");
s="insert into notes (t) values ('sample data')";
sql.exe(s);

s="select t from notes";
sql.exe(s);

The following, still assuming this code will be entered in main or a function, is an example of printing the data from a select. Note the headings section and the data sections.

if( sql.vcol_head.size() > 0 )
{
std::cout << "Headings" << std::endl;
copy(sql.vcol_head.begin(),
sql.vcol_head.end(),
std::ostream_iterator(std::cout,"t"));

std::cout << std::endl << std::endl;
std::cout << "Data" << std::endl;
copy(sql.vdata.begin(),
sql.vdata.end(),
std::ostream_iterator(std::cout,"t"));

std::cout << std::endl;
}