Why is this only inserting the first values from the array?

Question

I'm attempting to insert data stored in an NSMutableArray and store it in an SQLite Database.. So far I've been only able to insert the first 5 elements of the array or insert them all into the first column of the DB. I can't figure out where it's breaking/not working.

Contents of lockInfo (NSMutableArray):

LOCKINFO (
    51974,
    0,
    2,
    15,
    1,
    51975,
    0,
    24,
    15,
    1,
    51976,
    0,
    20,
    15,
    1,
    51977,
    0,
    23,
    15,
    1
    )

All that's being inserted into the first row is:

51974,
    0,
    2,
    15,
    1

Code:

-(void)insertLock:(NSMutableArray *)lockInfo{
    char *error;
    NSString *databasePath = [self dataPath:@"eloqdb.sqlite3"];
    sqlite3 *database;
    if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {
        sqlite3_close(database);
        NSLog(@"Could not open database");
    }else{
        NSLog(@"DB opened");
    }

    //for each element in the array, save the array index, and the contact:

     NSLog(@"count %@", lockInfo);

    NSLog(@"count %i", lockInfo.count);

    for (int i = 0; i < lockInfo.count; i++) {


        sqlite3_stmt *statement;
        NSString *SQLInsert = @"INSERT OR REPLACE INTO LOCK_PLAN_CHANGE (ID, FLAG, ITEM_DOOR_ID, KEY_SERIAL, TIMEZONE_ITEM_ID) "
        @"VALUES (?, ?, ?, ?, ?);";
        if (sqlite3_prepare_v2(database, [SQLInsert UTF8String], -1,
                               &statement, nil) == SQLITE_OK)
        {
            sqlite3_bind_text(statement, 1, [[lockInfo objectAtIndex:i] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 2, [[lockInfo objectAtIndex:i] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 3, [[lockInfo objectAtIndex:i] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 4, [[lockInfo objectAtIndex:3] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 5, [[lockInfo objectAtIndex:4] UTF8String], -1, NULL);
        }
        if (sqlite3_step(statement) != SQLITE_DONE) {
            // NSAssert1(0, @"Cannot Update Table", error);
        }
        sqlite3_finalize(statement);
    }
    sqlite3_close(database);
}

Answer 1

Your array actually contains lockInfo.count / 5 rows worth of data.

Change your loop to:

for (int i = 0; i < lockInfo.count; i += 5) {

Then update the binds to:

sqlite3_bind_text(statement, 1, [[lockInfo objectAtIndex:i] UTF8String], -1, NULL);
sqlite3_bind_text(statement, 2, [[lockInfo objectAtIndex:i+1] UTF8String], -1, NULL);
sqlite3_bind_text(statement, 3, [[lockInfo objectAtIndex:i+2] UTF8String], -1, NULL);
sqlite3_bind_text(statement, 4, [[lockInfo objectAtIndex:i+3] UTF8String], -1, NULL);
sqlite3_bind_text(statement, 5, [[lockInfo objectAtIndex:i+4] UTF8String], -1, NULL);

Those changes will put the correct values in the correct columns and it will add the correct number of rows.

BTW - this is a really poor data structure. You should define a class or struct with 5 properties. Then have an array of that class or struct.

Besides that main issue, you also don't make proper use of a prepared statement. You should prepare the statement just once before the loop.

Here's your code written properly with better error checking as well:

- (void)insertLock:(NSMutableArray *)lockInfo {
    NSString *databasePath = [self dataPath:@"eloqdb.sqlite3"];
    sqlite3 *database;
    if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {
        NSLog(@"Could not open database");
        return;
    } else {
        NSLog(@"DB opened");
    }

    //for each element in the array, save the array index, and the contact:

    NSLog(@"lockInfo %@", lockInfo);
    NSLog(@"count %i", lockInfo.count);

    sqlite3_stmt *statement;
    NSString *SQLInsert = @"INSERT OR REPLACE INTO LOCK_PLAN_CHANGE (ID, FLAG, ITEM_DOOR_ID, KEY_SERIAL, TIMEZONE_ITEM_ID) VALUES (?, ?, ?, ?, ?);";
    if (sqlite3_prepare_v2(database, [SQLInsert UTF8String], -1, &statement, nil) == SQLITE_OK) {
        for (NSInteger i = 0; i < lockInfo.count; i += 5) {
            sqlite3_bind_text(statement, 1, [lockInfo[i] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 2, [lockInfo[i+1] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 3, [lockInfo[i+2] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 4, [lockInfo[i+3] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 5, [lockInfo[i+4] UTF8String], -1, NULL);

            if (sqlite3_step(statement) != SQLITE_DONE) {
                // NSAssert1(0, @"Cannot Update Table", error);
            }
            sqlite3_reset(statement);
        }
        sqlite3_finalize(statement);
    } else {
        NSLog(@"Can't prepare: %s", sqlite3_errmsg(database));
    }
    sqlite3_close(database);
}