Home > Iphone, Objective-C, Programming > Accessing SQLite3 database in Objective-C

Accessing SQLite3 database in Objective-C

Today I will show you how to access sqlite3 database with objective-C. I use XCode 4.4.1. First thing todo is to add sqlite3 library (libsqlite3.dylib) to  framework directory in your project.

Then write the interface file ViewController.h:

#import <UIKit/UIKit.h>
#import <sqlite3.h>

@interface ViewController : UIViewController
{
  sqlite3 *db;
}

-(NSString *) filePath;
-(void) openDB;
-(void) createTableNamed:(NSString *) tableName
withField1:(NSString *) field1
withField2:(NSString *) field2;

-(void) insertRecordIntoTableNamed:(NSString *) tableName
withField1:(NSString *) field1
field1Value:(NSString *) field1Value
withField2:(NSString *) field2
field2Value:(NSString *) field2Value;

-(void) getAllRowsFromTableNamed:(NSString *)tableName;
@end

and then implement methods in ViewController.m:
1. filePath: this method returns the full path of the database that will be created in the Documents folder within apllication’s sandbox

-(NSString *) filePath
{

    NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
                                                           NSUserDomainMask,YES);
    NSString *documentDir = [path objectAtIndex:0];
    return [documentDir stringByAppendingPathComponent:@"database.sql"];

}

2. openDB: this method will open the database or create database if database is not exists

-(void) openDB
{
    if(sqlite3_open([[self filePath] UTF8String], &(db)) != SQLITE_OK)
    {
        sqlite3_close(db);
        NSAssert(0, @"Database failed to open");
    }
}

3. createTableNamed: this method will create table if table is not exists

-(void) createTableNamed:(NSString *) tableName
              withField1:(NSString *) field1
              withField2:(NSString *) field2
{
    char *err;
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('%@' TEXT PRIMARY KEY,'%@' TEXT);", tableName, field1, field2 ];

    if(sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK )
    {
        sqlite3_close(db);
        NSAssert(0, @"Table failed to create");
    }

}

4. insertRecordIntoTableNamed: this method will insert or replace records

-(void) insertRecordIntoTableNamed:(NSString *)tableName withField1:(NSString *)field1 field1Value:(NSString *)field1Value withField2:(NSString *)field2 field2Value:(NSString *)field2Value
{
    char *err;
    NSString *sql = [NSString stringWithFormat:@"INSERT OR REPLACE INTO '%@' ('%@','%@') VALUES ('%@','%@') ; ", tableName, field1, field2, field1Value, field2Value ];
    if(sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK  )
    {   
        sqlite3_close(db);
        NSAssert(0, @"Error Inserting/Updating Table");
    }
}

5. getAllRowsFromTableNamed: this method will retrieves records

-(void) getAllRowsFromTableNamed:(NSString *)tableName
{
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@", tableName];
    sqlite3_stmt * statement;

    if(sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        while(sqlite3_step(statement) == SQLITE_ROW)
        {
            char *field1 = (char *) sqlite3_column_text(statement, 0);
            NSString *field1Str = [[NSString alloc] initWithUTF8String:field1];
            char *field2 = (char *) sqlite3_column_text(statement, 1);
            NSString *field2Str = [[NSString alloc] initWithUTF8String:field2];

            NSString *str = [[NSString alloc] initWithFormat:@"%@ - %@", field1Str, field2Str];

            NSLog(@"%@",str);
        }
    }
}

To test it, insert some code in viedDidLoad method:

-(void)viewDidLoad
{
    [self openDB];
    [self createTableNamed:@"Contacts"
                withField1:@"name"
                withField2:@"email"];

    for(int i=0; i<5; i++)
    {
        NSString *name = [[NSString alloc] initWithFormat:@"User%d", i];
        NSString *email = [[NSString alloc] initWithFormat:@"user%d@yahoo.com", i];

        [self insertRecordIntoTableNamed:@"Contacts"
                              withField1:@"name"
                             field1Value:name
                              withField2:@"email"
                             field2Value:email];
    }

    [self getAllRowsFromTableNamed:@"Contacts"];
    [super viewDidLoad];
}

The Output :

2012-11-19 19:59:15.549 Databases[4388:c07] User0 - user0@yahoo.com
2012-11-19 19:59:15.552 Databases[4388:c07] User1 - user1@yahoo.com
2012-11-19 19:59:15.552 Databases[4388:c07] User2 - user2@yahoo.com
2012-11-19 19:59:15.553 Databases[4388:c07] User3 - user3@yahoo.com
2012-11-19 19:59:15.553 Databases[4388:c07] User4 - user4@yahoo.com
Advertisements
  1. November 22, 2012 at 9:15 am

    Fantastic items from you, man. I’ve be mindful your stuff prior to and you’re just too fantastic. I actually like what you have acquired here, certainly like what you’re saying and the best way during which you are saying it. You’re making it enjoyable and you continue to take care of to stay it sensible. I can not wait to learn much more from you. That is actually a wonderful website.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: