Using SQLite in an iPhone App

This entry describes using SQLite in an iPhone app for displaying , updating & deleting data in the database

1.First of all create a database using SQLite Database browser

Add the database to your project.Also add libsqlite3.0.dylib to your frameworks

2.  In the AppDelegate class add the below code,

- (void)applicationDidFinishLaunching:(UIApplication *)application { 

[self createEditableCopyOfDatabaseIfNeeded];

[self initializeDatabase];

}

-(void) createEditableCopyOfDatabaseIfNeeded{

BOOL success;

NSFileManager *fileManager=[NSFileManager defaultManager];

NSError *error;

NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

NSString *documentsDirectory= [paths objectAtIndex:0];

NSString *writableDBPath= [documentsDirectory stringByAppendingPathComponent:@"testt.sqlite"];

success= [fileManager fileExistsAtPath:writableDBPath ];

if(success) return;

NSString *defaultDBPath=[[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"lovetarot.sqlite"];

success=[fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error: &error];

}

-(void) initializeDatabase{

NSMutableArray *todoArray=[[NSMutableArray alloc] init];

self.todos=todoArray;

[todoArray release];

NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

NSString *documentsDirectory= [paths objectAtIndex:0];

NSString *path=[documentsDirectory stringByAppendingPathComponent:@"testt.sqlite"];

if(sqlite3_open ([path UTF8String], &database ) ==SQLITE_OK){

const char *sql="SELECT primaryKey FROM journals";

sqlite3_stmt *statement;

if(sqlite3_prepare_v2(database, sql, -1, &statement ,NULL) ==SQLITE_OK){

while(sqlite3_step(statement) == SQLITE_ROW ){

int primaryKey=sqlite3_column_int(statement,0);

//double date=sqlite3_column_int(statement,1);

GoddessInspiration *td=[[GoddessInspiration alloc] initWithPrimaryKey:primaryKey database:database];

[todos addObject:td];

[td release];

}

}

sqlite3_finalize(statement);

}else{

sqlite3_close(database);

}

}

3. Now , create NSObject class, lets name it test

4. In test.h add the below code,

#import <Foundation/Foundation.h>

#import <sqlite3.h>

@interface test : NSObject {

sqlite3 *database;

NSInteger primaryKey;

NSString *text;

NSString  *desc;

NSString *imagePath, *title;

double journalDate;

NSInteger priority;

NSInteger status;

BOOL dirty;

}

@property(assign,nonatomic,readonly) NSInteger primaryKey;

@property (nonatomic,retain) NSString *text;

@property (nonatomic,assign) double journalDate;

@property (nonatomic,retain) NSString *desc;

@property (nonatomic,retain) NSString *imagePath;

@property (nonatomic,retain) NSString *title;

@property (nonatomic)NSInteger priority;

@property (nonatomic)NSInteger status;

-(id)initWithPrimaryKey:(NSInteger )pk database:(sqlite3 *)db;

@end

5. In the test.m add the below code

#import "test.h"

static sqlite3_stmt *init_statement=nil;

@implementation test

@synthesize primaryKey,text,priority,status, desc,imagePath,title,journalDate;

-(id)initWithPrimaryKey:(NSInteger )pk database:(sqlite3 *)db{

  if(self = [super init] ) {

primaryKey=pk;

database =db;

if(init_statement ==nil){

const char *sql=" SELECT date,description,title,imagePath FROM journals WHERE primaryKey=? ";

if (sqlite3_prepare_v2(database, sql, -1, &init_statement, NULL) != SQLITE_OK){

NSAssert1(0,@"Error: Failed to prepare statement with message '%s'.",sqlite3_errmsg(database));

}

}

sqlite3_bind_int(init_statement,1,primaryKey);

if(sqlite3_step(init_statement) == SQLITE_ROW) {

//double journalDate;

self.journalDate=sqlite3_column_double(init_statement,0);

if(sqlite3_column_text(init_statement,1) != NULL)

self.desc=[ NSString stringWithUTF8String:(char *) sqlite3_column_text(init_statement,1)];

if(sqlite3_column_text(init_statement,3) != NULL)

self.imagePath=[NSString stringWithUTF8String:(char *) sqlite3_column_text(init_statement,3)];

if(sqlite3_column_text(init_statement,2) != NULL)

self.title=[NSString stringWithUTF8String:(char *) sqlite3_column_text(init_statement,2)];

}

else{

self.text=@"Nothing";

}

sqlite3_reset(init_statement);

}

return self;

}

@end

    Now displaying the data stored in the database

    

    test *temptest = (test *)[appDelegate.todos objectAtIndex:someindex];

NSInteger temp=temptest.primaryKey;

    temp will contain the value of primary key column, similarly you can get for other columns

  Deleting an entry from the database

1. In the app delegate add the below code,

-(void)removeEntry:(test *)tarot{

NSUInteger index=[todos indexOfObject:tarot];

if(index == NSNotFound) return;

[tarot deleteFromDatabase:database];

[todos removeObject:tarot];

}

2. In the test.m add the below code

bore @ implementation

static sqlite3_stmt *delete_statement=nil;

after @implementation

-(void)deleteFromDatabase:(sqlite3 *)database1{

if(delete_statement == nil){

const char *sql="DELETE FROM journals WHERE primaryKey=?";

if (sqlite3_prepare_v2(database1, sql, -1, &delete_statement, NULL) != SQLITE_OK){

NSAssert1(0,@"Error: Failed to prepare statement with message '%s'.",sqlite3_errmsg(database1));

}

}

sqlite3_bind_int(delete_statement,1, self.primaryKey);

int success = sqlite3_step(delete_statement);

if(success != SQLITE_DONE){

NSAssert1(0,@"Error: failed to save priority with message '%s'.",sqlite3_errmsg(database1));

}

sqlite3_reset(delete_statement);

}

3.Wherever you want to delete, add the below code

   test *tarot=[appDelegate.todos objectAtIndex:someindex];

   [appDelegate removeEntry:tarot];

 Inserting data into the database

1. In the app delegate file add the below code

-(void)addEntry:(test *)tarot {

[tarot insertNewTodoIntoDatabase:database];

[todos addObject:tarot];

}

2.In the test.m add the below code

before @ implementation

static sqlite3_stmt *insert_statement=nil;

after @implementation

-(NSInteger)insertNewTodoIntoDatabase:(sqlite3 *)database1{

database=database1;

if(insert_statement ==nil){

//NSString temp=td.textField.text

static char *sql="INSERT INTO journals (date,description,title,imagePath) VALUES (?,?,?,?)";

//static char *sql="INSERT INTO todo (text) VALUES (td.textFld.text)";

        if (sqlite3_prepare_v2(database1, sql, -1, &insert_statement, NULL) != SQLITE_OK){

NSAssert1(0,@"Error: Failed to prepare statement with message '%s'.",sqlite3_errmsg(database1));

}

}

sqlite3_bind_double(insert_statement,1,journalDate);

sqlite3_bind_text(insert_statement,2,[self.desc UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_bind_text(insert_statement,3,[self.title UTF8String], -1, SQLITE_TRANSIENT); 

sqlite3_bind_text(insert_statement,4,[self.imagePath UTF8String], -1, SQLITE_TRANSIENT);

int success=sqlite3_step(insert_statement);

sqlite3_reset(insert_statement);

if(success != SQLITE_ERROR){

primaryKey = sqlite3_last_insert_rowid(database1);

return primaryKey;

}

NSAssert1(0,@"Error: failed to insert into the database with message '%s'.",sqlite3_errmsg(database1));

return -1;

}

3 Now, wherever you want to add the data into database add the below code

 test *test1 = [[test alloc] init];

[test1 setJournalDate:milliSecs];

[test1 setTitle:self.titleTextField.text];

[test1 setDesc:self.descrpnText.text];

[test1 setImagePath:@"NO"];

[appDelegate addEntry:test1];

 Updating data into the database

1.In the app delegate class, add the below code

-(void)updateEntry:(test *)tarot {

NSUInteger index=[todos indexOfObject:tarot];

if(index == NSNotFound) return;

[tarot updateIntoDatabase:database];

}

2. In the test.m add the below code

before @ implementation

static sqlite3_stmt *update_statement=nil;

after @implementation

-(NSInteger)updateIntoDatabase:(sqlite3 *)database1{

database=database1;

if(update_statement == nil){

const char *sql="UPDATE journals SET date=?, description=?, title=?, imagePath=? WHERE primaryKey=?";

if (sqlite3_prepare_v2(database1, sql, -1, &update_statement, NULL) != SQLITE_OK){

NSAssert1(0,@"Error: Failed to prepare statement with message '%s'.",sqlite3_errmsg(database1));

}

}

sqlite3_bind_int(update_statement,5, self.primaryKey);

sqlite3_bind_double(update_statement,1,journalDate);

sqlite3_bind_text(update_statement,2,[self.desc UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_bind_text(update_statement,3,[self.title UTF8String], -1, SQLITE_TRANSIENT); 

sqlite3_bind_text(update_statement,4,[self.imagePath UTF8String], -1, SQLITE_TRANSIENT); 

int success = sqlite3_step(update_statement);

if(success != SQLITE_DONE){

NSAssert1(0,@"Error: failed to save with message '%s'.",sqlite3_errmsg(database1));

}

sqlite3_reset(update_statement);

return self.primaryKey;

}

3. Now after editing the entry , you need to save it in the database for that add the below code

 test *test1=[appDelegate.todos objectAtIndex:(someindex)];

[test1 setJournalDate:milliSecs];

[test1 setTitle:self.titleTextField.text];

[test1 setDesc:self.descrpnText.text];

[test1 setImagePath:imageValue];

[appDelegate updateTarot:test1];