Objective-C: Insert a batch of records in SQLite

NSString* statement;
    statement = @"BEGIN EXCLUSIVE TRANSACTION";
    sqlite3_stmt *beginStatement;
    if (sqlite3_prepare_v2(database, [statement UTF8String], -1, &beginStatement, NULL) != SQLITE_OK) {
        printf("db error: %s\n", sqlite3_errmsg(database)); 
        return;
    }
    if (sqlite3_step(beginStatement) != SQLITE_DONE) {
        sqlite3_finalize(beginStatement);
        printf("db error: %s\n", sqlite3_errmsg(database)); 
        return;
    }

    NSTimeInterval timestampB = [[NSDate date] timeIntervalSince1970];
    statement = @"INSERT OR REPLACE INTO item (hash, tag, owner, timestamp, dictionary) VALUES (?, ?, ?, ?, ?)";
    sqlite3_stmt *compiledStatement;
    if(sqlite3_prepare_v2(database, [statement UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK)
    {
        for(int i = 0; i < [items count]; i++){
            NSMutableDictionary* item = [items objectAtIndex:i];
            NSString* tag       = [item objectForKey:@"id"];
            NSInteger hash      = [[NSString stringWithFormat:@"%@%@", tag, ownerID] hash];
            NSInteger timestamp = [[item objectForKey:@"updated"] intValue];
            NSData *dictionary  = [NSKeyedArchiver archivedDataWithRootObject:item];

            sqlite3_bind_int(   compiledStatement, 1, hash);
            sqlite3_bind_text(  compiledStatement, 2, [tag UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(  compiledStatement, 3, [ownerID UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(   compiledStatement, 4, timestamp);
            sqlite3_bind_blob(  compiledStatement, 5, [dictionary bytes], [dictionary length], SQLITE_TRANSIENT);

            while(YES){
                NSInteger result = sqlite3_step(compiledStatement);
                if(result == SQLITE_DONE){
                    break;
                }
                else if(result != SQLITE_BUSY){
                    printf("db error: %s\n", sqlite3_errmsg(database)); 
                    break;
                }
            }
            sqlite3_reset(compiledStatement);
        }
        timestampB = [[NSDate date] timeIntervalSince1970] - timestampB;
        NSLog(@"Insert Time Taken: %f",timestampB);

        // COMMIT
        statement = @"COMMIT TRANSACTION";
        sqlite3_stmt *commitStatement;
        if (sqlite3_prepare_v2(database, [statement UTF8String], -1, &commitStatement, NULL) != SQLITE_OK) {
            printf("db error: %s\n", sqlite3_errmsg(database)); 
        }
        if (sqlite3_step(commitStatement) != SQLITE_DONE) {
            printf("db error: %s\n", sqlite3_errmsg(database)); 
        }

        sqlite3_finalize(beginStatement);
        sqlite3_finalize(compiledStatement);
        sqlite3_finalize(commitStatement);

One Response to Objective-C: Insert a batch of records in SQLite

  1. pooja p says:

    Upvoting.. code Worked for me..

Leave a Reply to pooja p Cancel reply