iPhone SDK: SQLite error: no such table:, even though a terminal query returns successful

I have a problem with sqlite database integration in my app. My app uses a translator style function, whereby a users text entry is converted into another way of wording the same word, the word alternatives are listed in a SQLite database.

Basically the code is designed to take text, split it into individual words, and search for them on the database, and return the result for each word, however, the application ends up not returning results and I get the error "error: no such table: dictionary" Even though a query on terminal returns results successfully.

Can anyone give me a hand in identifying what's been done wrong? Thanks

This is the code I used:

-(void)translate{
//take input and break into an array
NSString *clearText = [[NSString alloc] init];
clearText=inputBox.text;
NSArray *words = [[NSArray alloc] init];
words= [clearText componentsSeparatedByString:@" "];
numOfWords=words.count;
NSString *newText=@"";
//open database
sqlite3 *db = [self getNewDBConnection];
//loop through array
for(i=0;i<numOfWords;i++){
    sqlite3_stmt *resultStatement= nil;
    NSString *res = [NSString stringWithFormat:@"select * from dictionary where plain='%@'",[words objectAtIndex:i]];
    if((sqlite3_prepare_v2(db, [res UTF8String], -1, &resultStatement, nil))!=SQLITE_OK){
        NSLog(@"Error getting result, maybe word not found\n"); 
         //NSLog(@"tried query:%@",res);
        NSLog(@"error: %s", sqlite3_errmsg(db));
    }
    else{
        if(sqlite3_step(resultStatement)==SQLITE_ROW){
            //in the line below, 1 is the column number of the replacement word
            NSString *add = [[NSString alloc] initWithUTF8String: (char*)sqlite3_column_text(resultStatement,1)];
            newText=[newText stringByAppendingString:add];
            [add release];
        }
    }
    sqlite3_finalize(resultStatement);
}
//output result
outputBox.text=newText;
sqlite3_close(db);
}
-(sqlite3 *) getNewDBConnection{
sqlite3 *newDBconnection;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"data9.sqlite"];


if (sqlite3_open([path UTF8String], &newDBconnection) == SQLITE_OK) {

    NSLog(@"Database Successfully Opened");

} else {
    NSLog(@"Error in opening database");
}

return newDBconnection; 
}

This is the code used to copy the database:

- (void)createEditableCopyOfDatabaseIfNeeded {
// First, test for existence.
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSear开发者_JAVA技巧chPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"data9.sqlite"];
success = [fileManager fileExistsAtPath:writableDBPath];
if (success)  return;
// The writable database does not exist, so copy the default to the appropriate location.
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"data9.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if (!success) {
    NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
   }
}

Based on the missing table, it looks like it is creating a new database at the path. So either the path is wrong, or it is possible that you might not have copied the master database to that location. The SQLite file is usually in the bundle and needs to be copied first.

Major issues from my perspective. I went so far as to delete the entire database capability and then ran the app and it still opened the database.

I have this code in applicationDidFinishLaunchingWithOption in the appDelegate. It is designed for start-up to check a valid database is avaailable, so mostly it is never executed. I removed the conditional statement around the copy, and inserted a removeItemAtPath statement to delete the file. Ran it once, and my problem all went away (or at least those with the database, by biggest problem is still user error :-))

NSString *docsDir;
NSArray *dirPaths;
// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

docsDir = [dirPaths objectAtIndex:0];

// Build the path to the database file
NSString *databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"languageDB.sqlite"]];

NSFileManager *filemgr = [NSFileManager defaultManager];

NSError *error;
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"language.sqlite"];
NSString *writableDBPath = [docsDir stringByAppendingPathComponent:@"languageDB.sqlite"];

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

[filemgr copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
NSLog(@"copy error %@",error);
}

Something else you might want to make sure of if you're adding a table to an existing database and then trying to re-add it to your project is that you delete the copy of the database in the location where Xcode is copying it prior to loading your app in the simulator. For instance, when I was having this problem, I had to go into /Users/<user>/Library/Application Support/iPhone Simulator/6.0/Applications/1234DFE1-5AA9-4CFF-0235-3D98961E9281/Documents/ to delete the database there. When I cleaned and re-ran the simulator, it found my new table just fine.