Sqlite DB no such table exists

Question

Ok so I have a database in my iPhone simulator documents. And I now know for sure it's in the applications sandbox. Something is funky in the code I have. So I first get the DB path:

-(NSString *)getsynDbPath
{
    NSString* dataBAse = [[NSBundle mainBundle] pathForResource:@"ddd"ofType:@"sqlite"];

    return dataBAse;
}

Then I test the path:

    NSString *testData;
    testData = [self getsynDbPath];

    NSFileManager * fileManager = [NSFileManager defaultManager];

    BOOL success = [fileManager fileExistsAtPath:testData];

    if (success) {
        NSLog(@"Oh no! There was a big problem!");
    } else {
       //Successfully opened 
        if(sqlite3_open([testData UTF8String], &db)==SQLITE_OK){

            NSLog(@"Raise the roof!");

            //Calling method to loop through columns
            [self listOfCols];

        }


    }

I then go to a custom method where I loop through the columns inside the database:

-(NSArray *)listOfCols{

    NSMutableArray *retval = [[[NSMutableArray alloc]init]autorelease];

    NSString *query = @"SELECT KEY_ID FROM CON_DETAIL";

    sqlite3_stmt *statement;
      //Does not execute
    if (sqlite3_prepare_v2(db, [query UTF8String], -1, &statement, nil)==SQLITE_OK) {

        while (sqlite3_step(statement)==SQLITE_ROW) {

            int key_id = sqlite3_column_int(statement, 0);

            NSLog(@"Key ID: %d", key_id);

            char *nameChars = (char *) sqlite3_column_text(statement, 1);

            NSLog(@"chars %s", nameChars);

            char *cityChars = (char *) sqlite3_column_text(statement, 2);

            NSLog(@"chars %s", cityChars);
       }
    }

    NSLog(@"%s Why '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(db), sqlite3_errcode(db));

    return retval;

}

So here's my question. After I successfully opened the database, why the heck am I getting a log error that says: no such table: CON_DETAIL ? Any help is appreciated.

Answer 1

I think you have to copy your db in your document directory and then try to fetch. Copy it with following functions.

-(void) dbconnect{

    self.databaseName = @”yourdbname.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 = [documentsDir stringByAppendingPathComponent:self.databaseName];

    // Execute the “checkAndCreateDatabase” function

    [self checkAndCreateDatabase];
}

-(void) checkAndCreateDatabase{
    // Check if the SQL database has already been saved to the users phone, if not then copy it over
    BOOL success;

    // Create a FileManager object, we will use this to check the status
    // of the database and to copy it over if required
    NSFileManager *fileManager = [NSFileManager defaultManager];

    // Check if the database has already been created in the users filesystem
    success = [fileManager fileExistsAtPath:databasePath];

    // If the database already exists then return without doing anything
    if(success) {
        return;
    }
    // If not then proceed to copy the database from the application to the users filesystem

    // Get the path to the database in the application package
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

    // Copy the database from the package to the users filesystem
    [fileManager copyItemAtPath:databasePathFromApp toPath:self.databasePath error:nil];

    [fileManager release];

}

NOTE: If you are not getting db in your app’s document directory do the following. Go to : Target -> “Build Phases” -> “copy bundle Resources” Then add that particular file here.

After that call your "listOfCols" method.