Delete all records from the sqlite3 database iOS 7

Question

in my application I have stored the data in my sqlite3 database and uploading all my sqlite3 database to my online sever, once it copied to my server, I want to delete all my records I have stored in my local database.

I have two view controller first view controller, where I store the data to sqlite3 second view controller I have used to load the data to my server I have given like if user connected to network it has to upload the data.

I want like after uploading data to my server I want to delete all the records in my local database.

I have already tried some method its not working please tell me how to resolve this.

- (void) Delete_LoginData 
{
    if(sqlite3_open([self.filePath UTF8String], &db) == SQLITE_OK)
    {
        const char *sql = "DELETE * FROM reg";
        sqlite3_stmt *statement;
        if(sqlite3_prepare_v2(db, sql,-1, &statement, NULL) == SQLITE_OK)
        {
            sqlite3_reset(statement);
        }
     }
}

I called this function in my Reachability.

- (BOOL)reachable
{
    Reachability *reach = [Reachability reachabilityWithHostName:@"www.google.com"];
    NetworkStatus internetStatus = [reach currentReachabilityStatus];

    if(internetStatus == NotReachable) 
    {    
    }
    else
    {
        [self Delete_LoginData];
    }

    return YES;
}   

My file path.

- (NSString *) filePath
{
    NSArray *paths = filepath, YES);

    return [[paths objectAtIndex:0] stringByAppendingPathComponent:@"bp.sqlite"];
}

Code open the db.

- (void)openDB
{
    if (sqlite3_open([[self filePath] UTF8String], &db)!=SQLITE_OK)
    {
        sqlite3_close(db);
        NSAssert(0, @"Database failed to open");
    }
    else
    {
        NSLog(@"database opened");
    }
}

I have used this above method to delete record from my local database its not working please to where I'm doing wrong.

Thanks.

Answer 1

You can do like this,

sqlite3_stmt *statement;
- (void) Delete_LoginData 
{
   // Open database first
   if(sqlite3_open([self.filePath UTF8String], &db) == SQLITE_OK)
   {
      // Sql query
      const char *sql = "DELETE FROM reg";
      // We are preparing the statement here
      if(sqlite3_prepare_v2(db, sql,-1, &statement, NULL) == SQLITE_OK)
      {
          // If that prepared statement is OK then we can execute that statement
          if(sqlite3_step(statement) == SQLITE_DONE)
          {
              NSLog(@" Records deleted successfully..");
          }
          else
          {
              NSLog(@" Opppsss..... Unable to delete records..");
          }
      }
      // Finalizing the statement
      sqlite3_finalize(statement);
      // Closing the database
      sqlite3_close(db);    
}

Hope this work for you...

Thank you...

Answer 2

sqlite3_prepare_v2 doesn't act the execution You need to take a step into it

if(sqlite3_open([self.filePath UTF8String], &db) == SQLITE_OK){
    NSString *sql = "DELETE  FROM reg";
    const char *query_st = [sql UTF8String];

    if(sqlite3_prepare_v2(database, query_st, -1, &statement, NULL) == SQLITE_OK){
        // Loop through the results and add them to the feeds array
        if(sqlite3_step(statement) == SQLITE_DONE) {
            // Deleted records,....
        }else{
             NSAssert1(0,@"Error when counting rows  %s",sqlite3_errmsg(database));
        }
        sqlite3_finalize(statement);
    }
    sqlite3_close(database);
}

Answer 3

Use sqlite3_step instead of sqlite3_reset to execute the query

if(sqlite3_open([self.filePath UTF8String], &db) == SQLITE_OK)
{
    const char *sql = "DELETE FROM reg";
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, sql,-1, &statement, NULL) == SQLITE_OK)
    {
       if(sqlite3_step(statement) == SQLITE_DONE){
       // executed
       }else{
         //NSLog(@"%s",sqlite3_errmsg(db))
       }
    }
    sqlite3_finalize(statement);
}
sqlite3_close(db);