Sqlite3 strange step 21 iOs

Question

I have a strange problem with the sqlite3. My app has a db and I have a problem only in one table and only in one method connected with this table. The table is:

#define CONTACTS_TABLE "CREATE TABLE IF NOT EXISTS `contacts` (`_id` INTEGER PRIMARY KEY AUTOINCREMENT, `status` VARCHAR , `name` VARCHAR , `group_id` BIGINT , `avatator` INTEGER , `remoteId` BIGINT , `blacklist` INTEGER , `ignore` INTEGER)"

The table already has a lot of data, but when I try to update it, it shows me sqlite step 21, but the data updates (!!!).

- (BOOL)updateContactsWithContact:(ContactData *)myItem
{
    const char *dbPath = [dataBasePath UTF8String];
    if (sqlite3_open(dbPath, &database) == SQLITE_OK) {
       // NSString *insertSqlStatement = [NSString stringWithFormat: @"UPDATE contacts SET status = ?, name = ?, avatator = ?, blacklist = ?, ignore = ? WHERE remoteId = ?"];
        NSString *insertSqlStatement = [NSString stringWithFormat: @"UPDATE contacts SET status = ?, name = ? WHERE remoteId = ?"];

        NSLog(@"item for db with name: %@ with status: %@ with isAvatator: %d with group_id : %d with isBlacklist: %d with is ignore: %d with remoteId: %d", myItem.contactName, myItem.status, myItem.isAvatator, myItem.groupId, myItem.isBlackList, myItem.isIgnore, myItem.remoteId);

        const char *insertStmt = [insertSqlStatement UTF8String];
        if  (sqlite3_prepare_v2(database, insertStmt, -1, &sqlStatement, NULL) == SQLITE_OK) {

            sqlite3_bind_text(sqlStatement, 1, [myItem.status UTF8String], -1, nil);
            sqlite3_bind_text(sqlStatement, 2, [myItem.contactName UTF8String], -1, nil);
            //sqlite3_bind_int(sqlStatement, 3, [self convertBOOLToInt:myItem.isAvatator]);
            //sqlite3_bind_int(sqlStatement, 4, [self convertBOOLToInt:myItem.isBlackList]);
            //sqlite3_bind_int(sqlStatement, 5, [self convertBOOLToInt:myItem.isIgnore]);
            sqlite3_bind_int(sqlStatement, 3, myItem.remoteId);

            NSLog(@"Step updateContactsWithRemoteId: %d\n", sqlite3_step(sqlStatement));
            if (sqlite3_step(sqlStatement) == SQLITE_DONE)
            {
                NSLog(@"updated contact from updateContactWithContactData");
                sqlite3_finalize(sqlStatement);
                sqlite3_close(database);
                return YES;
            } else {
                NSLog(@"Not updated contact from updateContactWithContactData");
                NSLog(@"Step: %d\n", sqlite3_step(sqlStatement));
                sqlite3_finalize(sqlStatement);
                sqlite3_close(database);
                return NO;
            }
            sqlite3_finalize(sqlStatement);
        } else {
            NSLog(@"updateContactWithContactData is wrong statement");
        }
    }
    sqlite3_close(database);
    return NO;
}

For example, such a method updates THIS table correctly:

- (BOOL)updateContactsWithRemoteId:(NSInteger)remoteId withAvatatorValue:(BOOL)isAvatator
{
    const char *dbPath = [dataBasePath UTF8String];
    if (sqlite3_open(dbPath, &database) == SQLITE_OK) {
        NSString *insertSqlStatement = [NSString stringWithFormat: @"UPDATE contacts SET avatator = ? WHERE remoteId = ?"];

        const char *insertStmt = [insertSqlStatement UTF8String];
        if  (sqlite3_prepare_v2(database, insertStmt, -1, &sqlStatement, NULL) == SQLITE_OK) {

            sqlite3_bind_int(sqlStatement, 1, [self convertBOOLToInt:isAvatator]);
            sqlite3_bind_int(sqlStatement, 2, remoteId);
            NSLog(@"Step updateContactsWithRemoteId: %d\n", sqlite3_step(sqlStatement));
            if (sqlite3_step(sqlStatement) == SQLITE_DONE)
            {
                NSLog(@"updated contact from updateContactWithContactData");
                sqlite3_finalize(sqlStatement);
                sqlite3_close(database);
                return YES;
            } else {
                NSLog(@"Not updated contact from updateContactWithContactData");
                NSLog(@"Step: %d\n", sqlite3_step(sqlStatement));
                sqlite3_finalize(sqlStatement);
                sqlite3_close(database);
                return NO;
            }
            sqlite3_finalize(sqlStatement);
        } else {
            NSLog(@"updateContactWithContactData is wrong statement");
        }
    }
    sqlite3_close(database);
    return NO;
}

I can't find what is incorrect in my first method. I've tried all the combinations of update statement in this method, but the result is always the same. Please, help me, people. Thanks in advance.

Answer 1

You must only call sqlite3_step once per "update" or "insert" query. As written you call it at least twice, perhaps 3 times. Why? You also need to do better error checking. Try this:

- (BOOL)updateContactsWithContact:(ContactData *)myItem {
    BOOL ok = YES;
    const char *dbPath = [dataBasePath UTF8String];
    if (sqlite3_open(dbPath, &database) == SQLITE_OK) {
       // NSString *insertSqlStatement = [NSString stringWithFormat: @"UPDATE contacts SET status = ?, name = ?, avatator = ?, blacklist = ?, ignore = ? WHERE remoteId = ?"];
        NSString *insertSqlStatement = [NSString stringWithFormat: @"UPDATE contacts SET status = ?, name = ? WHERE remoteId = ?"];

        NSLog(@"item for db with name: %@ with status: %@ with isAvatator: %d with group_id : %d with isBlacklist: %d with is ignore: %d with remoteId: %d", myItem.contactName, myItem.status, myItem.isAvatator, myItem.groupId, myItem.isBlackList, myItem.isIgnore, myItem.remoteId);

        const char *insertStmt = [insertSqlStatement UTF8String];
        int res;
        if  ((res = sqlite3_prepare_v2(database, insertStmt, -1, &sqlStatement, NULL)) == SQLITE_OK) {

            sqlite3_bind_text(sqlStatement, 1, [myItem.status UTF8String], -1, nil);
            sqlite3_bind_text(sqlStatement, 2, [myItem.contactName UTF8String], -1, nil);
            //sqlite3_bind_int(sqlStatement, 3, [self convertBOOLToInt:myItem.isAvatator]);
            //sqlite3_bind_int(sqlStatement, 4, [self convertBOOLToInt:myItem.isBlackList]);
            //sqlite3_bind_int(sqlStatement, 5, [self convertBOOLToInt:myItem.isIgnore]);
            sqlite3_bind_int(sqlStatement, 3, myItem.remoteId);

            if ((res = sqlite3_step(sqlStatement)) == SQLITE_DONE) {
                NSLog(@"updated contact from updateContactWithContactData");
            } else {
                NSLog(@"Not updated contact from updateContactWithContactData");
                NSLog(@"Step: %d\n", res, sqlite3_errmsg(database));
                ok = NO;
            }
            sqlite3_finalize(sqlStatement);
        } else {
            NSLog(@"updateContactWithContactData is wrong statement: %d - %s", res, sqlite3_errmsg(database));
            ok = NO;
        }

        sqlite3_close(database);
    } else {
        NSLog(@"Unable to open database: %s", sqlite3_errmsg(database));
        ok = NO;
    }

    return ok;
}