Getting database locked error while adding row 2nd time

Question

I am getting error Database Locked. at first attempt it adds the row but after that i am getting the error database locked.

I am trying to make web page saver so at first time when app loads it is adding the row but when again I try to save any webpage it is not saving and I am getting the error database locked.

Even the deletion is also not happening after saving one web page.

#import "DBManager.h"
static DBManager *sharedInstance = nil;
static sqlite3 *database = nil;
static sqlite3_stmt *statement = nil;

 @implementation DBManager

 +(DBManager*)getSharedInstance{
if (!sharedInstance) {
    sharedInstance = [[super allocWithZone:NULL]init];
    [sharedInstance createDB];
}
return sharedInstance;
}

-(BOOL)createDB{
NSString *docsDir;
NSArray *dirPaths;
// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = dirPaths[0];
// Build the path to the database file
databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent: @"browser.db"]];

BOOL isSuccess = YES;
NSFileManager *filemgr = [NSFileManager defaultManager];
if ([filemgr fileExistsAtPath: databasePath ] == NO)
{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        char *errMsg;
        NSLog(@"insod");
        const char *sql_stmt ="create table if not exists list(sno int primary key,name varchar(50),category varchar(30),path varchar(500),fav int)";
        if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg)
            != SQLITE_OK)
        {
            isSuccess = NO;
            NSLog(@"Failed to create table");
        }
        sqlite3_close(database);
        return  isSuccess;
    }
    else {
        isSuccess = NO;
        NSLog(@"Failed to open/create database");
    }
}else{
    NSLog(@"File Exist");
}
return isSuccess;
 }

 -(BOOL) Delete:(NSString *) name{
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK){
    NSString *query = [NSString stringWithFormat:@"delete from list where name like '%@'",name];
    const char *stmt = [query UTF8String];
    sqlite3_prepare_v2(database, stmt,-1, &statement, NULL);
    if (sqlite3_step(statement) == SQLITE_DONE){
        sqlite3_finalize(statement);
        return YES;
    }else {
         NSLog(@"error: %s",sqlite3_errmsg(database));
        sqlite3_finalize(statement);
        return NO;
    }

}else{
    sqlite3_close(database);
    return NO;
}

 }

-(NSDictionary *) CatList:(NSString *) cat{
const char *dbpath = [databasePath UTF8String];
NSDictionary *dict;
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{

    NSString *querySQL =[NSString stringWithFormat:@"select name,path from list where category like '%@'",cat];
    const char *query_stmt = [querySQL UTF8String];

    if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
       // NSLog(@"inside list");

        NSMutableArray *arr1 = [[NSMutableArray alloc] init];
        NSMutableArray *arr2 = [[NSMutableArray alloc] init];
        //            NSMutableArray *arr3 = [[NSMutableArray alloc] init];
        while(sqlite3_step(statement) == SQLITE_ROW)
        {

            NSString *name = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
            [arr1 addObject:name];
            //NSLog(@"%@",name);
            NSString *category = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
            [arr2 addObject:category];
            //                //NSLog(@"%@",dept);
            //                NSString *path = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)];
            //                [arr3 addObject:path];
        }
        sqlite3_finalize(statement);
        dict = @{@"name":arr1,@"path":arr2};
        //NSLog(@"%@",dict);
    }else{
        NSLog(@"error: %s",sqlite3_errmsg(database));
    }
    sqlite3_close(database);
}
return dict;
 }

 - (BOOL) saveData:(NSString*)name category:(NSString*)category path:(NSString*)path{
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{

    NSString *highest = @"select max(sno) from list";
    const char *Query = [highest UTF8String];

    if (sqlite3_prepare_v2(database, Query, -1, &statement, NULL) == SQLITE_OK){
        if (sqlite3_step(statement) == SQLITE_ROW){
            int sno = sqlite3_column_int(statement, 0);
            NSString *insertSQL = [NSString stringWithFormat:@"insert into list values(\"%d\",\"%@\",\"%@\", \"%@\",\"%d\")",sno+1,name, category, path,0];
            const char *insert_stmt = [insertSQL UTF8String];
            sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
            if (sqlite3_step(statement) == SQLITE_DONE){
                sqlite3_finalize(statement);
                return YES;
            }else {
                sqlite3_finalize(statement);
                return NO;
            }

        }else{
            NSString *insertSQL = [NSString stringWithFormat:@"insert into list values(\"%d\",\"%@\",\"%@\", \"%@\",\"%d\")",1,name, category, path,0];
            const char *insert_stmt = [insertSQL UTF8String];
            sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
            if (sqlite3_step(statement) == SQLITE_DONE){
                sqlite3_finalize(statement);
                return YES;
            }else {
                sqlite3_finalize(statement);
                return NO;
            }
        }
    }
    sqlite3_close(database);
}
return NO;
 }

 -(BOOL) Fav:(NSString *) name{
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK){
    NSString *query = [NSString stringWithFormat:@"update list set fav = 1 where name like '%@'",name];
    const char *stmt = [query UTF8String];
    sqlite3_prepare_v2(database, stmt,-1, &statement, NULL);
    if (sqlite3_step(statement) == SQLITE_DONE){
        sqlite3_finalize(statement);
        return YES;
    }else {
        sqlite3_finalize(statement);
        return NO;
    }
}else{

    return NO;
}
 }

 -(NSArray *) GetListFav{
const char *dbpath = [databasePath UTF8String];
NSArray *dict;
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{

    NSString *querySQL = @"select name from list where fav = 1";
    const char *query_stmt = [querySQL UTF8String];

    if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        NSMutableArray *arr1 = [[NSMutableArray alloc] init];
        while(sqlite3_step(statement) == SQLITE_ROW)
        {
            NSString *name = [[NSString alloc] initWithUTF8String:              (const char *) sqlite3_column_text(statement, 0)];
            [arr1 addObject:name];
        }
        sqlite3_finalize(statement);
        dict = arr1;
    }else{
        NSLog(@"error: %s",sqlite3_errmsg(database));
    }
    sqlite3_close(database);
}
return dict;
 }

 -(BOOL) removeFav:(NSString *) name{
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK){
    NSString *query = [NSString stringWithFormat:@"update list set fav = 0 where name like '%@'",name];
    const char *stmt = [query UTF8String];
    sqlite3_prepare_v2(database, stmt,-1, &statement, NULL);
    if (sqlite3_step(statement) == SQLITE_DONE){
        sqlite3_finalize(statement);
        return YES;
    }else {
        sqlite3_finalize(statement);
        return NO;
    }

}else{
    return NO;
}
}

 -(NSArray *) GetList{
const char *dbpath = [databasePath UTF8String];
NSArray *dict;
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{

    NSString *querySQL = @"select name from list";
    const char *query_stmt = [querySQL UTF8String];

    if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        //NSLog(@"inside list");

        NSMutableArray *arr1 = [[NSMutableArray alloc] init];
        while(sqlite3_step(statement) == SQLITE_ROW)
        {
            NSString *name = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
            [arr1 addObject:name];
        }
        sqlite3_finalize(statement);
        dict = arr1;
    }else{
        NSLog(@"error: %s",sqlite3_errmsg(database));
    }
    sqlite3_close(database);
}
return dict;
  }

 @end

score 0 · Answer 1 · answered Jul 22 '16 at 19:57

0

You can only access sqllite once at a time. If you have multiple threads, you can run in this situation. Example:

So always try to close database once it is used using:

sqlite3_close(database);

answered Jul 22 '16 at 19:57

AtWork

1,283
1
14
34

Rob · Answer 2 · 2016-07-22T20:11:50.253

You need to make sure every sqlite3_open is balanced with a sqlite3_close before trying to do sqlite3_open again:

Your saveData method has quite a few return statements that will prevent the database from ever calling sqlite3_close. Make sure all paths out of the method close the database properly. In the end, this means that you'll try to open the database although it's already open.

Or, better, just open the database once and leave it open, eliminating the repeated opening and closing of the database.
Your Delete, Fav, and removeFav methods are also not closing the database.

A few unrelated observations:

You should be wary about using stringWithFormat to build SQL with string parameters. If the string being searched for had a apostrophe in it, your code will fail. Use ? placeholders and then use sqlite3_bind_text to bind values to those placeholders.
If you make your sno column a AUTOINCREMENT, you won't have to do that "get the max sno before inserting new row" logic. Thus, the CREATE statement might look like:
```
create table if not exists list (
    sno integer primary key autoincrement, 
    name text, 
    category text, 
    path text, 
    fav integer)
```
You can then omit sno from the INSERT statements, and it will automatically be assigned a unique identifier.

Getting database locked error while adding row 2nd time

2 Answers2