FMDB Query with dictionary

Question

I need to run a query that looks would look like

INSERT INTO Appointments (field1, field2, field3, ..., field30) VALUES (value1, value2, value3, ..., value30)

I have my Appointments being stored inside a Dictionary and would like to loop through that dictionary to make the keys equal the fields and the values equal the values.

I'm trying to use the executeUpdate:... withParameterDictionary:... but can't figure out how to make that work with multiple fields if I don't know the field names. The field names are being sent via JSON and instead of manually typing out 30 fields I would just like to loop through the dictionary and get them that way.

I have even tried

NSMutableArray *keys = nil;
 NSMutableArray *values = nil;

        for (NSDictionary *dict in [json objectForKey:@"data"]) {
            keys = [NSMutableArray array];
            values = [NSMutableArray array];
            for (id key in dict) {
                [keys addObject:key];
                [values addObject:[NSString stringWithFormat:@":%@", key]];
            }
            NSString *keyString = [keys componentsJoinedByString:@","];
            NSString *valueString = [values componentsJoinedByString:@","];
            [[dataObj db] executeUpdate:@"DELETE FROM Appointments"];
            NSLog(@"INSERT INTO Appointments (%@) VALUES (%@)", keyString, valueString);
            [[dataObj db] executeUpdate:@"INSERT INTO Appointments (?) VALUES (?)", keyString, valueString];

        }

The code above prints the NSLog how the query should looks but nothing is being inserted into the database. I know this because I am opening the simulator database file after the queries run and it is still blank.

How can I get the above code to work or how can I get the executeQuery:... withParameterDictionary:... to work with multiple names.

score 6 · Accepted Answer · answered Feb 23 '12 at 18:19

I ran a couple of quick tests, and this works for me:

NSDictionary* dict = [NSDictionary dictionaryWithObjectsAndKeys:@"AAAA44", @"a", @"BBBB44", @"b", @"CCCC44", @"c", nil];
NSMutableArray* cols = [[NSMutableArray alloc] init];
NSMutableArray* vals = [[NSMutableArray alloc] init];
for (id key in dict) {
    [cols addObject:key];
    [vals addObject:[dict objectForKey:key]];
}
NSMutableArray* newCols = [[NSMutableArray alloc] init];
NSMutableArray* newVals = [[NSMutableArray alloc] init];
for (int i = 0; i<[cols count]; i++) {
    [newCols addObject:[NSString stringWithFormat:@"'%@'", [cols objectAtIndex:i]]];
    [newVals addObject:[NSString stringWithFormat:@"'%@'", [vals objectAtIndex:i]]];
}
NSString* sql = [NSString stringWithFormat:@"insert into test (%@) values (%@)", [newCols componentsJoinedByString:@", "], [newVals componentsJoinedByString:@", "]];
NSLog(@"%@", sql);
BOOL updateSuccess = [db executeUpdate:sql];

The trick is to add ' to the data in the arrays.

you are correct, i did this yesterday and got it working but forgot to post. I will test out the dictionary stuff you posted. — Bot, Feb 23 '12 at 19:06
This is great. The only downside is that you are skipping all the built in escaping in FMDB. This could get injected depending on how you are getting all the parameters. — Julian, Aug 09 '12 at 06:20
I ran into similar problem, unable to figure out how to use executeQuery withParameterDictionary and autoincremented key. The method described here would work, but bypasses built in escaping, which is a bummer :( — selytch, Feb 23 '13 at 10:00

score 2 · Answer 2 · edited May 29 '13 at 07:56

2

NSDictionary *argsDict 
    = [NSDictionary dictionaryWithObjectsAndKeys:@"My Name", 
       @"name", nil];

[db executeUpdate:@"INSERT INTO myTable (name) VALUES (:name)"
    withParameterDictionary:argsDict];

edited May 29 '13 at 07:56

Taky

5,284
1
20
29

answered May 29 '13 at 07:34

yerwoo_gmail

31
2

score 0 · Answer 3 · answered Nov 28 '15 at 04:58

Here is some sample code I just wrote to support optional values at insert time. Just briefly tested but I think it works.

    NSMutableDictionary* fieldsandvalues = [NSMutableDictionary dictionary];
    fieldsandvalues[@"word"] = userphrase.word;
    fieldsandvalues[@"translation"] = userphrase.translation;
    if (userphrase.samplesentence.length > 0) {
        fieldsandvalues[@"samplesentence"] = userphrase.samplesentence;
    }
    if (userphrase.notes.length > 0) {
        fieldsandvalues[@"notes"] = userphrase.notes;
    }

    NSMutableArray* keyswithcolon = [NSMutableArray array];
    for (NSString* key in fieldsandvalues.allKeys) {
        [keyswithcolon addObject:[NSString stringWithFormat:@":%@", key]];
    }

    NSString* sql = [NSString stringWithFormat:@"INSERT INTO userphrase (%@) VALUES (%@)", [fieldsandvalues.allKeys componentsJoinedByString:@","], [keyswithcolon componentsJoinedByString:@","]];
//  DLog(@"sql: %@", sql);
    if (![self.db executeUpdate:sql withParameterDictionary:fieldsandvalues]) {
        NSAssert(NO, @"Failed inserting userphrase into database! Last error: %@ - %@", self.db.lastError, self.db.lastErrorMessage);
        return nil;
    }

FMDB Query with dictionary

3 Answers3