[Objective-C] Use database with sql (lite)

Here a simple tutorial to use SQLite in Objective-C to make complex iphone|ipad applications that uses a database!


Now, create a new project and import SQLite library. Generally it is located here:

/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS.X.X.X.sdk/usr/lib/libsqlite3.dylib

In your header file, you need t add the import and create sqlite3 class in your interface:

 I show only three method that uses the db, SELECT, DELETE, INSERT.

READ DATA (like SELECT * FROM XXX) : ----->>

- (NSMutableArray*) readDataFromDatabase  {
   dataArray  = [[NSMutableArray alloc] init];
   dataStored = [[NSMutableDictionary alloc] init];
 
   NSString *name;
   NSString *address;

   NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);  
   NSString *documentsDirectory = [paths objectAtIndex:0];
   NSString *path = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sqlite"];

   if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
        const char *sql = "SELECT * FROM tabella";
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
             while (sqlite3_step(statement) == SQLITE_ROW) {
                      name =   [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
                      address = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
   
                       [dataStored setObject:name forKey:@"name"];
                       [dataStored setObject:address forKey:@"address"];
   
                        [dataArray addObject:[dataStored copy]];
              }
         }
         sqlite3_finalize(statement);
    }
   sqlite3_close(database);
   [dataStored release];

   return dataArray;
}

In this method, that return a NSMutableArray, I used a NSMutableDictionary (dataStored) and a NSMutableArray (dataArray).
Now we have a dataArray with lists of the select query!
DELETE DATA FROM xxx :

- (void) deleteDataFromDatabase:(id) data
{  
   NSMutableDictionary *d = data;

   NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
   NSString *documentsDirectory = [paths objectAtIndex:0];
   NSString *path = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sqlite"];

   sqlite3_stmt *delete_statment = nil;
   if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
         if (delete_statment == nil) {
              const char *sql = "DELETE FROM tabella WHERE name=? AND address=?";
              if (sqlite3_prepare_v2(database, sql, -1, &delete_statment, NULL) != SQLITE_OK) {
                  NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
              }
         }
         sqlite3_bind_text(delete_statment, 2, [[d objectForKey:@"name"] UTF8String], -1,  SQLITE_TRANSIENT);
         sqlite3_bind_text(delete_statment, 3, [[d objectForKey:@"address"] UTF8String], -1,  SQLITE_TRANSIENT);

         int success = sqlite3_step(delete_statment);
 
         if (success != SQLITE_DONE) {
              NSAssert1(0, @"Error: failed to save priority with message '%s'.", sqlite3_errmsg(database));
         } else {
              sqlite3_reset(delete_statment);
         }
    }
    sqlite3_close(database);
}

Here, you can delete a value from db. (delete from tabella where x=0 and y=0).
It accept ad method parameter an object, that is necessary a NSMutableDictionary (first row).

INSERT DATA :

- (void) insertDataInDatabase:(NSString*)name address:(NSString*)address
{
   sqlite3_stmt *insert_statement = nil;
   NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
   NSString *documentsDirectory = [paths objectAtIndex:0];
   NSString *path = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sqlite"];

   if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
        if (insert_statement == nil) {
           static char *sql = "INSERT INTO tabella (name,address) VALUES(?,?)";
           if (sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL) != SQLITE_OK) {
                NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
           }

           sqlite3_bind_text(insert_statement, 2, [name UTF8String], -1,  SQLITE_TRANSIENT );
           sqlite3_bind_text(insert_statement, 3, [address UTF8String], -1, SQLITE_TRANSIENT );
        }
        int success = sqlite3_step(insert_statement);
 
        sqlite3_reset(insert_statement);
        if (success != SQLITE_ERROR) {
            NSLog(@"error");
        }
   }
   sqlite3_finalize(insert_statement);
   sqlite3_close(database);
}

In this method you can add values to the table.

First of all operations, you need call createEditableCopyOfDatabaseIfNeeded that locate (and copy to exec path) the db:
 

- (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:@"MyDB.sqlite"];
     success = [fileManager fileExistsAtPath:writableDBPath];
     if (success) return;
     NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"MyDB.sqlite"];
     success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
     if (!success) {
          NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
     }
}

It copy current .sqlite file in your device.