objective-c iOS - 使用 sqlite 数据库更新数据不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18713462/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
iOS - using sqlite database update data not working
提问by JennyProGram
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.
我想更新 Xcode sqlite db 中的一些数据。db连接成功，但是sql语句好像有问题，一直返回“添加联系人失败”，感谢帮助。
- (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";
}
}
回答by kalyani puvvada
Try like this..
试试这样..
In viewdidload we need to check wether table exist or not. If not we need to create db.
在 viewdidload 中，我们需要检查表是否存在。如果没有，我们需要创建数据库。
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);
}
回答by Rajneesh071
Check your sql query and change it like this.
检查您的 sql 查询并像这样更改它。
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";
}
回答by iAppDeveloper
Just check
只需检查
in .h file NSString *databaseName;
NSString *databasePath;
and in .m file specify databaseName = @"Db name";
NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [documentPaths objectAtIndex:0];
databasePath = [documentsDir stringByAppendingPathComponent:databaseName];
-(void)save:(id)sender
{
[self checkAndCreateDatabase];
sqlite3 *contactDB;
sqlite3_stmt *updateStmt;
if(sqlite3_open([databasePath UTF8String], &contactDB) == SQLITE_OK)
{
NSString *querySql=[NSString stringWithFormat:
@"UPDATE profile SET username = \"%@\" WHERE id = 1" ,
self.username.text];
const char*sql=[querySql UTF8String];
if(sqlite3_prepare_v2(contactDB,sql, -1, &updateStmt, NULL) == SQLITE_OK)
{
if(SQLITE_DONE != sqlite3_step(updateStmt))
{
NSLog(@"Error while updating. '%s'", sqlite3_errmsg(contactDB));
}
else{
sqlite3_reset(updateStmt);
NSLog(@"Update done successfully!");
}
}
sqlite3_finalize(updateStmt);
}
sqlite3_close(contactDB);
}
-(void) checkAndCreateDatabase{
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
success = [fileManager fileExistsAtPath:databasePath];
if(success) return;
NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
[fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
}
回答by Geekoder
There are many possibilities check all of the below:
有很多可能性检查以下所有内容：
1) Initialize statement with nil
1) 用 nil 初始化语句
sqlite3_stmt *statement = nil;
2) Try below
2）试试下面
if (sqlite3_prepare_v2(contactDB, insert_stmt,-1, &statement, NULL) == SQLITE_OK)
{
while (sqlite3_step(statement) == SQLITE_ROW)
{
//Updated
}
}
else
{
NSLog(@"error is %s",sqlite3_errmsg(database));
}
回答by Jason
Are you sure the dbPath is not in app bundle? We can't update db in the bundle.
您确定 dbPath 不在应用程序包中吗？我们无法更新包中的 db。
sqlite3_prepare_v2() and sqlite3_step() will return an int.
You can find something you want in sqlite3.h.
你可以在 sqlite3.h 中找到你想要的东西。
Like #define SQLITE_BUSY 5 /* The database file is locked */ ...
And, why not use FMDB? You can find it easy on Github. (Link https://github.com/ccgus/fmdb)
而且，为什么不使用 FMDB？你可以在 Github 上轻松找到。（链接https://github.com/ccgus/fmdb）
