Delete data in SQLite

Question

I want to delete record from database,

I define in file.h the database, and in the file.m the insert data and read data it's work, but the delete was not work.

this is the file.h

#import "sqlite3.h"
#define DATA_FILE @"prova12"
#define TABLE_NAME @"password"
#define FIELDS_NAME_SID @"pass"
#define FIELDS_NAME_SNAME @"foto"
#define FIELDS_NAME_SCLASS @"studentClass"
#define FIELDS_NAME_PROVA @"provaClass"

{sqlite3 *db;
}

in the file.m

for the path:

    -(NSString *)dataFilePath {
    NSArray * myPaths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory,NSUserDomainMask, YES); NSString * myDocPath = [myPaths objectAtIndex:0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:DATA_FILE];
    return filename;

for read:

   -(NSMutableArray*)selectAll
  {
NSMutableArray *list = [[NSMutableArray alloc] initWithObjects:nil];
NSString *filename = [self dataFilePath];
NSLog(@"%@",filename);
if (sqlite3_open([filename UTF8String], &db) != SQLITE_OK) {
    sqlite3_close(db);
    NSAssert(NO,@"no");
} else {

    NSString *qsql = [NSString stringWithFormat: @"SELECT %@ FROM %@", FIELDS_NAME_SID, TABLE_NAME];
    NSLog(@"qui%@",qsql);
    sqlite3_stmt *statement;

    if (sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, NULL) == SQLITE_OK) {

        sqlite3_bind_text(statement, 1, [inserisci.text UTF8String], -1, NULL);


        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *field1 = (char *) sqlite3_column_text(statement, 0);
            NSString *field1Str = [[NSString alloc] initWithUTF8String: field1];
            //studentId.text = field1Str;
            //[field1Str release];
            [list addObject:field1Str];
            NSLog(@"%d",list.count);

        }
    }

    sqlite3_finalize(statement);
    sqlite3_close(db);

}
return list;
}

and this for save:

-(IBAction) save {
NSString *filename = [self dataFilePath];


if (sqlite3_open([filename UTF8String], &db) != SQLITE_OK) {
    sqlite3_close(db);
    NSAssert(NO,@"no");
} else {

    NSString *sqlStr = [NSString stringWithFormat: @"INSERT OR REPLACE INTO %@ (%@, %@, %@ ,%@) VALUES (?,?,?,?)",
                        TABLE_NAME, FIELDS_NAME_SID, FIELDS_NAME_SNAME, FIELDS_NAME_SCLASS, FIELDS_NAME_PROVA];

    sqlite3_stmt *statement;

    if (sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK) {

        NSLog(@"quiiiiddddd %@", filename);
        sqlite3_bind_text(statement, 1, [inserisci.text UTF8String], -1, NULL);
        sqlite3_bind_text(statement, 2, [immagine       UTF8String], -1, NULL);
        sqlite3_bind_text(statement, 3, [inserisci.text UTF8String], -1, NULL);
        sqlite3_bind_text(statement, 4, [inserisci.text UTF8String], -1, NULL);



        if (sqlite3_step(statement) != SQLITE_DONE) {
            NSAssert(0, @"no");
        }
    }

    sqlite3_finalize(statement);
    sqlite3_close(db);

}

but I don't have an idea to delete data, can anyone please help me?

Answer 1

you want to delete all record ore selected

-(bool)deleteEvent:(int)eventID
{
    DBSettings *dbSettings = [[DBSettings alloc]init];
    [dbSettings checkAndCreateDatabase];
    DatabaseName=dbSettings.DBName;
    DatabasePath=dbSettings.DBPath;
    [dbSettings release];

    sqlite3 *database;
    if(sqlite3_open([DatabasePath UTF8String], &database) == SQLITE_OK)
    {
        const char *sqlStatement;
        NSString *query = [NSString stringWithFormat:@"%@'%i'",kDeleteQuery,eventID];

        //Convert NSString to char pointer for execution
        sqlStatement=[query UTF8String];

        if(sqlite3_prepare_v2(database, sqlStatement, -1, &deleteStmt, NULL) != SQLITE_OK)
            NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));

        if(SQLITE_DONE != sqlite3_step(deleteStmt))
        {
            NSAssert1(0, @"Error while deleting data. '%s'", sqlite3_errmsg(database));
        }

        sqlite3_finalize(deleteStmt);
        sqlite3_close(database);
        return YES;
}

return NO;

}

kDeleteQuery "Is your query for all record or selected"

Answer 2

-(void)deleteDetails:(int *)detailId
{
    if (sqlite3_open([filename UTF8String], &db) != SQLITE_OK) 
    {
        sqlite3_close(db);
    }
    else 
    {
        sqlite3_stmt *statement;
        NSString  *strSQL =[NSString stringWithFormat:@"DELETE FROM tablename WHERE id = %d", detailId];
        mainSql = [strSQL UTF8String];

        if(sqlite3_prepare_v2(db, mainSql , -1, &statement, NULL)==SQLITE_OK)
        {           
           while(sqlite3_step(statement) == SQLITE_ROW)
           {
               NSLog(@"Records are deleted");
           }
        }
        else
        {
            NSLog(@"Error : -------'%s'", sqlite3_errmsg(db));
        }
    }
    sqlite3_reset(statement);
    sqlite3_finalize(statement);
    sqlite3_close(db);
}