Thursday, May 14, 2009

SQLite C++ wrapper, so you don't have to

SQLite is great. It is small, fast and easy to set up. However, if you are writing software that needs it, you're in for a bit of learning. You will need to learn what API calls to make and when to make them, usually by going over example code and using trial and error. I am introducing an open source SQLite C++ wrapper that will allow you to use the database without having to learn the SQLite API. It may save you several days of work.

The wrapper is just two C++ files, has a MIT style license and is platform independent. You will still need to link in SQLite 3.0 and learn the quirky SQLite SQL syntax.

You can download it here.

Creating a database and table
#include "sdsqlite.h"

void create_db(void)
{
sd::sqlite database("mydb.db");
database << "create table if not exists work (first_name text, last_name text, hours real)";
}


Database insertion
#include "sdsqlite.h"

struct work_data { char* first; char* last; float hours; };

work_data wdata[] = {
{"Joe", "Smith", 2.5},
...
};

void insert_rows(void)
{
try
{
sd::sqlite database("mydb.db"); // open the db with the table already created

sd::sql insert_query(database); // build an sql query
insert_query << "insert into work (first_name, last_name, hours) VALUES(?, ?, ?)";

database << "begin transaction";// create a transaction for speed

// insert data (sdsqlite will auto-detect data type and execure query)
for(int i=0;i<sizeof(wdata)/sizeof(work_data);++i)
insert_query << wdata[i].first << wdata[i].last << wdata[i].hours;

database << "commit transaction";// complete transaction
}
catch(sd::db_error& err)
{
// do something with error
}
}


Database extraction
#include "sdsqlite.h"

void extract_name(const std::string& name)
{
try
{
sd::sqlite database("mydb.db"); // open the db with the table already created

// select all names that begin with the contents of the "name" variable
sd::sql selquery(database);
selquery << "select first_name, last_name, hours from work where first_name like ?" << name+"%";

// extract the matching rows
float hours;
std::string first, last;
while(selquery.step())
{
selquery >> first >> last >> hours;

// do something with the data
}
}
catch(sd::db_error& err)
{
// do something with error
}
}

13 comments:

kishore said...

how can i add a column in the table if the column doesn't exists using your wrapper?

Matt Giger said...

The wrapper doesn't contain any fields by itself , those statements you see are just examples. You can specify any sql statement you like, with whatever fields your table has, to extract and/or insert data into your table.

kishore said...

ya..i know that those are examples.But I'm using it my application,in which I have to add a column in existing table making sure that it doesn't exists before.Can you tell me,how can I do this?

kishore said...

can you write me that as those examples?

Matt Giger said...

You would probably want to use the standard "alter table" command for SQLite which is described here. A simple example would be "alter table mytable add column newcolumn integer".

Anonymous said...

thank , i will try it

ดูทีวี

วิทยุ

Jay Godse said...

The SQLite API is quite straightforward to begin with. It is not much more complicated than learning fopen/fclose/fread/fwrite in Posix.

If C++ is not your thing, there are lots of bindings to other languages. Check out my site at http://www.squidoo.com/sqliteprogramming for more details.

Wade said...

Great, it's just what I want!
but the insert data in the example doesn't work for me, I'm using gcc version 4.0.1 (Apple Inc. build 5493) under MacOS. I checked the code but could not find when did you do the sql.step(), the only place is sql << end, but I don't know how to do it, and could not find the code in your example.

so I changed the test code to :
sd::sql insert_query(database); // build an sql query
insert_query << "insert into work (first_name, last_name, hours) VALUES(?, ?, ?)";

database << "begin transaction";// create a transaction for speed

// insert data (sdsqlite will auto-detect data type and execure query)
insert_query << "Joe" << "Smith" << 1 ;
if(!insert_query.step()){
cout << "error insert 1" << endl;
};

insert_query << "insert into work (first_name, last_name, hours) VALUES(?, ?, ?)" << "tom" << "pet" << 2.5 ;
if(!insert_query.step()){
cout << "error insert 2" << endl;
};

after that, it works, is that a problem? thanks for the great wrapper, it will save me a lot of time.

gg said...

thanks Matt, I've been kludging my own wrapper to date but this is much cleaner and more complete.

gg said...

Matt,

Works great so far but I think u mixed up reset and finalize so binding of new variables for the insert example don't work.

Add a finalize function and rework reset as follows:

/// Delete a statement.
void sql::finalize()
{
if(stmt_)
sqlite3_finalize(stmt_);
stmt_ = 0;
ipos_ = opos_ = icol_ = ocol_ = 0;
}

/// Reset the statement to allow new bindings to existing statement or rewind DB.
void sql::reset(void)
{
if (sqlite3_reset(stmt_) != SQLITE_OK)
throw new sd::db_error("Failed to reset statement");
}

Again great wrapper otherwise.

Anonymous said...

thank , i will try it
ทีวีออนไลน์

xiao said...

Romantic pink is a tender pretty girl can best embody the links of london color; the stars also began to become followers of links london pink. The summer of passion and energy links of london bracelet and romanticpink, aroused a sweet and romantic agitation. See below nine groups sweetie bracelet pink star.

Puchino said...

last_rowid(void) doesnt work :(,

trying as SQL statement
int lastRowId;
sd::sdsql sql_select(handleDB);

sql_select << SELECT last_insert_rowid();

while(sql_select.step()){
sql_select >> lastRowId;
}

But same problem. lastRowId is still 0;