adding items to sqlite3 database is not working

Question

I have the following code and it is implemented based on a tutorial on youtube,, but I changed some of it to meet my purpose which is inserting items hard coded .. the database is created and also the table but I found that addItems is not adding. I don't think that there is wrong with addItemToTable method, I think it is a logical error in the first part listed here, but can not find it .Any help will be great

thanks in advance

these are the items:

    table_ok = YES;
    if (table_ok) {
            if (!db_open_status) {
                [self openDBWithSQLName:dataBaseName];
                NSLog(@"DB opened");
            }

            NSMutableDictionary *objectColsVals = [[NSMutableDictionary alloc]init];
            NSString *this_id = @"12";
            NSString *this_name = @"and";
            NSString *this_email = @"[email protected]";
            NSString *this_password = @"aa11111";
            NSString *this_role = @"Marketing";
            [objectColsVals setValue:this_id forKey:[my_columns_names objectAtIndex:0]];
            [objectColsVals setValue:this_name forKey:[my_columns_names objectAtIndex:1]];
            [objectColsVals setValue:this_email forKey:[my_columns_names objectAtIndex:2]];
            [objectColsVals setValue:this_password forKey:[my_columns_names objectAtIndex:3]];
            [objectColsVals setValue:this_role forKey:[my_columns_names objectAtIndex:4]];

            if ([[objectColsVals allKeys] count] > 0) {
                if ([self addItemToTable:tableName WithColumnValues:objectColsVals]) {
                    NSLog(@"inserted");
                    [self closeDB];
                }
              }  

This the method:

-(BOOL)addItemToTable:(NSString *)usetable WithColumnValues:(NSDictionary *)valueObject{
    BOOL has_beenAdded = NO;
    NSString *mycolumns = @"";
    NSString *myvalues = @"";
    //loop through all the value keys 
    for (int r=0; r<[[valueObject allKeys] count]; r++) {
        NSString *this_keyname = [[valueObject allKeys]objectAtIndex:r];
        mycolumns = [mycolumns stringByAppendingString:this_keyname];
        NSString *thisval = [NSString stringWithFormat:@"'%@'",[valueObject objectForKey:this_keyname]];
        myvalues = [myvalues stringByAppendingString:thisval];
        //add commas to seperate the col and val lists before last item
        if (r<(([[valueObject allKeys] count])-1)) {
            mycolumns = [mycolumns stringByAppendingString:@","];
            myvalues = [myvalues stringByAppendingString:@","];
        }
    }

    NSString *myinsert = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES(%@)",usetable,mycolumns,myvalues];
    char *err;
    if (sqlite3_exec(estate_db, [myinsert UTF8String], NULL, NULL, &err) != SQLITE_OK) {
        sqlite3_close(estate_db);
    }else{
        has_beenAdded = YES;
    }

    return has_beenAdded;
}

Answer 1

In terms of what you've got here, it's hard to say where the problem is. Nothing seems obviously wrong. At a very minimum, one should:

• examine what the resulting INSERT statement that you built programmatically, to make sure there isn't some subtle issue that eludes a cursory examination of the code;

• if any sqlite3_xxx() calls fail (notably if the sqlite3_exec returns anything besides SQLITE_OK), then log the error message (either the err variable, or by calling sqlite3_errmsg()); if you don't look at these error messages you're just flying blind; and

• run the app on the simulator and then open the simulator's copy of the database on your Mac (in the ~/Library/Application Support/iPhone Simulator directory; if the ~/Library folder is hidden, unhide it by running the with chflags -nohidden ~/Library command in the Terminal command line tool) and examine the contents of the database directly. Verify column names, table names, etc.

Again, it's unclear where the problem is, but it likely rests in something simple like some confusion when opening the database or creating the table in question. Until we confirm error messages and the actual SQL, it's hard to say. It could be anything from an attempt to open the readonly copy of the database in the bundle to erroneously calling sqlite3_open and unwittingly creating a new blank database. You really should update the question and share the code that creates the database (or copies it from the bundle), as well as doing some of the diagnostic steps outlined above.

---

Having said this, I really discourage you from adding values into your SQL with stringWithFormat. The dynamic building of the SQL is fine, but you really should not the use of stringWithFormat to insert the values into the SQL, itself. Given that you're quoting the text values with single quotes, what if the person's last name was O'Brian? Or if you changed your routine to use double quotes, what if the person's name was Dwayne "The Rock" Johnson? The current code may fail if the string delimiter occurs in the data value. Worse, you technically expose yourself to SQL injection attacks.

What you should generally do is to use ? placeholders. For example, consider a dictionary defined as follows:

NSDictionary *dataToInsert = @{@"name"        : @"Jack",
                               @"id"          : @37,
                               @"password"    : @"feefifofum",
                               @"role"        : [NSNull null],
                               @"email"       : @"[email protected]",
                               @"hourly_wage" : @12.85};

What you want to do is to build a SQL statement that looks like the following:

INSERT INTO test (name,id,password,role,email,hourly_wage) VALUES (?,?,?,?,?,?)

You then want to bind the values to those ? placeholders using the sqlite3_bind_xxx() functions.

So, you can create and prepare that SQL statement (building an array of values and an array of placeholders) like so:

NSArray *keys = [dataToInsert allKeys];
NSMutableArray *values = [NSMutableArray arrayWithCapacity:[keys count]];
NSMutableArray *placeholders = [NSMutableArray arrayWithCapacity:[keys count]];

// build array of values and array of question mark placeholders

for (NSString *key in keys) {
    [values addObject:[dataToInsert objectForKey:key]];
    [placeholders addObject:@"?"];
}

// use the `keys` and `placeholders` arrays to build the SQL

NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES (%@)",
                       tableName,
                       [keys componentsJoinedByString:@","],
                       [placeholders componentsJoinedByString:@","]];

if (sqlite3_prepare_v2(db, [insertSql UTF8String], -1, &statement, NULL) != SQLITE_OK) {
    NSLog(@"prepare failed: %s", sqlite3_errmsg(db));
    sqlite3_close(db);
    return;
}

// statement is prepared, but we still have to bind the values...

You can then bind the values with something like the following. This is doing dynamic checking of the class of the objects in the values array (and if it is a NSNumber, look at the objCType to determine the type of number):

// now use the `values` array to bind values to the ? placeholders

[values enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop) {
    int rc = SQLITE_ERROR;

    if ([obj isKindOfClass:[NSString class]])
        rc = sqlite3_bind_text(statement, idx + 1, [obj UTF8String], -1, SQLITE_TRANSIENT);
    else if ([obj isKindOfClass:[NSNull class]])
        rc = sqlite3_bind_null(statement, idx + 1);
    else if ([obj isKindOfClass:[NSNumber class]]) {
        const char *objCType = [obj objCType];

        if (strcmp(objCType, @encode(int)) == 0 || strcmp(objCType, @encode(unsigned int)) == 0 || strcmp(objCType, @encode(short)) == 0 || strcmp(objCType, @encode(unsigned short)) == 0 || strcmp(objCType, @encode(char)) == 0 || strcmp(objCType, @encode(unsigned char)) == 0)
            rc = sqlite3_bind_int(statement, idx + 1, [obj integerValue]);
        else if (strcmp(objCType, @encode(long)) == 0 || strcmp(objCType, @encode(unsigned long)) == 0 || strcmp(objCType, @encode(long long)) == 0 || strcmp(objCType, @encode(unsigned long long)) == 0)
            rc = sqlite3_bind_int64(statement, idx + 1, [obj longLongValue]);
        else if (strcmp(objCType, @encode(float)) == 0 || strcmp(objCType, @encode(double)) == 0)
            rc = sqlite3_bind_double(statement, idx + 1, [obj doubleValue]);
        else {
            NSLog(@"column %d is %@ but has unknown numeric type %s; will use `description`", idx + 1, obj, objCType);
            rc = sqlite3_bind_text(statement, idx + 1, [[obj description] UTF8String], -1, SQLITE_TRANSIENT);
        }
    }
    else
        rc = sqlite3_bind_text(statement, idx + 1, [[obj description] UTF8String], -1, SQLITE_TRANSIENT);

    if (rc != SQLITE_OK)
    {
        NSLog(@"bind %d failed: %s", idx + 1, sqlite3_errmsg(db));
        sqlite3_finalize(statement);
        sqlite3_close(db);
        return;
    }
}];

if (sqlite3_step(statement) != SQLITE_DONE) {
    NSLog(@"step failed: %s", sqlite3_errmsg(db));
    sqlite3_close(db);
    return;
}

sqlite3_finalize(statement);