什么时候调用SQLite清理功能? [英] when to call SQLite cleanup functions?
问题描述
我正在使用iOS中的sqlite数据库.我在应用程序中使用了CURD操作.例如，我在下面的代码中将数据插入到数据库中.
I am working with sqlite database in iOS.I have used CURD operation in my app.For example to insert data into the database i have used below code.
- (BOOL) saveData:(User *)user
{
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
if([self getUserData:user.email] != nil)
{
[self updateUserData:user];
return YES;
}
else
{
NSString *insertSQL = [NSString stringWithFormat:
@"insert into users(userid,name,email,password,address,age,gender,phone,qualification,role,createddate,apiKey,priorityid,usertype) values (\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\")",user.userid,user.name,user.email,user.password,user.address,user.age,user.gender,user.phone,user.qualification,user.role,user.createddate,user.api_key,user.priority_id,user.user_type];
NSLog(@"%@",insertSQL);
const char *insert_stmt = [insertSQL UTF8String];
sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
if (sqlite3_step(statement) == SQLITE_DONE)
{
sqlite3_finalize(statement);
// sqlite3_close(database);
return YES;
}
else
{
NSLog(@"%serrr is ",sqlite3_errmsg(database));
//sqlite3_reset(statement);
sqlite3_close(database);
return NO;
}
}
}
//sqlite3_reset(statement);
sqlite3_close(database);
return NO;
}
由于sqlite数据库，我的应用程序出现内存问题.我无法理解调用 sqlite3_reset(); ， sqlite3_finalize()的顺序. ， sqlite3_close().请参考我的代码，以便我可以解决应用程序中的所有内存问题
I am getting memory issues in my app due to sqlite database.I am not able to understand what should be order of calling sqlite3_reset();,sqlite3_finalize(), sqlite3_close().Please tell in reference to my code so that i can resolve all memory issues in my app
- (BOOL) insertSeMapData:(Client *)client : (NSString *)userid : (NSString *)sendemailto : (NSString *)assignworkordersto
{
BOOL result=NO;
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
InspectionMapDetails *inspMap = [self getSEMapData:client :userid];
if(inspMap != nil)
{
[self updateSEMapData:client :userid :inspMap : sendemailto : assignworkordersto];
result=YES;
}
else
{
const char *insert_stmt = "insert into map(inspid,inspectorid,clientid,status,createddate,sendemailreportto,assignworkordersto) values (?,?,?,?, datetime(),?,?)";
if (sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL) == SQLITE_OK)
{
NSString *clientinspid=[NSString stringWithFormat:@"%@",client.inspid];
NSString *clientid=[NSString stringWithFormat:@"%@",client.clientid];
userid=[NSString stringWithFormat:@"%@",userid];
sqlite3_bind_text(statement, 1, [clientinspid UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 2, [userid UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 3, [clientid UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 4, [sendemailto UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 5, [assignworkordersto UTF8String], -1, SQLITE_TRANSIENT);
if (sqlite3_step(statement) == SQLITE_DONE)
{
result = YES;
}
sqlite3_finalize(statement);
}
else
{
NSLog(@"Unable to prepare statement: %s",sqlite3_errmsg(database));
}
sqlite3_close(database);
}
}
else
{
NSLog(@"Unable to open database: %s",sqlite3_errmsg(database));
}
return result;
}
上面的函数导致了内存问题. Sqlite_open 语句正在导致内存泄漏，我不明白为什么?
Above function causing memory issue.Here Sqlite_open statement is causing a memory leak which i don't understand why?
推荐答案
有简单的指导原则，直言不讳，您的代码根本没有遵循任何指导原则.
There are simple guidelines, and to be blunt, your code doesn't follow any of them at all.
对于成功调用sqlite3_open的每次呼叫，您都必须呼叫sqlite3_close.
For each successful call to sqlite3_open you must call sqlite3_close.
对于每次成功调用sqlite3_prepare_v2，您必须调用sqlite3_finalize. (可选)在两次重用已准备好的语句的情况下，您可以在两次之间调用sqlite3_reset零次或多次.
For each successful call to sqlite3_prepare_v2 you must call sqlite3_finalize. Optionally, in between you may call sqlite3_reset zero or more times in cases where you reuse the prepared statement.
您应该始终检查对sqlite3_open和sqlite3_prepare_v2的调用结果.如果失败，则应使用sqlite3_errmsg记录问题所在.
You should always check the result of calls to sqlite3_open and sqlite3_prepare_v2. If they fail, you should use sqlite3_errmsg to log what the problem was.
请勿使用stringWithFormat构建查询字符串.而是使用对各种sqlite3_bind_xxx函数的适当调用将值正确绑定到查询.
Do not build query strings using stringWithFormat. Instead, properly bind values to the query using appropriate calls to the various sqlite3_bind_xxx functions.
您发布的代码违反了所有这些条件.在很多情况下，您不关闭数据库或完成预准备的语句.而且您错误地使用stringWithFormat:建立查询.
The code you posted violates all of these. You have plenty of cases where you don't close the database or finalize the prepared statement. And you are incorrectly building your query with stringWithFormat:.
这是您的全部固定代码.请注意，我假设您要插入的所有值都是字符串.相应地调整任何非字符串值.
Here's your code all fixed up. Note that I'm assuming all the values you are inserting are strings. Adjust accordingly for any non-string values.
- (BOOL) saveData:(User *)user
{
BOOL result = NO;
if([self getUserData:user.email] != nil)
{
[self updateUserData:user];
result = YES;
}
else
{
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
const char *insert_stmt = "insert into users(userid,name,email,password,address,age,gender,phone,qualification,role,createddate,apiKey,priorityid,usertype) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
if (sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL) == SQLITE_OK) {
sqlite3_bind_text(stmt, 1, [user.userid UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, [user.name UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, [user.email UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 4, [user.password UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 5, [user.address UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 6, [user.age UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 7, [user.gender UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 8, [user.phone UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 9, [user.qualification UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 10, [user.role UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 11, [user.createddate UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 12, [user.api_key UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 13, [user.priority_id UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 14, [user.user_type UTF8String], -1, SQLITE_TRANSIENT);
if (sqlite3_step(statement) == SQLITE_DONE)
{
result = YES;
}
sqlite3_finalize(statement);
}
else
{
NSLog(@"Unable to prepare statement: %s",sqlite3_errmsg(database));
}
sqlite3_close(database);
}
else
{
NSLog(@"Unable to open database: %s",sqlite3_errmsg(database));
}
}
return result;
}
基于新修改进行更新:
就像您的原始代码一样，您违反了一些规则.您更新的代码不太符合我在上面的答案中给出的模式.
Just like your original code, you violate a few of the rules. Your updated code doesn't quite follow the pattern I gave in my answer above.
现在的主要问题是您打开数据库，但仅在两个可能的代码路径之一下将其关闭.如果inspMap != nil您从不关闭数据库.
The main issue now is that you open the database but you only close it under one of two possible code paths. If inspMap != nil you never close the database.
您确实应该重构代码，以便仅在inspMap为nil的情况下打开数据库.我在原始答案中做了与您原始代码相似的操作.
You really should refactor the code so you only open the database if inspMap is nil. I did something similar to your original code in my original answer.
这篇关于什么时候调用SQLite清理功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
