(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);
    }
}

possible duplicate of Sqlite inserts get lost after app restart — peko
– peko, Commented Nov 13, 2013 at 12:30
All files in the bundle ([NSBundle mainBundle]) are readonly. You should copy the file to the document directory before attempting to change it. — rckoenes
– rckoenes, Commented Nov 13, 2013 at 12:56

Retterdesdialogs · Accepted Answer · 2013-11-13 12:56:41Z

1

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];
}

answered Nov 13, 2013 at 12:56

Retterdesdialogs

3,2101 gold badge24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rob Over a year ago

I assume you meant to have checkAndCreateDatabase check for the existence of the file in the databasePath and only copy if it doesn't already exist there.

Retterdesdialogs Over a year ago

Correct. I think copyItemAtPath: only inserts if file not exists. Correct me if i'm wrong.

Rob Over a year ago

Agreed, but I'd personally prefer to check for the existence of the file in Documents first, and only try to copy if it's not there. That way, you can more easily differentiate between a known and likely situation (no copy needed) from a less likely, but still possible genuine error (we need to copy the file, but the attempted copy operation failed for one reason or another). But you're quite right that you could attempt to copy even when a copy is not needed and just let it fail if the file already exists.

Rob · Accepted Answer · 2013-11-13 13:08:16Z

0

A couple of observations:

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.
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.
You are performing sqlite3_step twice in the INSERT statement.
In loadData, you are not logging sqlite3_errmsg if sqlite3_prepare_v2 failed. Always look at sqlite3_errmsg upon any error.

answered Nov 13, 2013 at 13:08

Rob

441k74 gold badges850 silver badges1.1k bronze badges

Collectives™ on Stack Overflow

(Objective C) Save changes in sqlite database

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related