iOS - Copying NSDictionary to SQLite3

Question

I have a method that receives a table name and an NSDictionary. In that method I want a loop to create an SQL Insert statement for each record that will copy the contents of the NSDictionary into the corresponding table. The NSDictionary was populated via a JSON web service in a previous method.

The problem I am having is getting the information out of the NSDictionary and into the Insert statement in the correct format.

What I want is for every record to have an Insert statement similar to this:

INSERT INTO speaker VALUES (0f32ae3f-ad56-e311-a863-000c29beef51,<null>,1385478793, Mike Smith,<null>,BVSc PhD DSAM DipECVIM-ca MRCVS)

But what I am getting is:

INSERT INTO speaker VALUES (
   {
        ID = "0f32ae3f-ad56-e311-a863-000c29beef51";
        ImageURL = "<null>";
        LastMod = 1385478793;
        Name = "Mike Smith";
        Profile = "<null>";
        Qualifications = "BVSc PhD DSAM DipECVIM-ca MRCVS";
   }
)  

Here is my code so far:

-(void)populateTable:(NSString *)tableName dictonary:(NSMutableDictionary *)tempDict
{

    sqlite3_stmt *stmt;

    NSMutableString *keys = [NSMutableString string];
    NSMutableString *values = [NSMutableString string];

    for (NSString *key in tempDict.allKeys) { //edited from tempDict to tempDict.allKeys
        [keys appendFormat:@"%@,", key];
        [values appendFormat:@"%@,", [tempDict objectForKey:key]];
    }

    [values deleteCharactersInRange:NSMakeRange([values length] -1, 1)];
    NSString *queryStr = [NSString stringWithFormat:@"INSERT INTO %@ VALUES %@", tableName, values];
    NSLog(@"Query = %@", queryStr);
    NSLog(@"GetData   -   populateTable   -   qry : %@",queryStr);
    const char *sql = [queryStr UTF8String]; ;

    if((sqlite3_open([[self filePath] UTF8String], &congressDB)==SQLITE_OK))
    {
        NSLog(@"GetData   -   populateTable   -   str = %@", queryStr);
        if (sqlite3_prepare(congressDB, sql, -1, &stmt, NULL)==SQLITE_OK)
        {
            NSLog(@"GetData   -   populatTable   -   About to carry out SQL statement");
            sqlite3_step(stmt);
            sqlite3_finalize(stmt);
        }
        else
        {
            NSLog(@"GetData   -   populateTable   -   Problem with prepare statement: %s", sqlite3_errmsg(congressDB));
            NSLog(@"GetData   -   populateTable   -   stmt = %@", stmt);
        }
        sqlite3_close(congressDB);

    }

}

Answer 1

Try this correction:

- (void)populateTable:(NSString *)tableName dictonary:(NSMutableDictionary *)tempDict
{

    sqlite3_stmt *stmt;

    NSMutableString *values = [NSMutableString string];

    for (NSString *key in tempDict.allKeys)
    {
        [values setString:@""];
        NSArray *array = [tempDict objectForKey:key];
        if (array.count)
        {
            NSDictionary * dict = [array objectAtIndex:0];
            for (NSString *keyInDict in dict.allKeys)
            {
                [values appendFormat:@"%@,", [dict objectForKey:keyInDict]];
            }

            [values deleteCharactersInRange:NSMakeRange([values length] -1, 1)];
            NSString *queryStr = [NSString stringWithFormat:@"INSERT INTO %@ VALUES %@", tableName, values];
            NSLog(@"Query = %@", queryStr);
            NSLog(@"GetData   -   populateTable   -   qry : %@",queryStr);
            const char *sql = [queryStr UTF8String]; ;

            if((sqlite3_open([[self filePath] UTF8String], &congressDB)==SQLITE_OK))
            {
                NSLog(@"GetData   -   populateTable   -   str = %@", queryStr);
                if (sqlite3_prepare(congressDB, sql, -1, &stmt, NULL)==SQLITE_OK)
                {
                    NSLog(@"GetData   -   populatTable   -   About to carry out SQL statement");
                    sqlite3_step(stmt);
                    sqlite3_finalize(stmt);
                }
                else
                {
                    NSLog(@"GetData   -   populateTable   -   Problem with prepare statement: %s", sqlite3_errmsg(congressDB));
                    NSLog(@"GetData   -   populateTable   -   stmt = %@", stmt);
                }
                sqlite3_close(congressDB);
            }
        }
    }
}