Working with Database

Hi all,

Pretty sure that many of you have already worked on this.
An idea to frame things related to DB together.

Lets see how much this works!!!!


Ok Now lets proceed

1) Create Database

- (BOOL)createAppDatabase

{

    NSString *docsDir;

    NSArray *dirPaths;

    BOOL isSuccess = NO;

    

    dirPaths = NSSearchPathForDirectoriesInDomains

                                                 (NSDocumentDirectory, NSUserDomainMask, YES);

    

    docsDir = [dirPaths objectAtIndex:0];

    

    self.databasePath = [[NSString alloc] initWithString: 

           [docsDir stringByAppendingPathComponent: @"MyDatabase.db"]];

    

    NSFileManager *filemgr = [NSFileManager defaultManager];

    

    if ([filemgr fileExistsAtPath: databasePath ] == NO)

    {

              const char *dbpath = [databasePath UTF8String];

        

              if (sqlite3_open(dbpath, &database) == SQLITE_OK)

              {

                             isSuccess = YES;

                             sqlite3_close(database);

             }

             else

                             isSuccess = NO;

    }    

    return isSuccess;

}

2) Create Tables

- (BOOL)createTableWithSqlStatement:(NSString *)sqlStatement

{

    BOOL isSuccess = NO;

    NSFileManager *fileManager = [NSFileManager defaultManager];

    

    if ([fileManager fileExistsAtPath:self.databasePath ]  ==  YES)

    {

        const char *dbpath = [self.databasePath UTF8String];

        if (sqlite3_open(dbpath, &database) == SQLITE_OK)

        {

            char *errMsg;            

            const char *sql_stmt = [sqlStatement UTF8String];

            if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)

            {

                isSuccess = NO;

                NSLog(@"Failed to create table");

            }

            sqlite3_close(database);

            return  isSuccess;

        }

        else

       {

            isSuccess = NO;

            NSLog(@"Failed to open/create database");

        }

    }

    return isSuccess;

}

3) Fetch Data from Table

NSString *sqlStatement = [NSString stringWithFormat:@"SELECT * FROM Info 

                                                                                             where userId=='%d'",@"21"];

- (LoginModel*)fetchDataFromInfoTable:(NSString*)sqlStatement

{

    sqlite3_stmt *sql_smt;

    LoginModel *loginModel;

    

    if (sqlite3_open([self.databasePath UTF8String], &database) == SQLITE_OK)

    {

        if (sqlite3_prepare_v2(database, [sqlStatement UTF8String], -1, &sql_smt, nil) 

                          == SQLITE_OK)

        {

            loginModel = [[LoginModel alloc] init];

            while(sqlite3_step(sql_smt)==SQLITE_ROW)

            {

                const char *s;

                

                s=(char *)sqlite3_column_text(sql_smt, 0); // Here 0 is the 1st column

                if(s==NULL)

                    loginModel.userID=@"";

                else

                    loginModel.userID =[NSString stringWithUTF8String:s];

                

                s=(char *)sqlite3_column_text(sql_smt, 1); // Here 1 is the 2nd column

                if(s==NULL)

                    loginModel.userName=@"";

                else

                    loginModel.userName =[NSString stringWithUTF8String:s];

            }

        }

        sqlite3_finalize(sql_smt);

        sqlite3_close(database);

    }

    else

    {

        loginModel = nil;

        NSLog(@"Failed to open/create database");

    }

    return loginModel;

}

4) Execute Statements

- (BOOL)executeSqlStatement:(NSString*)sqlStatement

{

    sqlite3_stmt *sql_smt;

    BOOL isSuccess = NO;

    

    if (sqlite3_open([self.databasePath UTF8String], &database) == SQLITE_OK)

    {

        if (sqlite3_prepare_v2(database, [sqlStatement UTF8String], -1, &sql_smt, nil) 

                     ==  SQLITE_OK)

        {

            if (SQLITE_DONE!=sqlite3_step(statement))

            {

                sqlite3_close(database);

                isSuccess = NO;

            }

            else {

                NSLog(@"Success");

                isSuccess = YES;

                NSLog(@"%@",sqlStatement);

            }

        }

        sqlite3_finalize(sql_smt);

        sqlite3_close(database);

    }

    else

    {

        isSuccess = NO;

        NSLog(@"Failed to open/create database");

    }    

    return isSuccess;

}

5) Call executeSqlStatement 

    NSString *sqlStatement = [NSString stringWithFormat:@"INSERT INTO Info

          (userId , userName) VALUES (%d,'%@')",[userID intValue],userName];

   [self executeSqlStatement:sqlStatement];

  sqlStatement = [NSString stringWithFormat:@"UPDATE Info 

            Set userName =  '%@' where userID =  '%d' ",@"Swati",@"21"];

    

  [self executeSqlStatement:sqlStatement];

sqlStatement = [NSString stringWithFormat: @"DELETE FROM Info

                                                                                    WHERE userID ='%d'",@"21"];

Hey where is my database ???

Here is the path >> Admin >> Library >> Application Support >> iPhone Simulator >> Your chosen Version >> Applications >> Here you will see a list of all apps on Simulator >> choose yours >> Documents >> MyDatabase.db

How to view the contents ???

You can install a plugin in /firefox named Sqlite Manager, click and provide the location and here is the DB view.....