Sqlite database locked error - iOS

Question

I'm using sqlite to use insert and update the data in table view. I can able to update the data only once, on the next attempt it showing database locked. Even though am closing the database, please help. Below is the code.

-(void)saveUserCredential: (NSString *)email :(NSString *)userName :(NSString *)loginTime :(NSString *)source
{
NSDateFormatter *dateformate=[[NSDateFormatter alloc]init];
[dateformate setDateFormat:@"yyyy-MM-dd HH:mm"]; // Date formater
NSString *todayDate = [dateformate stringFromDate:[NSDate date]];

const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
    NSString  * query = @"SELECT * from users";
    int rc =sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, NULL);
    if(rc == SQLITE_OK)
    {
        if(sqlite3_step(statement) == SQLITE_ROW)
        {

              NSString *updateSQL = [NSString stringWithFormat:@"update users set email = '%@', username = '%@', source = '%@', created = '%@' where id = '%d'",email, userName, source, todayDate,1];

            const char *update_stmt = [updateSQL UTF8String];
            sqlite3_prepare_v2(database, update_stmt, -1, &statement, NULL );
            //sqlite3_bind_int(statement, 1, 1);
            if (sqlite3_step(statement) == SQLITE_DONE)
            {
                NSLog(@"successfully updated");
            }
            else{

                NSLog(@"Error while updating. '%s'", sqlite3_errmsg(database));
            }

        }
        else
        {
            NSString *insertSQL = [NSString stringWithFormat:@"insert into users (email,username,source,created) values('%@','%@','%@','%@')",email,userName,source,todayDate];

            NSLog(@"INS SQL: %@", insertSQL);
            const char *insert_stmt = [insertSQL UTF8String];
            sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
            if (sqlite3_step(statement) == SQLITE_DONE)
            {
                NSLog(@"INSERTED");
            }
            else
            {
                NSLog(@"NOT INSERTED");
            }
            NSLog(@"hello ");

        }
        sqlite3_finalize(statement);
        sqlite3_close(database);

    }

}

}

Midhun MP · Accepted Answer · 2016-02-25 09:39:48Z

2

You need to call

sqlite3_finalize(statement);

for each successful sqlite_prepare_v2 call. It should be balanced. Also use different sqlite3_stmt for each query.

So your code need to be changed like:

const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
    NSString  * query = @"SELECT * from users";
    int rc =sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, NULL);
    if(rc == SQLITE_OK)
    {
        if(sqlite3_step(statement) == SQLITE_ROW)
        {

              NSString *updateSQL = [NSString stringWithFormat:@"update users set email = '%@', username = '%@', source = '%@', created = '%@' where id = '%d'",email, userName, source, todayDate,1];

            const char *update_stmt = [updateSQL UTF8String];
            sqlite3_stmt *upStmt;
            sqlite3_prepare_v2(database, update_stmt, -1, &upStmt, NULL );
            //sqlite3_bind_int(upStmt, 1, 1);
            if (sqlite3_step(upStmt) == SQLITE_DONE)
            {
                NSLog(@"successfully updated");
            }
            else
            {

                NSLog(@"Error while updating. '%s'", sqlite3_errmsg(database));
            }
            sqlite3_finalize(upStmt);
        }
        else
        {
            NSString *insertSQL = [NSString stringWithFormat:@"insert into users (email,username,source,created) values('%@','%@','%@','%@')",email,userName,source,todayDate];

            NSLog(@"INS SQL: %@", insertSQL);
            const char *insert_stmt = [insertSQL UTF8String];
            sqlite3_stmt *inStmt;
            sqlite3_prepare_v2(database, insert_stmt,-1, &inStmt, NULL);
            if (sqlite3_step(inStmt) == SQLITE_DONE)
            {
                NSLog(@"INSERTED");
            }
            else
            {
                NSLog(@"NOT INSERTED");
            }
            NSLog(@"hello ");
           sqlite3_finalize(inStmt);

        }
        sqlite3_finalize(statement);
        sqlite3_close(database);

    }

}

edited Feb 25, 2016 at 9:39

answered Feb 25, 2016 at 9:13

Midhun MP

108k31 gold badges157 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mac_Play Over a year ago

so I need to add finalize statement after this line if (sqlite3_step(statement) == SQLITE_DONE) { NSLog(@"successfully updated"); } else{ NSLog(@"Error while updating. '%s'", sqlite3_errmsg(database)); }

Mac_Play Over a year ago

It's not updating the table now.

Midhun MP Over a year ago

@Mac_Play: I had a typo in the code and it is now fixed, please check now

Mac_Play Over a year ago

Actually u have added 2 sqlite3_stmt seperately to handle insert and update right ? or you made any other changes ?

Collectives™ on Stack Overflow

Sqlite database locked error - iOS

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related