CODEWITHSUNDEEP
iosdatabasesqlitesql-delete
Alex McPherson
Alex McPherson

Reputation: 3195

Delete row from SQLLite database

I am trying to delete a row from my database "Code mentioned below", however it never deletes the row and the prepare statement also never seems to be called.

I can insert and update ok but for the life of me can't figure this out..

barcode is a string variable passed to the method from a editable tableview.

I can see the correct values when I log the deletSQL string but record is actually never removed.

For further info the barcode in the database is a primary key.

Also database paths are all correct and its calling the right db file.

Its just the delete function that is not working...

-(void)delete:(NSString *)barcode{

const char *dbpath = [databasePath3 UTF8String];
if (sqlite3_open(dbpath, &database3) == SQLITE_OK)
{

    NSLog(@"Opened OK");

NSString *deleteSQL = [NSString stringWithFormat: @"delete from assets where assetBarcode=\"%@\'",barcode];
NSLog(@"%@",deleteSQL);
const char *sql = [deleteSQL UTF8String];

if(sqlite3_prepare_v2(database3, sql, 1, &deleteStatement, NULL) == SQLITE_OK)
{

    NSLog(@"SQL OK?");

    if (sqlite3_step(deleteStatement) == SQLITE_ROW)
    {

        sqlite3_bind_text(deleteStatement, 1, [barcode UTF8String], -1, SQLITE_TRANSIENT);

    }

}

else{

    NSLog(@"WHAAAATTTTT");
}

}

sqlite3_step(deleteStatement);
sqlite3_reset(deleteStatement);
sqlite3_close(database3);

}

Upvotes: 0

Views: 1170

Answers (2)

gaurish.salunke
gaurish.salunke

Reputation: 1079

the barcode columne in the database appears to be of type varchar

try this

-(void)delete:(NSString *)barcode
{

if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) 
{
    if(deleteStatement == nil)
    {
        const char *sql = "DELETE FROM assets WHERE assetBarcode = ?";
        if(sqlite3_prepare_v2(database, sql, -1, & deleteStatement, NULL) != SQLITE_OK)
            NSAssert1(0, @"Error while creating delete statement. '%s'", sqlite3_errmsg(database));
    }

    sqlite3_bind_text(deleteStatement, 1, [barcode UTF8String], -1, SQLITE_TRANSIENT);

    if(SQLITE_DONE != sqlite3_step(deleteStatement))
        NSAssert1(0, @"Error while deleting data. '%s'", sqlite3_errmsg(database));
    else
        //SQLite provides a method to get the last primary key inserted by using sqlite3_last_insert_rowid
        //noteID = sqlite3_last_insert_rowid(database);

        //Reset the delete statement.
        sqlite3_reset(deleteStatement);
    sqlite3_close(database);
    deleteStatement = nil;
}   
else
    sqlite3_close(database);

}

Upvotes: 1

buggy_durability
buggy_durability

Reputation: 76

Regarding the SQL string...

delete from assets where assetBarcode=\"%@\'

The parameter is being opened with a quote and closed with a single quote. Use either double or single quotes for both. Perhaps this is related to your issue?

Upvotes: 0

Related Questions