iOS :Issue with bookmarking SQL database

Question

I have created a multi lingual dictionary app which reads several databases , I have a problem with bookmaring... In the app's MainViewController I have an action menu which users select dictionaries. so I have created a key for these dictionaries to recognizing which dictionary is selected . Here is my code :

Reading Data Base

- (NSString *) getDBPath
{
    NSInteger kValue = [[[NSUserDefaults standardUserDefaults] stringForKey:@"Bv"] intValue];

    NSString *documentsDir = [[NSString alloc] init];
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
    documentsDir = [paths objectAtIndex:0];


    switch (kValue) {
        case 1:
             documentsDir = [[NSBundle mainBundle]pathForResource:@"eg-pr" ofType:@"sqlite"];
            break;

            case 2:
             documentsDir = [[NSBundle mainBundle]pathForResource:@"pr-eng" ofType:@"sqlite"];
        default:
            break;
    }

    return documentsDir;
}

the problem is I can not bookmark any word on the device !! what is the problem ???

here is dictionary changing function :

- (void)eng_pr
{

    [[NSUserDefaults standardUserDefaults] setObject:[NSNumber numberWithInt:1] forKey:@"Bv"];
    [[NSUserDefaults standardUserDefaults] synchronize];

    dbClass.viewController = self;
    self.searchbar.placeholder = @"Search";
    [myTable reloadData];
}

and bookmarking :

-(void) setBookMark:(NSInteger)oid {

    sqlite3_stmt    *statement;

    const char *dbpath = [[self getDBPath] UTF8String];

    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        NSString *SetFavSQL = [NSString stringWithFormat: @"UPDATE DIC SET bookmark=1 WHERE id=\"%d\"", oid];
        //      NSLog(@"%@",SetFavSQL);
        const char *SetFav_stmt = [SetFavSQL UTF8String];

        sqlite3_prepare_v2(database, SetFav_stmt, -1, &statement, NULL);
        if (sqlite3_step(statement) != SQLITE_DONE)
        {

        }
        sqlite3_finalize(statement);
        sqlite3_close(database);
    }

}

EDITED :

SEARCHING FUNCTION

- (void)searchWord:(NSString *)txt{

    NSMutableArray *DB_Array = [[NSMutableArray alloc] init];


    NSString *dbPath = [[NSString alloc] initWithString: [self getDBPath]];

    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {


        NSString *sql =[NSString stringWithFormat:@"SELECT * FROM DIC Where Name LIKE \'%@%%\' order by NAME LIMIT 30",txt];

        sqlite3_stmt *compiledStatement;

        if(sqlite3_prepare_v2(database, [sql UTF8String] , -1, &compiledStatement, NULL) == SQLITE_OK) {
            while(sqlite3_step(compiledStatement) == SQLITE_ROW) {

                NSInteger oid = sqlite3_column_int(compiledStatement, 0);

                const char* f1 = (const char*)sqlite3_column_text(compiledStatement, 1);
                NSString *oName = f1 == NULL ? nil : [[NSString alloc] initWithUTF8String:f1];

                const char* f2 = (const char*)sqlite3_column_text(compiledStatement, 2);
                NSString *oMean = f2 == NULL ? nil : [[NSString alloc] initWithUTF8String:f2];


                const char* f3 = (const char*)sqlite3_column_text(compiledStatement, 3);
                NSString *oPron = f3 == NULL ? nil : [[NSString alloc] initWithUTF8String:f3];

                NSInteger bm = sqlite3_column_int(compiledStatement, 5);

                readerClass = [[Reader alloc]initWithReadDB:oid Name:oName Mean:oMean Pron:oPron bookMark:bm];

                [DB_Array addObject:readerClass];

            }
        }
        else {
            NSLog(@"Error retrieving data from database.");
        }
        sqlite3_close(database);
    }
    else {

        NSLog(@"Error: Can't open database!");

    }

    AppDelegate *appDelegateClass = (AppDelegate *)[[UIApplication sharedApplication] delegate];
    [appDelegateClass.wordList removeAllObjects];
    [appDelegateClass.wordList=DB_Array mutableCopy];
}

Answer 1

OK, the problem is that your database (.sqlite) are located in the Resources Folder and all data/files int the resources folder can be read but cannot be written. And when you call the bookmark method your do a query (UPDATE ...) so it try to modify data in the sqlite and it just can't because your .sqlite is in the Resources Folder.

The solution to your problem is to put your .sqlite file in the Document directory and not in the resource directory ;)

Edit : If I were you, at the first application start, copy all your .sqlite from the Resources folder to the Document folder, and work with the .sqlite located in the Document folder.

Some useful tips : Copy file from Resources folder to Document folder

Edit 2:

When you do this :

switch (kValue) {
    case 1:
         documentsDir = [[NSBundle mainBundle]pathForResource:@"eg-pr" ofType:@"sqlite"];
        break;

        case 2:
         documentsDir = [[NSBundle mainBundle]pathForResource:@"pr-eng" ofType:@"sqlite"];
    default:
        break;
}

You are reading the files from the Resource folder, you should read the sqlite from the docuemnt directory :

switch (kValue) {
    case 1:
         documentsDir = [NSString stringWithFormat:@"%@/eg-pr.sqlite", documentsDir];
        break;

        case 2:
         documentsDir = [NSString stringWithFormat:@"%@/pr-eng.sqlite", documentsDir];
    default:
        break;
}

Moreover if kValue is not equals to 1 or 2 the documentDir equals [paths objectAtIndex:0]; (so it will return the Document directory itself and not the sqlite files located in the document directory) does it should be something like that ? :

documentsDir = [NSString stringWithFormat:@"%@/filename.sqlite", documentsDir];