(Objective C) Save changes in sqlite database

Question

I'm creating an app for my school project that has to write data to my sqlite database. It works, as long as the app is running active but as soon as the app closes, my added data is gone and when I want to read this data this will not work off course. I included both my loadData and saveData methods. The two database paths are the same in both functions so it's not that I'm writing my data elsewhere. I really can't find the solution or the problem. I even get the insert success in my output, so the insert is successful.

- (void) saveData:(id)sender{
    NSString *sqldb = [[NSBundle mainBundle] pathForResource:@"PXLate" ofType:@"sqlite3"];
    sqlite3_stmt *stmt;
    NSString *queryInsert = @"INSERT INTO assignments (name, lesson, dueDate, notification, start, at) VALUES ('abc','abc', 'abc', 1, 'abc', 'abc')";
    NSLog(@"%@",sqldb);

    NSLog(@"%@",queryInsert);
    if(sqlite3_open([sqldb UTF8String], &_PXLate) == SQLITE_OK)
    {
        sqlite3_prepare_v2(_PXLate, [queryInsert UTF8String], -1, &stmt, NULL);

        if(sqlite3_step(stmt)==SQLITE_DONE)
        {
            NSLog(@"insert success");
        }
        else
        {
            NSLog(@"insert un success");
            NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(_PXLate));
        }

        int success=sqlite3_step(stmt);

        if (success == SQLITE_ERROR)
        {
            NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(_PXLate));
            //[_PXLate save:&error];

        }        sqlite3_finalize(stmt);
    }
    sqlite3_close(_PXLate);
}

and my loadData function

- (void) loadData:(id)sender
{
     //path for database
        NSString *sqldb = [[NSBundle mainBundle] pathForResource:@"PXLate" ofType:@"sqlite3"];
    //check if present
    NSFileManager*fm=[NSFileManager defaultManager];
        NSLog(@"path: %@", sqldb);

    const char *dbpath = [sqldb UTF8String];
    sqlite3_stmt    *statement;

    if (sqlite3_open(dbpath, &_PXLate) == SQLITE_OK)
    {
        NSString *querySQL = [NSString stringWithFormat: @"SELECT * FROM assignments WHERE name='abc'", _label.text];
        const char *query_stmt = [querySQL UTF8String];
        NSLog(@"name");
        NSLog(querySQL);
        int response = sqlite3_prepare_v2(_PXLate, query_stmt, -1, &statement, NULL);
        NSLog(@"response %d", response);

        if (response == SQLITE_OK)
        {
            NSLog(@"name");
            if (sqlite3_step(statement) == SQLITE_ROW)
            {
                NSString *namefield = [[NSString alloc]
                                          initWithUTF8String:
                                          (const char *) sqlite3_column_text(
                                                                             statement, 0)];
                NSLog(@"name:%@", namefield);
                _label.text = namefield;
            } else {
                _label.text = @"Match not found";

            }
            sqlite3_finalize(statement);
        }
        sqlite3_close(_PXLate);
    }
}

Answer 1

A couple of observations:

1. As Retterdesdialogs said, you should

   • Check for existence of database in Documents;

   • If not there, copy from bundle to Documents; and

   • Open database from Documents.

   You should not open database from bundle, because on the device that folder is read-only.

2. In your INSERT statement you are not checking the response of sqlite3_prepare_v2, which is a very common source of errors. If this is not SQLITE_OK, you should immediately log sqlite3_errmsg, before you call sqlite3_step.

3. You are performing sqlite3_step twice in the INSERT statement.

4. In loadData, you are not logging sqlite3_errmsg if sqlite3_prepare_v2 failed. Always look at sqlite3_errmsg upon any error.

Answer 2

You have to copy your sqlite to the documents directory and then work with that. Example:

self.databaseName = @"databasename.sqlite";

// Get the path to the documents directory and append the databaseName
NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [documentPaths objectAtIndex:0];

self.databasePath = [[NSString alloc]init];
self.databasePath = [documentsDir stringByAppendingPathComponent:self.databaseName];
[self checkAndCreateDatabase];

And the create method:

-(void)checkAndCreateDatabase 
{
   NSFileManager *fileManager = [NSFileManager defaultManager];
   NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:self.databaseName];
   [fileManager copyItemAtPath:databasePathFromApp toPath:self.databasePath error:nil];
}