Create a Table and Insert data to the Table usig Sqlite3 - iPhone

 

    //According to the 'query' create the database  

- (BOOL)executeCreateTableQuery:(NSString*)query

{

    BOOL success = NO;

    MCDatabase *mcDB = [MCDatabase getSharedInstance];  // MCDatabase class has My previous post 

   

    @try {

        char *errorMsg;

        int result = sqlite3_exec([mcDB getOpenedDatabase], [query UTF8String], NULL, NULL, &errorMsg);

        [query release];

        if (result == SQLITE_OK) {

            success = YES;

        }else {

            NSLog(@"eCommerce MCDatabaseConfig-executeQuery: execute failed");

            NSLog(@"%@",query);

        }

    }

    @catch (NSException * e) {

        NSLog(@"eCommerce MCDatabaseConfig-executeQuery: Caught %@: %@", [e name], [e reason]);

    }

    @finally {

        if ([mcDB isDatabaseOpened]) {

            [mcDB closeDatabase];

        }

    }

    return success;

}

 

- (BOOL)createTable

{

    BOOL added = NO;

    BOOL createTable = [self executeTableQuery:@"CREATE TABLE IF NOT EXISTS cache_flags (id INTEGER PRIMARY KEY AUTOINCREMENT,slides INTEGER,speaker_lists STRING)"];

    if (createTable) {

        NSLog(@"-------------------------Databse cache_flags CREATE");

        added = YES;

        NXDatabase *mcDB = [NXDatabase getSharedInstance];   // MCDatabase class has My previous post

       

        @try {

            char *sql = "INSERT OR REPLACE INTO cache_flags(id,slides,speaker_lists) VALUES(?,?,?);";

            sqlite3_stmt *stmt;

            if (sqlite3_prepare_v2([mcDB getOpenedDatabase], sql, -1, &stmt, nil) == SQLITE_OK) {

                sqlite3_bind_int(stmt, 1, 1);

                sqlite3_bind_text(stmt, 3, [(NSString *)[speakersDic valueForKey:@"FIRSTNAME"] UTF8String], -1, NULL);

               

            }

            if (sqlite3_step(stmt) != SQLITE_DONE) {

                NSLog(@"-executeInsertQuery-addNewInvoice: ading new Category failed");

            }else {

                added = YES;

            }

            sqlite3_finalize(stmt);

        }

        @catch (NSException * e) {

            NSLog(@"-executeInsertQuery-addNewInvoice: Caught %@: %@", [e name], [e reason]);

        }

        @finally {

            if ([mcDB isDatabaseOpened]) {

                [mcDB closeDatabase];

                    //[mcDB release];

            }

        }

     

        return added;

    }

    else {

        NSLog(@"-------------------------Database cache_flags Not CREATE");

        return added;

    }

        //[databaseConfig release];

}

Database with iPhone

     1.  First we have to configure database in iPhone

MCDatabase.h File

#import "Foundation/Foundation.h"

#import "/usr/include/sqlite3.h"

#define kDATABASE_NAME @"eCommerce.sqlite3"

@interface MCDatabase : NSObject

{

   

}

+(MCDatabase *)getSharedInstance;

- (sqlite3 *)getOpenedDatabase;

- (BOOL)closeDatabase;

- (BOOL)isDatabaseOpened;

@end

MCDatabase.m File


 

#import "MCDatabase.h"

static sqlite3        *database;

static BOOL            opened;

static MCDatabase    *sharedDatabase = nil;

@implementation MCDatabase

    //Create shared object for the 'Database'

+ (MCDatabase *)getSharedInstance

{

    @synchronized(self) {

        if (sharedDatabase == nil)

            sharedDatabase = [[MCDatabase alloc] init];

    }

    return sharedDatabase;   

}

- (sqlite3 *)getOpenedDatabase

{

    @try {

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

        NSString *documentsDirectory = [paths objectAtIndex:0];

        NSString *databasePath = [documentsDirectory stringByAppendingPathComponent:kDATABASE_NAME];

       

        int result = sqlite3_open([databasePath UTF8String], &database);

        if (result == SQLITE_OK) {

            opened = YES;

        }else {

            sqlite3_close(database);

        }   

    }

    @catch (NSException * e) {

        NSLog(@"eCommerce MCDatabaseConfig-getDatabase: Caught %@: %@", [e name], [e reason]);

    }

   

    return database;

}

- (BOOL)closeDatabase

{

    @try {

        sqlite3_close(database);

        opened = NO;

    }

    @catch (NSException * e) {

        NSLog(@"eCommerce MCDatabaseConfig-closeDatabase: Caught %@: %@", [e name], [e reason]);

    }

   

    return !opened;

}

- (BOOL)isDatabaseOpened

{

    return opened;

}

- (void)dealloc

{

    free(database);

    [super dealloc];

}

@end

 

Working with Resources Directory in XCode and Document Directory in iPhone

1. Copy File from Resources folder to Document Directory

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

    NSString *documentsDirectory = [paths objectAtIndex:0];

        // Copy eCommerce.sqlite3 from resources to the Documents folder

    NSString *storePath = [documentsDirectory stringByAppendingPathComponent: @"eCommerce.sqlite3"];

    NSString *filePath = [[NSBundle mainBundle] pathForResource:@"eCommerce" ofType:@"sqlite3"];

    if (filePath) {

        NSData *myData = [NSData dataWithContentsOfFile:filePath];

        if (![myData writeToFile:storePath atomically:YES]) {

            [self handleError:@"Critical file creation on the disk failed unexpectedly! Please leave the application as this is unrecoverable.!"];

        }

    }else {

        [self handleError:@"there is no database in Resources Folder!"];

    }

  

      2.  Check whether Existing file is there or not in Document directory

    NSString *getDatabasePath = [documentsDirectory stringByAppendingString:@"/eCommerce.sqlite3"];

   

    NSFileManager* fileManager = [NSFileManager defaultManager];

    if(![fileManager fileExistsAtPath:getDatabasePath])  {

        NSLog(@"file doesn't exist");

            // file doesn't exist

    } else {

         NSLog(@"file exist");

    }

      3. Create a Folder in side Document directory

- (void)createFolder:(NSString*)folderName

{

    NSString *storePath = [self.documentsDirectory stringByAppendingPathComponent:folderName];

    NSFileManager *NSFm= [NSFileManager defaultManager];

//        if(![NSFm createDirectoryAtPath:storePath attributes:nil])

//            [self handleError:@"Critical file creation on the disk failed unexpectedly! Please leave the application as this is unrecoverable.!"];

    if(![NSFm createDirectoryAtPath:storePath withIntermediateDirectories:YES attributes:nil error:NULL])

        [self handleError:@"Critical file creation on the disk failed unexpectedly! Please leave the application as this is unrecoverable.!"];

   

}

 

      4. Delete a file in side Document directory

    NSFileManager *fm = [NSFileManager defaultManager];     // delete zip file

            [fm removeItemAtPath:storePath error:nil];

            [fm removeItemAtPath:[documentsDirectory stringByAppendingPathComponent: @"__MACOSX"] error:nil];