CODEWITHSUNDEEP
objective-cios5sqlitefmdb
Bot
Bot

Reputation: 11855

FMDB Query with dictionary

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.

Upvotes: 4

Views: 4443

Answers (3)

Jonny
Jonny

Reputation: 16308

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;
    }

Upvotes: 0

yerwoo_gmail
yerwoo_gmail

Reputation: 31

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

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

Upvotes: 2

lawicko
lawicko

Reputation: 7344

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.

Upvotes: 6

Related Questions