Make arrays from json and save arrays in columns of SQLite in objective C iOS

Question

I am retrieving data from my json file and it contains the timings for sunrise, sunset and midnight. I want to make array for sunrise , array for sunset and array for midnight. and save my arrays in sunriseColumn , sunsetColumn and midnightColumn in SQLite.

But I am little bit confused how can I do that in a proper way.

What I have in my ViewDidLoad()

- (void)viewDidLoad {
[super viewDidLoad];

[self saveinDatabase];

NSURL *url = [NSURL fileURLWithPath:@"/Users/macbook/Desktop/Test/Test/myFile.json"];
NSString *fileContent = [NSString stringWithContentsOfURL:url encoding:NSUTF8StringEncoding error:nil];
NSLog(@"Json data is here %@", fileContent);

saving data in dictionary

NSArray *data = [[theFeedString JSONValue] objectForKey:@"data"];
for (NSDictionray *dict in data) {
NSString *timings = [[dict objectForKey:@"timings"] intValue];
NSArray *sunriseArray = [objectForKey:@"sunrise"]
NSArray *sunsetArray = [objectForKey:@"sunset"]
NSArray *midnight = [objectForKey:@"midnight"]
}

in my console I get all the data from my json and my json look like this

{
"data": [
{
"timings": {
"Sunrise": "07:14 (PKT)",
"Sunset": "18:15 (PKT)",
"Midnight": "00:45 (PKT)"
}
},
{
"timings": {
"Sunrise": "07:13 (PKT)",
"Sunset": "06:40 (PKT)",
"Midnight": "00:45 (PKT)"
}
}
]
}

a method for storing data in SQLite

-(void) saveinDatabase {

NSString *docsDir;
NSArray *dirPaths;

dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, TRUE);
docsDir = dirPaths[0];

_dbPath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:@"mydatabase.db"]];
NSFileManager *fileManager = [NSFileManager defaultManager];
if([fileManager fileExistsAtPath:_dbPath] == NO) {

    const char *dbPathagain = [_dbPath UTF8String];

    if(sqlite3_open(dbPathagain, &_DB) == SQLITE_OK ) {
        char *errorMessage;
        const char *sql_statement = "CREATE TABLE IF NOT EXISTS jsontable(ID integer primary key, sunrise text, sunset text, midnight text)";

        NSLog(@"created table success");

        sqlite3_stmt *statement;
        const char *dbPathagain = [ _dbPath UTF8String];

        if(sqlite3_open(dbPathagain, &_DB) == SQLITE_OK ) {
            NSString *insertSQLData = [NSString stringWithFormat:@"INSERT INTO jsontable(sunrise, sunset, midnight) VALUES (\"%@\", \"%@\", \"%@\")", sunriseArray, sunsetArray, midnightArray];
            const char *insert_statement = [insertSQLData UTF8String];
            sqlite3_prepare_v2(_DB, insert_statement, -1, &statement, NULL);

            if (sqlite3_step(statement) == SQLITE_DONE) {
                NSLog(@"data added successfully");
                NSLog(@"here is sunrise times %@", sunriseArray);
            }
            else {
                NSLog(@"could not add timings");
            }
            sqlite3_finalize(statement);
            sqlite3_close(_DB);
        }

        if (sqlite3_exec(_DB, sql_statement, NULL, NULL, &errorMessage) != SQLITE_OK) {
            NSLog(@"failed to create table");
        }
        sqlite3_close(_DB);
    }
    else {
        NSLog(@"failed to open db or cretate table");
    }
}
}
}

Answer 1

Make following changes in you code:

Pass your "data" array to method.

 NSDictionary *dict = @{
                               @"data": @[
                                       @{
                                           @"timings": @{
                                                   @"Sunrise": @"07:14 (PKT)",
                                                   @"Sunset": @"18:15 (PKT)",
                                                   @"Midnight": @"00:45 (PKT)"
                                                   }
                                           },
                                       @{
                                           @"timings": @{
                                                   @"Sunrise": @"07:13 (PKT)",
                                                   @"Sunset": @"06:40 (PKT)",
                                                   @"Midnight": @"00:45 (PKT)"
                                                   }
                                           }
                                       ]
                               };
     NSArray *arrData = [NSArray arrayWithArray:[dict objectForKey:@"data"]];
    [self saveinDatabase:arrData];

Replace method with following code:

-(void) saveinDatabase:(NSArray*) arrData {

    NSString *docsDir;
    NSArray *dirPaths;

    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, TRUE);
    docsDir = dirPaths[0];

    _dbPath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:@"mydatabase.db"]];
    NSFileManager *fileManager = [NSFileManager defaultManager];
    if([fileManager fileExistsAtPath:_dbPath] == NO) {

        const char *dbPathagain = [_dbPath UTF8String];

        if(sqlite3_open(dbPathagain, &_DB) == SQLITE_OK ) {
            char *errorMessage;
            const char *sql_statement = "CREATE TABLE IF NOT EXISTS jsontable(ID integer primary key, sunrise text, sunset text, midnight text)";

            NSLog(@"created table success");

            sqlite3_stmt *statement;
            const char *dbPathagain = [ _dbPath UTF8String];

            if(sqlite3_open(dbPathagain, &_DB) == SQLITE_OK ) {


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

                    NSDictionary *dictTiming = [[arrData objectAtIndex:i] valueForKey:@"timings"];
                    NSString *insertSQLData = [NSString stringWithFormat:@"INSERT INTO jsontable(sunrise, sunset, midnight) VALUES (\"%@\", \"%@\", \"%@\")", [dictTiming objectForKey:@"Sunrise"], [dictTiming objectForKey:@"Sunset"], [dictTiming objectForKey:@"Midnight"]];
                    const char *insert_statement = [insertSQLData UTF8String];
                    sqlite3_prepare_v2(_DB, insert_statement, -1, &statement, NULL);

                    if (sqlite3_step(statement) == SQLITE_DONE) {
                        NSLog(@"data added successfully");
                        NSLog(@"here is sunrise times %@", sunriseArray);
                    }
                    else {
                        NSLog(@"could not add timings");
                    }

                }


                sqlite3_finalize(statement);
                sqlite3_close(_DB);
            }

            if (sqlite3_exec(_DB, sql_statement, NULL, NULL, &errorMessage) != SQLITE_OK) {
                NSLog(@"failed to create table");
            }
            sqlite3_close(_DB);
        }
        else {
            NSLog(@"failed to open db or cretate table");
        }
    }
}