Aug 13, 2008

(iPhone) How to use SQLite

1. Add libsqlite3.dylib framwork in your project.

2. Add #import in your interface file.

3. You should create a .sql file, there are many ways to do it: you can create with you console by taping sqlite3 file.sql or you can create by coding in your project. In my case, i create the database dynamically, so i create the database file in my code, and i will post an example below.

4. Declare 2 variables in your interface file:
sqlite3 *database;
NSString *dbPath;
In you implementation file:
- (void) createTable {
dbPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"db.sql"];
if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
NSString *createSql = @"CREATE TABLE test (text varchar(255))";
if (sqlite3_exec(database, [createSql cStringUsingEncoding:NSUTF8StringEncoding], NULL, NULL, NULL) == SQLITE_OK) {
NSLog(@"create table");
NSString *insertSql = @"INSERT INTO test (text) VALUES('fff')";
int testvalue = sqlite3_exec(database, [insertSql cStringUsingEncoding:NSUTF8StringEncoding], NULL, NULL, NULL);
if (testvalue == SQLITE_OK) {
NSLog(@"insert query ok");
else {
NSLog(@"error code %i", testvalue);

and this method will show you how to use the select query:
- (void) findRowNb {
NSString *selectSql = @"SELECT COUNT(*) FROM test";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, [selectSql cStringUsingEncoding:NSUTF8StringEncoding], -1, &statement, NULL) == SQLITE_OK) {
while (sqlite3_step(statement) == SQLITE_ROW) {
int count = sqlite3_column_int(statement, 0);
NSLog(@"row nb %i", count);

No comments: