iOS - 使用sqlite数据库更新数据不起作用 [英] iOS - using sqlite database update data not working

问题描述

我想更新Xcode sqlite db中的一些数据。数据库已成功连接，但似乎sql语句中出现了问题，它一直返回无法添加联系人，感谢您的帮助。

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";
    }



}

推荐答案

尝试这样..

在viewdidload中我们需要检查是否存在表。如果不是，我们需要创建数据库。

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);
}

这篇关于iOS - 使用sqlite数据库更新数据不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！