iOS: Sqlite database error : Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: 'error preparing statement'

Question

I had used sqlite DB for my application and i need to call the two simultaneous queries, but i giving error as : Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: 'error preparing statement'

I am running a timer of every 10 seconds for Selecting rows from DB and Insert rows in DB after a button event.

My code snippet as:

in viewWillAppear:

int result2 = sqlite3_open([dbPath UTF8String], &database);
if (result2 != SQLITE_OK) {
    NSLog(@"Failure in connecting to the database with result %d",result2);
}
else {
    NSLog(@ "Succesfully opened connection to DB") ;

}

and in viewWillDisappear:

int result = sqlite3_close(database);
if (result != SQLITE_OK){
    NSLog(@"Failure in closing connection to database. Result %d",result);
}
else {
    NSLog(@"Successfully closed DB connection") ;
}

For Inserting rows:

NSString *queryInsert = [NSString stringWithFormat: @"insert into mail_snoozlist (msgBody,msgSubject, msgSender,msgTo,msgDate,snoozTime) values('%@','%@','%@','%@','%@','%@')",strBody,msgSub,msgFrom,msgTo,strMsgDate,stringFromDate];

    NSLog(@"queryInsert:%@",queryInsert);

    const char *sql = [queryInsert UTF8String];

    if(sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
        sqlite3_step(statement);
        sqlite3_reset(statement);

    } else {

        NSAssert1(0,@"error preparing statement",sqlite3_errmsg(database));
        return;
    }

    sqlite3_finalize(statement);

and for Selecting rows:

NSString *querySQL2 = [NSString stringWithFormat: @"Select * from mail_snoozlist WHERE snoozTime = '%@'",_snoozTime];

    NSLog(@"querySql:%@",querySQL2);

    if (sqlite3_prepare_v2(database, [querySQL2 UTF8String], -1, &statement, NULL) == SQLITE_OK)
    {

        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            Message *obj = [[Message alloc] init];

            NSString *msgBody=[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
            obj.msgBody= msgBody;

            NSString *msgSub=[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)];
            obj.msgSub= msgSub;

            NSString *msgSender=[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)];
            obj.msgFrom= msgSender;

            NSString *msgTo=[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)];
            obj.msgTo= msgTo;

            NSString *msgDate=[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 5)];
            obj.msgDate= msgDate;

            [listOfItems addObject:obj];

            [self.tableView reloadData];
        }

        sqlite3_reset(statement);
        sqlite3_finalize(statement);

Anyone please help me to solve this problem.

Thanks!

Answer 1

You should change your NSAssert statement to include the error message:

NSAssert1(0, @"error preparing statement: %s", sqlite3_errmsg(database));

Once you do that, you should get a meaningful response which will help you diagnose the problem.

Without looking at the sqlite3_errmsg message, it is difficult to diagnose the problem. It could be as simple as a typo in a column name or table name or as complicated as the table not being found because the database wasn't found when it was created, so a blank database (without that table) was created. Hard to say until we see the error message.

---

As an aside, you should not be building your SQL with stringWithFormat because you open yourself to SQL injection attacks as well as will have problems if any of those text values have an apostrophe in them. You should use ? placeholders instead of printf-style formatters, and then bind the values to those columns with sqlite3_bind_text calls:

NSString *queryInsert = @"insert into mail_snoozlist (msgBody,msgSubject, msgSender,msgTo,msgDate,snoozTime) values(?, ?, ?, ?, ?, ?)";

if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) {
    NSAssert1(0,@"error preparing statement: %s",sqlite3_errmsg(database));
    return;
}

// for these 6 sqlite3_bind function calls, if any of these strings can be `nil`, then you'd
// want to call sqlite3_bind_null if that's the case, rather than sqlite3_bind_text

if (sqlite3_bind_text(statement, 1, [strBody UTF8String], -1, NULL) != SQLITE_OK) {
    NSAssert1(0,@"error binding 1: %s",sqlite3_errmsg(database));
    sqlite3_finalize(statement);
    return;
}

if (sqlite3_bind_text(statement, 2, [msgSub UTF8String], -1, NULL) != SQLITE_OK) {
    NSAssert1(0,@"error binding 2: %s",sqlite3_errmsg(database));
    sqlite3_finalize(statement);
    return;
}

if (sqlite3_bind_text(statement, 3, [msgFrom UTF8String], -1, NULL) != SQLITE_OK) {
    NSAssert1(0,@"error binding 3: %s",sqlite3_errmsg(database));
    sqlite3_finalize(statement);
    return;
}

if (sqlite3_bind_text(statement, 4, [msgTo UTF8String], -1, NULL) != SQLITE_OK) {
    NSAssert1(0,@"error binding 4: %s",sqlite3_errmsg(database));
    sqlite3_finalize(statement);
    return;
}

if (sqlite3_bind_text(statement, 5, [strMsgDate UTF8String], -1, NULL) != SQLITE_OK) {
    NSAssert1(0,@"error binding 5: %s",sqlite3_errmsg(database));
    sqlite3_finalize(statement);
    return;
}

if (sqlite3_bind_text(statement, 6, [stringFromDate UTF8String], -1, NULL) != SQLITE_OK) {
    NSAssert1(0,@"error binding 6: %s",sqlite3_errmsg(database));
    sqlite3_finalize(statement);
    return;
}

if (sqlite3_step(statement) != SQLITE_DONE) {
    NSAssert1(0,@"error stepping: %s",sqlite3_errmsg(database));
}

sqlite3_finalize(statement);

I illustrate the issue with the insert statement, but the same should be done with the select statement, too.

Answer 2

Add these two methods and call them in insert method before insertion and selection,

 - (void) createEditableCopyOfDatabaseIfNeeded
{


    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"dbname.sqlite"];
    success = [fileManager fileExistsAtPath:writableDBPath];
    if (success) return;
    //{

    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"dbname.sqlite"];

    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    // }
    if (!success)
    {
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }
}


- (void)initializeDatabase
 {



NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"dbname.sqlite"];
sqlite3_open([path UTF8String], &database);
}