iphone - 在 iphone 上删除数据 sqlite - 数据库被锁定

Question

当我尝试从我的 SqlLite 数据库中删除数据时，我的应用程序崩溃了。

这是错误:由于未捕获的异常“NSInternalInconsistencyException”而终止应用程序，原因:“删除时出错。 '数据库已锁定'

这是我在数据库中的所有代码:

#import "Database.h"

static sqlite3 *database = nil;
static sqlite3_stmt *deleteStmt = nil;
static sqlite3_stmt *addStmt = nil;

@implementation Database

@synthesize id_poi, pathDb, arrPoi;

- (id) initWithData:(NSDictionary *) data {

    [super init];
    NSLog(@"%@",[data objectForKey:@"pathDb"]);
    NSLog(@"id_poi = %@",[data objectForKey:@"id_poi"]);
    //setto l'id nello stato dell'oggetto
    id_poi = [data objectForKey:@"id_poi"];
    pathDb = [data objectForKey:@"pathDb"];

    return self;
}

- (void) deletePoi {

    if (sqlite3_open([pathDb UTF8String], &database) == SQLITE_OK) {
        const char *sql = "delete from Poi where id_poi = ?";
        sqlite3_stmt *deleteStmt;
        if(sqlite3_prepare_v2(database, sql, -1, &deleteStmt, NULL) == SQLITE_OK) {
            //When binding parameters, index starts from 1 and not zero.
            sqlite3_bind_int(deleteStmt, 1, [id_poi integerValue]);

            if (SQLITE_DONE != sqlite3_step(deleteStmt)) {
                NSAssert1(0, @"Error while deleting. '%s'", sqlite3_errmsg(database));
                [[WPActivityIndicator sharedActivityIndicator] hide];
                return;
            }
        } else {
            NSAssert1(0, @"Error while creating delete statement. '%s'", sqlite3_errmsg(database));   
        }
        sqlite3_finalize(deleteStmt);
    } else {
        sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
    }


    [[WPActivityIndicator sharedActivityIndicator] hide];
}

- (BOOL) checkIdPoi {

    // lista temporanea
    NSMutableArray *listaTemp = [[NSMutableArray alloc] init];
    // Oggetto che contiene i vari elementi
    NSMutableDictionary *dictionary;

    NSMutableString *str_id_poi;//id della persona

    if (sqlite3_open([pathDb UTF8String], &database) == SQLITE_OK) {
        // query che ricava i valori
        const char *sql = "select id_poi from Poi where id_poi = ?";
        sqlite3_stmt *selectstmt;

        if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {

            sqlite3_bind_int(selectstmt, 1 , [id_poi integerValue]);
            while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                // ricaviamo i valori letti dalla query
                str_id_poi = [NSMutableString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 0)];

                // inseriamo tutti i valori letti in un unico oggetto
                dictionary = [[NSMutableDictionary alloc] initWithObjectsAndKeys:str_id_poi, @"id_poi", nil];
                //NSLog(@"str_id_poi = %@",str_id_poi);
                [listaTemp addObject:dictionary];
                [dictionary release];
                return TRUE;
            }
        } else {
            NSAssert1(0, @"Error while read data. '%s'", sqlite3_errmsg(database));
            return FALSE;
        }
    }
    else {
        sqlite3_close(database);
        return FALSE;
    }

    return FALSE;

}

- (void) addPoi {

    //NSLog(@"%@",pathDb);

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

        const char *sql = "insert into Poi(id_poi) Values(?)";
        sqlite3_stmt *addStmt;
        if(sqlite3_prepare_v2(database, sql, -1, &addStmt, NULL) == SQLITE_OK) {

            NSString *str_id_poi = [[NSString alloc] initWithFormat:@"%d", [id_poi integerValue]];
            sqlite3_bind_text(addStmt, 1, [str_id_poi UTF8String], -1, SQLITE_TRANSIENT);

            if(SQLITE_DONE != sqlite3_step(addStmt)) {
                NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
                [[WPActivityIndicator sharedActivityIndicator] hide];
                return;
            } else {
                //SQLite provides a method to get the last primary key inserted by using sqlite3_last_insert_rowid
                //id_poi = sqlite3_last_insert_rowid(database);
            }

            //Reset the add statement.
            sqlite3_reset(addStmt);


        } else {
            NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));            
        }
        sqlite3_finalize(addStmt); 

    } else {
        sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
    }

    sqlite3_close(database);

    [[WPActivityIndicator sharedActivityIndicator] hide];

}

// Carica i valori dal database passato come parametro
-(NSMutableArray*)getIdPOI {

    // lista temporanea
    NSMutableArray *listaTemp = [[NSMutableArray alloc] init];
    // Oggetto che contiene i vari elementi
    NSMutableDictionary *dictionary;

    NSMutableString *str_id_poi;//id della persona

    if (sqlite3_open([pathDb UTF8String], &database) == SQLITE_OK) {
        // query che ricava i valori
        const char *sql = "select id_poi from Poi";
        sqlite3_stmt *selectstmt;

        if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {

            while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                // ricaviamo i valori letti dalla query
                str_id_poi = [NSMutableString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 0)];

                // inseriamo tutti i valori letti in un unico oggetto
                dictionary = [[NSMutableDictionary alloc] initWithObjectsAndKeys:str_id_poi, @"id_poi", nil];
                //NSLog(@"str_id_poi = %@",str_id_poi);
                [listaTemp addObject:dictionary];
                [dictionary release];
            }

            //Reset the add statement.
            sqlite3_reset(selectstmt);

        } else {
            NSAssert1(0, @"Error while read data. '%s'", sqlite3_errmsg(database));
        }

        //ADD THIS LINE TO YOUR CODE
        sqlite3_finalize(selectstmt);
    }
    else
        sqlite3_close(database);

    sqlite3_close(database);

    return listaTemp;
}


+ (void) finalizeStatements {
    if(database) sqlite3_close(database);
    if(deleteStmt) sqlite3_finalize(deleteStmt);
    if(addStmt) sqlite3_finalize(addStmt);
}


-(void)dealloc {
    [pathDb release];
    [arrPoi release];

    [super dealloc];
}

@end

有什么建议吗？

Answer 1

当针对 sqlite 数据库触发多个查询时，可能会发生这种类型的错误。当您执行任何查询时，您需要同步所有对数据库的调用。

像这样将所有数据库代码包装在@synchronized block 中:

@synchronized(self)
{
   // database query code goes here
}

注意。此示例假定所有数据库查询都是从 1 类内部执行的。如果不是，您应该将“self”替换为一个公共(public)对象实例，该实例由所有与数据库接口(interface)的类共享。或者，您可以在主线程上运行所有数据库语句。