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
}
}

16 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;

Narender Godara said...

Packers and movers in ahmedabad
Packers and movers in gandhidham
Packers and movers in gandhinagar
Packers and movers in jamnagar
Packers and movers in rajkot
Packers and movers in surat
Packers and movers in vadodara
Packers and movers in thane

Shaaban Mohamed said...


شركة تنظيف بالدمام
شركة تنظيف فلل بالدمام
شركة تنظيف شقق بالدمام
شركة تسليك مجاري بالدمام
شركة مكافحة حشرات بالدمام
شركة كشف تسربات المياة بالدمام

شركة مكافحة النمل الابيض بالخبر
شركة مكافحة النمل الابيض بالجبيل
شركة مكافحة النمل الابيض بالقطيف
شركة مكافحة النمل الابيض بالاحساء

شركة كشف تسربات المياة بالخبر
شركة كشف تسربات المياة بالقطيف
شركة كشف تسربات المياة بالجبيل
شركة كشف تسربات المياة بتبوك
شركة كشف تسربات المياه بالباحة

Shaaban Mohamed said...


شركة تنظيف بالخبر
شركة تنظيف بالجبيل
شركة تنظيف بالاحساء
شركة تنظيف بالقطيف
شركة نقل عفش بالقطيف
شركة تنظيف خزانات بالقطيف

شركة تسليك مجاي بالجبيل
شركة تسليك مجاري بالقطيف
شركة تسليك مجاري بالخبر
شركة تسليك مجاري بالباحة

شركة تسليك مجاري بجدة
شركة تسليك مجاري بالباحة
شركة تسليك مجاري ببقيق
شركة تسليك مجاري بالطائف
شركة تسليك مجاري بالخفجي
شركة تسليك مجاري بعنك-سيهات
?شركة تسليك مجاري بالنعيرية

شركة تنظيف بالدمام
شركة مكافحة حشرات بالخبر
شركة مكافحة حشرات بالدمام
شركة مكافحة حشرات بالدمام
افحة حشرات بالجبيل
شركة مكافحة حشراتبالجبيل
شركة مكافحة حشرات بالقطيف
شركة مكافحة حشرات بالاحساء
شركة مكافحة حشرات بالدمام


................................................


شركة رش مبيدات بالدمام
شركة رش مبيدات بالاحساء
شركة رش مبيدات بالقطيف