sqlite3更新在iOS中不起作用

问题描述：

我正在尝试更新sqlite数据库.这是我正在使用的代码

I am trying to update sqlite db. This is the code I am using

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

        const char * sql;
        sql = "update tms  set name = ?,place=?,stars=?  where id=?";

        sqlite3_stmt *selectStatement;

        //prepare the select statement
        int returnValue = sqlite3_prepare_v2(database, sql, -1, &selectStatement, NULL);
        if(returnValue == SQLITE_OK)
        {
            sqlite3_bind_text(selectStatement, 1,[[payloadDict  valueForKey:@"userName"] UTF8String] , [[payloadDict  valueForKey:@"userName"] length],SQLITE_STATIC);
            sqlite3_bind_text(selectStatement, 2,[[payloadDict  valueForKey:@"locName"] UTF8String], [[payloadDict  valueForKey:@"locName"] length],SQLITE_STATIC);
            sqlite3_bind_int(selectStatement, 3, [[payloadDict  valueForKey:@"starCount"] integerValue]);
            sqlite3_bind_int(selectStatement, 4, [[payloadDict  valueForKey:@"rowid"] integerValue]);
            int success = sqlite3_step(selectStatement);
            if(success == SQLITE_DONE)
            {
                isExist = TRUE;
            }
            else {
                //NSAssert1(0,@"Error: Failed to Update %s",sqlite3_errmsg(database));
            }
    }

执行sqlite3_step时，我获得的成功值为101.但是不会使用新值更新数据库.如何正确执行此操作?谢谢

I am getting value 101 as success when sqlite3_step is executed. But database is not updated with new values. How can I do this properly? Thanks

答

我同意@ott关于确保数据库位于 Documents 目录中的出色建议(尽管我本以为那是会给你一个错误).

I agree with @ott's excellent suggestion of making sure the database is located in the Documents directory (though I would have thought that that would have given you an error).

我还要仔细检查 [[payloadDict valueForKey:@"rowid"] integerValue] 返回的值，以确保它与 id 列中的值匹配表中现有行之一.如果不匹配，即使未进行任何更新， sqlite3_step 也会返回 SQLITE_DONE .

I'd also double check the value returned by [[payloadDict valueForKey:@"rowid"] integerValue] to make sure it matches a value in the id column for one of the existing rows in your table. If it doesn't match anything, sqlite3_step will return SQLITE_DONE even if nothing was updated.

还请注意，您可能还需要确保将 id 值存储为数字值，而不是文本字符串，因为sqlite对于让您以最初指定的任何数据类型存储值都相当宽松首次插入数据时，无论表的定义方式如何)，如果将数据最初存储为文本值，我不确定是否寻找数字匹配的 WHERE 子句是否会成功.如果您使用 id 列定义(例如 id INTEGER PRIMARY KEY AUTOINCREMENT )，系统会自动为您定义值，那么这不是问题，但是如果您手动填充 id 列中，可能需要仔细检查.(通常，它在动态地将字符串解释为数字方面做得很好，但是有一些奇怪的情况是有问题的:例如，如果您在数字字段中存储的字符串值为"5,127"，则稍后再尝试要检索其数字值，sqlite将不知道在文本值"5,127"中用逗号做什么，并且会将数字值解释为5，而不是5127.)

Also note that you might also want to make sure that the id values are stored as numeric values, not text strings as sqlite is pretty lax about letting you store values in whatever data type you originally specified when you first inserted the data, regardless of how the table was defined), and I'm not sure if a WHERE clause looking for a numeric match will succeed if the data was originally stored as a text value. If you used an id column definition like id INTEGER PRIMARY KEY AUTOINCREMENT, where the system defined the values automatically for you, this isn't an issue, but if you manually populated the id column, it might be something to double check. (Generally it does a pretty good job in interpreting strings as numbers on the fly, but there are some weird situations that are problematic: For example, if you stored a string value of "5,127" in a numeric field, if you later then try to retrieve its numeric value, sqlite won't know what to do with the comma in the text value "5,127" and will interpret the numeric value as 5, not as 5127.)

sqlite3更新在iOS中不起作用

相关推荐