SQLite Table does not exist

Question

EDIT: SOLVED! answer below first code box

When i try to connect with a sqlite database (with a table TblTest). In debug mode in my app, the error is:

* Assertion failure in -[QueryClass getTestData:], * Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: 'Cant build SQL to read item [no such table: TblTest]'

In release mode (simulator), it does not give an error but it will not read the database. The code behaves like it can connect to the db, but can NOT read the TblTest?

I already reset the simulator and used clean and build.

Sorry for the long code:

@implementation QueryClass
@synthesize databaseName;
@synthesize databaseLite;

-(id)initWithDatabase:(NSString *)databaseNameP{
    if(self = [super init]){
        [self createEditableCopyOfDatabaseIfNeeded];
        self.databaseName = databaseNameP;
        self.databaseLite = [self getNewDBConnection];
    }
    return self;
}

- (void)createEditableCopyOfDatabaseIfNeeded {
    NSLog(@"Creating editable copy of database");
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:self.databaseName];
    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:self.databaseName];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file with message ‘%@’.", [error localizedDescription]);
    }
}

- (sqlite3 *) getNewDBConnection{
    sqlite3 *newDBconnection;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:self.databaseName];
    // Open the database. The database was prepared outside the application.
    if (sqlite3_open([path UTF8String], &newDBconnection) == SQLITE_OK) {

        NSLog(@"Database Successfully Opened  ");

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

    return newDBconnection; 
}

-(NSMutableArray *)getTestData:(NSInteger) testId{
    (NSMutableArray *testArray = [[NSMutableArray alloc]init];

    int ret;
    sqlite3_stmt *selStmt = nil;
    const char *sql = "SELECT testId, name, FROM TblTest WHERE testId = ?";

    if (!selStmt)
    { // build update statement
        if (sqlite3_prepare_v2(self.databaseLite, sql, -1, &selStmt, NULL)!=SQLITE_OK)
        {
            selStmt = nil;
        }
    }

    sqlite3_bind_int(selStmt, 1, testId);

    if(!selStmt){
        NSAssert1(0, @"Cant build SQL to read item [%s]", sqlite3_errmsg(self.databaseLite));
    }

    while ((ret=sqlite3_step(selStmt))==SQLITE_ROW) 
    { // get the fields from the record set and assign to item

        Test *test = [[Test alloc]init];

        NSNumber *testId = [NSNumber numberWithInt: (int) sqlite3_column_int(selStmt, 0)];
        NSString *name = [NSString stringWithUTF8String:(char *) sqlite3_column_text(selStmt, 1)];


        test.testId =testId;
        test.name   =name;


        [testArray addObject:pill];
        [test release];
    }
    sqlite3_reset(selStmt);

    return [testArray autorelease];
}



//in my function i do:
qc = [[QueryClass alloc]initWithDatabase:@"databaseLite.sql"];
[qc getTestData:0];

---

I rewrote my code using this tutorial and it works like a charm. :)

Thanks

-(id)initWithDatabase:(NSString *)databaseNameP{
    if(self = [super init]){
        [self createEditableCopyOfDatabaseIfNeeded];
        [self initializeDatabase];
    }
    return self;
}

// Creates a writable copy of the bundled default database in the application Documents directory.
- (void)createEditableCopyOfDatabaseIfNeeded {
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"databaseLite4.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:@"databaseLite4.sqlite"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }
}

// Open the database connection and retrieve minimal information for all objects.
- (void)initializeDatabase {

    // The database is stored in the application bundle. 
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    self.path = [documentsDirectory stringByAppendingPathComponent:@"databaseLite4.sqlite"];

}

Answer 1

When you solve your own problems, you should provide your solution as an answer rather than updating your question. It is possible (and recommended in this situation) to answer your own question and accept it as your answer. You will not gain any reputation for accepting your own answer, but the question will be correctly listed as solved.

Below is the answer I extracted from your question. Feel free to accept it.

---

I rewrote my code using this tutorial and it works like a charm. :)

Here's my solution:

-(id)initWithDatabase:(NSString *)databaseNameP{
    if(self = [super init]){
        [self createEditableCopyOfDatabaseIfNeeded];
        [self initializeDatabase];
    }
    return self;
}

// Creates a writable copy of the bundled default database in the application Documents directory.
- (void)createEditableCopyOfDatabaseIfNeeded {
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"databaseLite4.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:@"databaseLite4.sqlite"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }
}

// Open the database connection and retrieve minimal information for all objects.
- (void)initializeDatabase {

    // The database is stored in the application bundle. 
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    self.path = [documentsDirectory stringByAppendingPathComponent:@"databaseLite4.sqlite"];

}