sqlite3 int problem

Question

I've completely given up on this, so if a moderator happens to come by, deleting would be great.
It doesn't hurt to leave this question up, but CoreData is so much nicer, you know?

---

I have a sqlite database for handling the contents of a table. It's great and everything (much easier than other options I looked at), but I'm having trouble with ints. The first time I go to edit an item after launching the app, the field for the int is empty. Re-entering works fine, it saves and appears in the table, but the next edit (without reopening the app) sets the second item's int to that of the first.

i.e., A(1) resets to A(0). I fix it (A(1)), but then B(2) becomes B(1) as soon as I load the edit view. Fix it (B(2)) or not (B(1)), C(3) will then have the same (#) as B.

I still can't figure out what's causing it. Changing the int to a string (edit database column and every relevant file in the app) would certainly work, but that's a whole lot of unnecessary work just to make it slower and easier to break.

edit:

CREATE TABLE "items" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" VARCHAR, "need" INTEGER DEFAULT 0, "notes" TEXT)

- (void)updateItemAtIndexPath:(NSIndexPath *)path {
    Item *i = (Item *)[items objectAtIndex:path.row];
    int ret;
    const char *sql = "update items set name = ?, need = ?, notes = ? where id = ?;";

    if (!updStmt) { // build update statement
        if ((ret = sqlite3_prepare_v2(database, sql, -1, &updStmt, NULL)) != SQLITE_OK) {
            NSAssert1(0, @"Error building statement to update items [%s]", sqlite3_errmsg(database));
        }
    }

    // bind values to statement
    NSString *s = i.name;
    if (s == NULL) s = @"";
    sqlite3_bind_text(updStmt, 1, [s UTF8String], -1, SQLITE_TRANSIENT);
    NSInteger n = i.need;
    sqlite3_bind_int(updStmt, 2, n);
    s = i.notes;
    if (s == NULL) s = @"";
    sqlite3_bind_text(updStmt, 3, [s UTF8String], -1, SQLITE_TRANSIENT);
    n = i.itemid;
    sqlite3_bind_int(updStmt, 4, n);

    // now execute sql statement
    if (sqlite3_step(updStmt) != SQLITE_DONE) {
        NSAssert1(0, @"Error updating values [%s]", sqlite3_errmsg(database));
    }

    // now reset bound statement to original state
    sqlite3_reset(updStmt);
}

- (void)insertItem:(Item *)item {
    int ret;
    const char *sql = "insert into items (name, need, notes) values (?, ?, ?);";

    if (!insStmt) { // first insert - build statement
        if ((ret = sqlite3_prepare_v2(database, sql, -1, &insStmt, NULL)) != SQLITE_OK) {
            NSAssert1(0, @"Error building statement to insert item [%s]", sqlite3_errmsg(database));
        }
    }

    // bind values
    NSString *s = item.name;
    if (s == NULL) s = @"";
    sqlite3_bind_text(insStmt, 1, [s UTF8String], -1, SQLITE_TRANSIENT);
    NSInteger n = item.need;
    sqlite3_bind_int(insStmt, 2, n);
    s = item.notes;
    if (s == NULL) s = @"";
    sqlite3_bind_text(insStmt, 3, [s UTF8String], -1, SQLITE_TRANSIENT);

    // execute sql statement
    if (sqlite3_step(insStmt) != SQLITE_DONE) {
        NSAssert1(0, @"Error inserting item [%s]", sqlite3_errmsg(database));
    }

    // reset bound statement to original state
    sqlite3_reset(insStmt);

    [self readItems]; // refresh array
}

Answer 1

Instead of using sqlite3_bind_text and sqlite3_bind_int, I would try to construct the query string from the various values and use sqlite3_exec to run it. Let's call that a tryout towards a solution.

Example (warning, untested!!):

- (void)updateItemAtIndexPath:(NSIndexPath *)path {
    Item *i = (Item *)[items objectAtIndex:path.row];

    // validate values
    NSString *name = i.name;
    if (name == NULL) name = @"";
    [name stringByReplacingOccurrencesOfString:@"'"
                                    withString:@"''"];
    NSInteger need = i.need;
    NSString *notes = i.notes;
    if (notes == NULL) notes = @"";
    [notes stringByReplacingOccurrencesOfString:@"'"
                                     withString:@"''"];
    NSInteger itemid = i.itemid;

    NSString *sql = [NSString stringWithFormat: 
                    @"update items set name = '%@', need = %@, notes = '%@' where id = %@;", 
                    name, need, notes, itemid];

    // now execute sql statement
    if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, NULL) != SQLITE_DONE) {
        NSAssert1(0, @"Error updating values [%s]", sqlite3_errmsg(database));
    }
}