0

I have an app that allows you to add items to a list, (From a tableview), when the user clicks on the add button next to the corresponding item, it calls the following +(void)addItem method. Everything works fine, but after heavy testing (60-70 back to back inserts causes it to return the SQL error code "Unable to open database file" and the app has to be restarted. Any ideas? Thanks!

+(void)addItem:(NSString *)dbPath{

NSUserDefaults *prefs = [NSUserDefaults standardUserDefaults];

NSString *oldTrip = [prefs objectForKey:@"selectedList"];

NSString *table = @"The Table";

NSString *name = [prefs objectForKey:@"SelectedAddItem"];    
NSString *countStr = @"1";
NSString *doneStr = @"No";
NSString *noteStr = @"None";
NSString *orderStr = @"15";


sqlite3 *database;


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

    const char *sqlStr = [[NSString stringWithFormat:@"Insert into '%@' Values (null,?,?,?,?,?,?)",oldTrip]UTF8String];        

    sqlite3_stmt *compiledStatement;


    if (sqlite3_prepare_v2(database, sqlStr, -1, &compiledStatement, NULL) == SQLITE_OK){

        sqlite3_bind_text(compiledStatement, 0,[oldTrip UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(compiledStatement, 1,[name UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(compiledStatement, 2,[table UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(compiledStatement, 3,[countStr UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(compiledStatement, 4,[doneStr UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(compiledStatement, 5,[noteStr UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(compiledStatement, 6,[orderStr UTF8String], -1, SQLITE_TRANSIENT);

    }

    if(sqlite3_step(compiledStatement) != SQLITE_DONE ) {


        NSString* messageString = [NSString stringWithFormat: @"%s\n\nPlease contact the developer if this error persists", sqlite3_errmsg(database)]; 

        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Error inserting" message:messageString delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];


        [alert show];
        [alert release];

    } else {

        sqlite3_reset(compiledStatement);

        sqlite3_finalize(compiledStatement);
    }

}

sqlite3_close(database);

}

To get the DB Information, I use:

- (NSString *) getDBPath {

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
NSString *documentsDir = [paths objectAtIndex:0];
return [documentsDir stringByAppendingPathComponent:@"ListDatabase.sql"];
}
Emre Yazici
  • 10,136
  • 6
  • 48
  • 55
Mark
  • 3
  • 1
  • Why in `sqlStr` you have five `?` bind placeholders, but `sqlite3_bind_text` six times? – jamapag Jul 05 '11 at 20:31
  • On the topic of binding etiquette, you may want to use integer bindings for `count` and `order`. On top of that, it's not clear to me why you need `NSUserDefaults` for this at all, since this code is presumably getting called by a `UIViewController` subclass that could pass the table and selected item as arguments—but that's a comment from the standpoint of someone who has no idea what your code is like. – Joe Osborn Jul 05 '11 at 20:42
  • Thanks, no, your right. The NSUserDefaults was how I was passing data before I knew how to properly accomplish that task, I have learned very quickly that cleaning up code after the fact sucks, and to try and do it right the first time around. – Mark Jul 05 '11 at 20:53

1 Answers1

0

If it happens only under heavy load, you may want to try leaving the database file open rather than opening and closing it for every operation. I'm not very familiar with SQLite, but you may also be able to reuse the compiled statement just by resetting its bindings each time around (and you don't need to call reset before finalize if you don't intend to reuse the statement).

Perhaps you can open and close your database in your view controller's viewDidLoad and viewDidUnload methods?

Joe Osborn
  • 1,145
  • 7
  • 10