ios - 在 ios 中更新 sqlite 行时出错

Question

当我尝试在 iOS 中更新 sqlite 行时，出现“更新行时出错”错误。这是我的更新方法:

-(BOOL) updateResultForLesson:(int)lessonId result:(int)result{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {

        sqlite3_stmt *statement;
        NSString *querySQL = [NSString stringWithFormat:@"update lessons set result=%d where id=%d",result, lessonId];

        const char *query_stmt = [querySQL UTF8String];

        sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL);


        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSLog(@"updated");
        return YES;
        }
        else {
            sqlite3_reset(statement);
            NSLog(@"error while updating row");
            return NO;
        }
    }
    return NO;

}

这里插入方法效果很好:

-(BOOL) addLesson:(NSString *)title levelId:(NSInteger)levelId categoryId:(NSInteger)categoryId{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        int result = 0;

        NSString *insertSQL = [NSString stringWithFormat:@"insert into lessons (title, level_id, category_id, result) values(\"%@\", \"%d\", \"%d\", \"%d\")",title, levelId, categoryId, result];

        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);

        if (sqlite3_step(statement) == SQLITE_DONE)
        {
           ;
            return YES;
        }
        else {
            sqlite3_reset(statement);
            return NO;
        }
    }
    return NO;

}

我做错了什么？谁能帮忙？插入和选择方法运行良好，我唯一的问题是更新行。

Answer 1

我建议你使用 sqlite3_bind_ 函数:

SQLITE_API int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
SQLITE_API int sqlite3_bind_double(sqlite3_stmt*, int, double);
SQLITE_API int sqlite3_bind_int(sqlite3_stmt*, int, int);
SQLITE_API int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
SQLITE_API int sqlite3_bind_null(sqlite3_stmt*, int);
SQLITE_API int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
SQLITE_API int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
SQLITE_API int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
SQLITE_API int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);

并检查 sqlite3_prepare_v2 是否返回“OK”结果。

试试这段代码:

-(BOOL) updateResultForLesson:(int)lessonId result:(int)result{
    const char *dbpath = [databasePath UTF8String];
     BOOL success = NO;
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {

        sqlite3_stmt *statement;

        const char *query_stmt = "update lessons set result= ? where id= ? ";

        if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) ==SQLITE_OK)
        {

            int bindRes = 0;
            bindRes = sqlite3_bind_int64(statement, 0, result);
            bindRes = sqlite3_bind_int64(statement, 1, lessonId);


            int state = sqlite3_step(statement);
            if (state == SQLITE_DONE)
            {
                success = YES;
            }
            else {
                NSLog(@"Failed to update record");
                success = NO;
            }
        }


        sqlite3_reset(statement);
        sqlite3_finalize(statement);

        return success;
}

警告:我不知道“结果”的类型，因此您必须对列表中的语句使用正确的绑定(bind)函数