Monday 6 January 2014

How to Get Data , insert data and Delete data in SQLite

-(void)GetDataFromDB{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    NSString *dbPath = [documentsDir stringByAppendingPathComponent:@"RideRecord.sqlite"];/// Table Name
    
    NSMutableArray *arrayTemp = [[NSMutableArray alloc]init];
    
    sqlite3 *database2;
    
    if (sqlite3_open([dbPath UTF8String], &database2) == SQLITE_OK) {
        
        const char *sqlStatement3 = "select * from NewRideTable";//Table Name
        
        sqlite3_stmt *compileStatement;
        
        if (sqlite3_prepare_v2(database2, sqlStatement3, -1, &compileStatement, NULL) == SQLITE_OK) {
            
            
            while (sqlite3_step(compileStatement) == SQLITE_ROW) {
                
                NSLog(@"one record");
                
                NSMutableDictionary *dict = [[NSMutableDictionary alloc]init];
                [dict setObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(compileStatement,0)] forKey:@"TimeStamp"];
                [dict setObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(compileStatement,1)] forKey:@"Speed"];
                [dict setObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(compileStatement,2)] forKey:@"Distance"];
                [dict setObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(compileStatement,3)] forKey:@"LatitudeDegrees"];
                [dict setObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(compileStatement,4)] forKey:@"LongitudeDegrees"];
                [dict setObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(compileStatement,5)] forKey:@"Altitude"];
                [dict setObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(compileStatement,6)] forKey:@"Temperature"];
                [dict setObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(compileStatement,7)] forKey:@"HeartRate"];
                [dict setObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(compileStatement,8)] forKey:@"Cadence"];
                [dict setObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(compileStatement,9)] forKey:@"Powers"];
                [dict setObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(compileStatement,10)] forKey:@"PointOrder"];

                [arrayTemp addObject:dict];
            }

            [dictRides setObject:arrayTemp forKey:@"Ridedata"];
            [dictRides retain];
            
            NSData *jsonData = [NSJSONSerialization dataWithJSONObject:dictRides
                                                               options:0
                                                                 error:nil];
            
            if (!jsonData) {
                NSLog(@"Error");
            } else {
                
                strRidesJson = [[NSString alloc] initWithBytes:[jsonData  bytes] length:[jsonData length] encoding:NSUTF8StringEncoding];
                [strRidesJson retain];
                NSLog(@"\n\n JSON String ==>> %@",strRidesJson);
                
            }
        }
    } else {

        NSLog(@"error in database");
    }
    sqlite3_close(database2);
}
-(void)InsertRideData{
    NSDate *date = [[NSDate alloc]init];
    strTimeStamp = [[NSString stringWithFormat:@"%@",date]retain];
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    NSString *dbPath = [documentsDir stringByAppendingPathComponent:@"RideRecord.sqlite"];
    
    sqlite3 *database2;
    
    if (sqlite3_open([dbPath UTF8String], &database2) == SQLITE_OK) {
        static sqlite3_stmt *compiledStatement = nil;
//        NSString *insertSQL = @"INSERT INTO NewRideTable (TimeStamp,Speed,Distance,LatitudeDegrees,LongitudeDegrees,Altitude,Temperature,HeartRate,Cadence,Powers,PointOrder) VALUES ('2013-11-20 13:27:52 +0000', '5.169240','20','22.996843','72.498082','72.918686','8','67','20','200','72.498082','1')";
        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO NewRideTable (TimeStamp,Speed,Distance,LatitudeDegrees,LongitudeDegrees,Altitude,Temperature,HeartRate,Cadence,Powers,PointOrder) VALUES ('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%d')",strTimeStamp,strSpeed,strDistance,strLat,strLong,strAltitude,strTemperture,strHeartrateVal,lblCadenceVal.text,lblPowerVal.text,PointOrder];
        [insertSQL retain];
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database2,insert_stmt, -1, &compiledStatement, NULL);
//        if (sqlite3_prepare_v2(database2, insert_stmt, -1, &compiledStatement, NULL) == SQLITE_OK) {
        if(sqlite3_step(compiledStatement)==SQLITE_DONE)
        {
            NSLog(@"Working");
        }
        sqlite3_finalize(compiledStatement);
    }
    sqlite3_close(database2);
    PointOrder++;
}
-(void) DeleteNewRideRecords {

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    NSString *dbPath = [documentsDir stringByAppendingPathComponent:@"RideRecord.sqlite"];
    
    sqlite3 *database2;
    
    if (sqlite3_open([dbPath UTF8String], &database2) == SQLITE_OK) {
        NSString *query = @"delete from NewRideTable";
        const char *sqlStatement = [query UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(database2, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            // Loop through the results and add them to the feeds array
            while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
                // Read the data from the result row
                NSLog(@"result is here");
            }
            
            // Release the compiled statement from memory
            sqlite3_finalize(compiledStatement);
        }
    }
    PointOrder = 0;
    sqlite3_close(database2);

//    [self GetDataFromDB];

}