iOS sqlite 无法提交，没有事务处于事件状态

Question

我正在 iOS 上做一个按钮来更新 SQlite 数据。我可以使用 Firefox SQLite Manager 更新值，但是使用下面的代码会出现“无法提交，没有事务处于事件状态”的问题。请说明一下。谢谢。

=- (id *)updateBanks{

sqlite3_stmt *statement = NULL;

const char *branch_no = "MAYBANK1";
const char *address = "KLCC";

NSFileManager *fileMgr = [NSFileManager defaultManager];

NSString *dbPath = [[[NSBundle mainBundle] resourcePath ]stringByAppendingPathComponent:@"banks.sqlite"];

BOOL success = [fileMgr fileExistsAtPath:dbPath];

if(!success)
{
    NSLog(@"Cannot locate database file '%@'.", dbPath);
}
if(!(sqlite3_open([dbPath UTF8String], &db) == SQLITE_OK))
{
    NSLog(@"An error has occured.");

}

    if(sqlite3_open([dbPath UTF8String], &db) == SQLITE_OK)
{
    const char *sql = "Update Maybank Set address = ? Where branch_no = ?";
    if(sqlite3_prepare_v2(db, sql, -1, &statement, NULL)==SQLITE_OK){
        sqlite3_bind_text(statement, 1, address, -1, SQLITE_TRANSIENT);
        //sqlite3_bind_text(updateStmt, 1, [address.text UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 2, branch_no, -1, SQLITE_TRANSIENT);


    }
}
char* errmsg;
sqlite3_exec(db, "COMMIT", NULL, NULL, &errmsg);

if(SQLITE_DONE != sqlite3_step(statement)){
    NSLog(@"Error while updating. %s", sqlite3_errmsg(db));
}
else{

}
sqlite3_finalize(statement);
sqlite3_close(db);
return 0;

Answer 1

documentation说:

Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.

Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command.

因此，您的 UPDATE 语句的自动事务已经提交。仅当您还运行了 BEGIN 时才需要运行 COMMIT。

---

另外，你不能调用 sqlite3_open 两次；第一个数据库连接将被泄露。

另外，在执行 COMMIT 之前，您必须调用 sqlite3_step 和 sqlite3_finalize 。

此外，资源是只读的；你必须 copy the database file out of the bundle能够对其进行修改。