Storing and retrieving data from sqlite database

Question

I am building an app which contains a form in one view,in which the user fills all the fields and when he clicks the save button the data must be saved in to database and after navigating back,there's another view which, when entered, must show the saved data(event).

I have created a database and have gone through several sqlite3 tutorials;

I have done all other changes to my code according to my requirement. However, when I use this statement to check whether data is inserted in database:

SELECT * FROM reminders;

I am getting nothing and I am confused whether data is inserted or not.

How do I save it properly, and how do I retrieve data from database to use and display it in other view?

Answer 1

First you should create the sqlite3 database file (check this link), then you should include it into your project. Now to connect to it you can use the following code:

#pragma mark -
#pragma mark Create/Load Database
+ (void)createEditableCopyOfDatabaseIfNeeded {
    // First, test for existence.
    NSArray * paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString * documentsDirectory = [paths objectAtIndex:0];
    NSString * writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"DATABASENAME.DB"];

    BOOL success;
    NSFileManager * fileManager = [NSFileManager defaultManager];
    success = [fileManager fileExistsAtPath:writableDBPath];
    if (success) {
        return;
    }

    // The writable database does not exist, so copy the default to the appropriate location.
    NSError * error;
    NSString * defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"DATABASENAME.DB"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }
}
+ (sqlite3 *)getDBConnection {
    [DatabaseController createEditableCopyOfDatabaseIfNeeded];

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

    // Open the database. The database was prepared outside the application.
    sqlite3 * newDBConnection;
    if (sqlite3_open([path UTF8String], &newDBConnection) == SQLITE_OK) {
        //NSLog(@"Database Successfully Opened :)");
    } else {
        //NSLog(@"Error in opening database :(");
    }
    return newDBConnection;
}

then to insert a record you can use this code:

+ (void)insertEvent:(Event *)newEvent {
    sqlite3 * connection = [DatabaseController getDBConnection];
    const char * text = "INSERT INTO Event (Serial, Name, Date) VALUES (?, ?, ?)";
    sqlite3_stmt * insert_statement;
    int prepare_result = sqlite3_prepare_v2(connection, text, -1, &insert_statement, NULL);
    if ((prepare_result != SQLITE_DONE) && (prepare_result != SQLITE_OK)) {
        // Error
        sqlite3_close(connection);
        return;
    }

    sqlite3_bind_int(insert_statement, 1, newEvent.Serial);
    sqlite3_bind_text(insert_statement, 2, [newEvent.Name UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_double(insert_statement, 3, [newEvent.Date timeIntervalSince1970]);

    int statement_result = sqlite3_step(insert_statement);
    if ((statement_result != SQLITE_DONE) && (statement_result != SQLITE_OK)) {
        //Error
        sqlite3_close(connection);
        return;
    }

    sqlite3_finalize(insert_statement);

    // Get the Id of the inserted event
    int rowId = sqlite3_last_insert_rowid(connection);
    newEvent.Id = rowId;

    sqlite3_close(connection);
}

now to get an event:

+ (Event *)getEventById:(int)id {
    Event * result = nil;
    sqlite3 * connection = [DatabaseController getDBConnection];

    const char * text = "SELECT * FROM Event WHERE Id = ?";
    sqlite3_stmt * select_statement;

    int prepare_result = sqlite3_prepare_v2(connection, text, -1, &select_statement, NULL);
    if ((prepare_result != SQLITE_DONE) && (prepare_result != SQLITE_OK)) {
        // error
        sqlite3_close(connection);
        return result;
    }

    sqlite3_bind_int(select_statement, 1, id);

    if (sqlite3_step(select_statement) == SQLITE_ROW) {
        result = [[[Event alloc] init] autorelease];

        result.Id = sqlite3_column_int(select_statement, 0);
        result.Serial = sqlite3_column_int(select_statement, 1);
        result.Name = (((char *) sqlite3_column_text(select_statement, 2)) == NULL)? nil:[NSString stringWithUTF8String:((char *) sqlite3_column_text(select_statement, 2))];
        result.Date = [NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(select_statement, 3)];
    }
    sqlite3_finalize(select_statement);

    sqlite3_close(connection);
    return (result);
}

Answer 2

Here is a blog post that should get you pointed in the right direction, pretty useful to me so sharing it with you.:P

http://dblog.com.au/iphone-development-tutorials/iphone-sdk-tutorial-reading-data-from-a-sqlite-database/

Answer 3

This code used for storing data and retriving data from sqlite data base First you just add sqlite3 frame work after that write bellow code in objective-c

ViewController.h

   #import <UIKit/UIKit.h>
   #import "sqlite3.h"
  @interface ViewController : UIViewController

  @property (weak, nonatomic) IBOutlet UITextField *firstName;
  @property (weak, nonatomic) IBOutlet UITextField *lastName;
  @property (weak, nonatomic) IBOutlet UITextField *state;
 @property (weak, nonatomic) IBOutlet UITextField *mobileNum;

 - (IBAction)saveButton:(id)sender;


- (IBAction)featchButton:(id)sender;
 @property (weak, nonatomic) IBOutlet UILabel *label;

 @property NSString *myDatabase;
 @property sqlite3 *marksDB;

ViewController.m

         #import "ViewController.h"

          @interface ViewController ()

           @end

           @implementation ViewController

      - (void)viewDidLoad {
      [super viewDidLoad];
NSString *docsDir;
NSArray *dirPaths;

dirPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);


docsDir=dirPaths[0];

_myDatabase=[[NSString alloc]initWithString:[docsDir stringByAppendingString:@"marks.db"]];
NSLog(@"My Data base %@",_myDatabase);

NSFileManager *fileMgr=[NSFileManager defaultManager];
if ([fileMgr fileExistsAtPath:_myDatabase]==NO)
{
    const char *dbpath=[_myDatabase UTF8String];

    if (sqlite3_open(dbpath, &_marksDB)==SQLITE_OK)
    {
        char *errMsg;


     const char *sql_stmt="CREATE TABLE IF NOT EXISTS MARKS(ID INTEGER     PRIMARY KEY AUTOINCREMENT ,FIRST NAME TEXT,LAST NAME TEXT,STATE TEXT,MOBILE INTEGER  )";

        if (sqlite3_exec(_marksDB, sql_stmt, NULL, NULL, &errMsg)!=SQLITE_OK)
        {
          _label.text=@"Failed to create Table";
        }
        sqlite3_close(_marksDB);
    }
    else
    {
         _label.text=@"Failed to Create/Open Database";
    }
}

}

        - (IBAction)saveButton:(id)sender {
        sqlite3_stmt *statement;
const char *dbpath=[_myDatabase UTF8String];
if (sqlite3_open(dbpath, &_marksDB)==SQLITE_OK)
{
    NSString *insertSQL=[NSString stringWithFormat:@"INSERT INTO MARKS(firstname,lastname,state,mobile )VALUES(\"%@\",\"%@\",\"%@\",\"%@\")",_firstName.text,_lastName.text,_state.text,_mobileNum.text ];
    const char *insert_stmt=[insertSQL UTF8String];

    sqlite3_prepare_v2(_marksDB, insert_stmt, -1, &statement, NULL);
    if (sqlite3_step(statement)==SQLITE_DONE)
    {
       _label.text=@"Contact Added";
        _firstName.text=@"";
       _lastName.text=@"";
       _state.text=@"";
       _mobileNum.text=@"";

    }
    else
    {
        _label.text=@"Failed to Add Contact";
    }
    sqlite3_finalize(statement);
    sqlite3_close(_marksDB);

}

}

      - (IBAction)featchButton:(id)sender {
         const char *dbpath=[_myDatabase UTF8String];
        sqlite3_stmt *statement;
           if (sqlite3_open(dbpath, &_marksDB)==SQLITE_OK)

{

    NSString *query=[NSString stringWithFormat:@"SELECT firstname,lastname,state,mobile,  FROM MARKS WHERE firstname=\"%@\"",_firstName.text];
    const char *query_stmt=[query UTF8String];
    if (sqlite3_prepare_v2(_marksDB, query_stmt, -1, &statement, NULL)==SQLITE_OK)
    {
        if (sqlite3_step(statement)==SQLITE_ROW)
        {
            NSString *first=[[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 0)];
            _firstName.text=first;

            NSString *lastName=[[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 1)];
           _lastName.text=lastName;

            NSString *state=[[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 2)];
            _state.text=state;
            NSString *mobile=[[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 3)];
            _mobileNum.text=mobile;


           _label.text=@"Match Found";





        }
        else
        {
           _label.text=@"Not Matched";
            _lastName.text=@"";
            _state.text=@"";
            _mobileNum.text=@"";
        }
        sqlite3_finalize(statement);
    }
    sqlite3_close(_marksDB);
}


 }
  @end

Answer 4

you can check whether your data has been saved or not by checking the database table. Go to Users>your computer name>Library>Application support>iphone Simulator>4.3(your version of ios)>Application.. Then look for your Application,go to documents and open the sqlite file. Here you can see the data.

Answer 5

You should use FMDB to reduce the complexity of your code.

It is an Objective-C wrapper around SQLite.

FMDB on github