iOS - using sqlite database update data not working

Question

I would like to update some data in Xcode sqlite db. The db is successfully connected, but seems like there's something wrong in the sql statement, it keeps returning "Failed to add contact", thanks for helping.

- (void) saveData:(id)sender
{

    NSLog(@"The code runs through here!");
    sqlite3_stmt    *statement;

    NSString *documents = [self applicationDocumentsDirectory];
    NSString *dbPath = [documents stringByAppendingPathComponent:@"monitorDB.sqlite"];
    const char *dbpath = [dbPath cStringUsingEncoding:NSASCIIStringEncoding];

    if (sqlite3_open(dbpath, & contactDB) == SQLITE_OK)
    {

        NSString *insertSQL = [NSString stringWithFormat:
                               @"UPDATE profile SET username = \"%@\" WHERE id = 1" ,
                               self.username.text];

        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(contactDB, insert_stmt,
                           -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            self.settingStatus.text = @"Contact added";
        } else {
            self.settingStatus.text = @"Failed to add contact";
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }   else {
        self.settingStatus.text = @"DB Not Connect";
    }



}

Solution 1

Try like this..

In viewdidload we need to check wether table exist or not. If not we need to create db.

NSString *docsdir;
NSArray *dirpaths;

dirpaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsdir=[dirpaths objectAtIndex:0];
dabasePath=[NSString stringWithFormat:[docsdir stringByAppendingPathComponent:@"contact.db"]];

NSFileManager *filemgr= [NSFileManager defaultManager];
if ([filemgr fileExistsAtPath:dabasePath]==NO ) {
    const char *dbpath=[dabasePath UTF8String];
    if (sqlite3_open(dbpath, &contactDB)== SQLITE_OK) {
        char *error;
        const char *sql_stmt="CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, ADDRESS TEXT, NAME TEXT, PHONE TEXT, IMAGE BLOB)";
        if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &error)!= SQLITE_OK) {
            status.text=@"failed to create";
        }
        sqlite3_close(contactDB);
    }
}

To save data try to use the following code.

-(IBAction)saveData:(id)sender{

    sqlite3_stmt *statement;
    const char *dbpath = [dabasePath UTF8String];
    NSData *imagedata=UIImagePNGRepresentation(imageview.image);
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) {
        NSString *insertSql =[NSString stringWithFormat:@"INSERT INTO CONTACTS (name, address, phone, image) VALUES (\"%@\", \"%@\", \"%@\", ?) ", name.text, address.text, phone.text ];
        // NSString *nam=name.text;
        const char *insert_stmt = [insertSql UTF8String];
        sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL);
        sqlite3_bind_blob(statement, 1, [imagedata bytes], [imagedata length], NULL);

        if (sqlite3_step(statement) == SQLITE_DONE) {
          status.text=@"contact added";
          [self clearClick:nil];
        }else{
          status.text=@"failed to added";
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }
}

To update data try to use the following code.

-(IBAction)updateClick:(id)sender{

sqlite3_stmt *updateStmt;
 const char *dbpath = [dabasePath UTF8String];
if(sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
    const char *sql = "update contacts Set address = ?, phone = ?, image = ? Where name=?";
    if(sqlite3_prepare_v2(contactDB, sql, -1, &updateStmt, NULL)==SQLITE_OK){
        sqlite3_bind_text(updateStmt, 4, [name.text UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(updateStmt, 1, [address.text UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(updateStmt, 2, [phone.text UTF8String], -1, SQLITE_TRANSIENT);
        NSData *imagedata=UIImagePNGRepresentation(imageview.image);
        sqlite3_bind_blob(updateStmt, 3, [imagedata bytes], [imagedata length], NULL);
    }
}
char* errmsg;
sqlite3_exec(contactDB, "COMMIT", NULL, NULL, &errmsg);

if(SQLITE_DONE != sqlite3_step(updateStmt)){
    NSLog(@"Error while updating. %s", sqlite3_errmsg(contactDB));
}
else{
    [self clearClick:nil];
}
sqlite3_finalize(updateStmt);
sqlite3_close(contactDB);
}

Solution 2

Check your sql query and change it like this.

NSString *insertSQL = [NSString stringWithFormat:
                           @"UPDATE profile SET username = '%@' WHERE id = 1" ,
                           self.username.text];

Or if you want to do using bind text.

if (sqlite3_open(dbpath, & contactDB) == SQLITE_OK)
{    
    const char *insert_stmt = "UPDATE profile SET username = ? WHERE id = 1";
    if(sqlite3_prepare_v2(contactDB, insert_stmt,
                          -1, &statement, NULL)== SQLITE_OK)
    {
        sqlite3_bind_text(statement, 1, [self.username.text UTF8String], -1, SQLITE_TRANSIENT);
    }

    if (sqlite3_step(statement) == SQLITE_DONE)
    {
        self.settingStatus.text = @"Contact added";
    } else {
        self.settingStatus.text = @"Failed to add contact";
    }
    sqlite3_finalize(statement);
    sqlite3_close(contactDB);
}   else {
    self.settingStatus.text = @"DB Not Connect";
}

Comments

[trojanfoe]

This looks more like a comment than an answer. There cannot be answer at the moment as the OP has no idea why the UPDATE fails, until they introduce error reporting in their code.

[JennyProGram]

there;s a error message: 'no such table: profile'. Actually what i did is, create sqlite at Firefox, then i copy and add file to the xcode project. I reopened the sqlite file, and there's a 'profile' table

[Jason]

I think you need check the dbpath. If you want to update your sqlite db, you must copy it form bundle to local.

[trojanfoe]

Show the code for checkAndCreateDatabase. Stop using NSAssert1 in favour of NSLog.

[iAppDeveloper]

@jennyProGram just check update statement written is correct, then print the NSLog(@"%@",insertSQL); then run same query in db

[trojanfoe]

OK looking good. One final thing; use bindings to get the username into the prepared statement, not [NSString stringWithFormat:].

[Master Stroke]

Before doing any transaction with the db,please make sure that you're copying the db from bundle to documents directory(in case it's not present in the directory)

[user3182143]

Thank you so much kalyani puvvada.I struggeld with update query for past one month.Even i saw your answer i did not use the code fully.Just now i implemented your update code fully.It works now for me.I am happy now.Thank you.