问题
- 问题代码
FMDatabaseQueue *queue = [DBUtilities shareDBqueue];
//先执行del在插入
//注:此处用事务
[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
for(TestDoInfo *testdo in tempTestDoAdd){
checksql = [NSString stringWithFormat:kSQLExistsGreaterAddTimeWithTestdo,(long)testdo.testDoID,(long)testdo.carType,(long)testdo.subjectType,testdo.add_time];
FMResultSet *resultSet = [db executeQuery:checksql];
if (resultSet== nil || !resultSet.next) {
insertsql = [DBUtilitiesSQLHelper insertSQLWithObject:testdo];
updateSuccess = [db executeUpdate:insertsql];
if (!updateSuccess) {
*rollback = YES;
}else{
if (!testdo.is_show_in_wrong) {
updateSuccess = [db executeUpdate:[NSString stringWithFormat:@"update test_do set my_answer = (SELECT AnswerTrue FROM web_note where ID = %ld) where test_id = %ld and car_type = %ld and kemu_type = %ld ",(long)testdo.testDoID,(long)testdo.testDoID,(long)testdo.carType,(long)testdo.subjectType]];
if (!updateSuccess) {
*rollback = YES;
}
}
}
}
}
}];
错误在使用FMDB执行查询时未关闭返回结果集（FMResultSet）
解决方案(手动关闭结果集)
if (resultSet != nil) {
[resultSet close];
}
相关知识点
1.result.next
- (BOOL)next {
return [self nextWithError:nil];
}
- (BOOL)nextWithError:(NSError **)outErr {
int rc = sqlite3_step([_statement statement]);
if (SQLITE_BUSY == rc || SQLITE_LOCKED == rc) {
NSLog(@"%s:%d Database busy (%@)", __FUNCTION__, __LINE__, [_parentDB databasePath]);
NSLog(@"Database busy");
if (outErr) {
*outErr = [_parentDB lastError];
}
}
else if (SQLITE_DONE == rc || SQLITE_ROW == rc) {
// all is well, let's return.
}
else if (SQLITE_ERROR == rc) {
NSLog(@"Error calling sqlite3_step (%d: %s) rs", rc, sqlite3_errmsg([_parentDB sqliteHandle]));
if (outErr) {
*outErr = [_parentDB lastError];
}
}
else if (SQLITE_MISUSE == rc) {
// uh oh.
NSLog(@"Error calling sqlite3_step (%d: %s) rs", rc, sqlite3_errmsg([_parentDB sqliteHandle]));
if (outErr) {
if (_parentDB) {
*outErr = [_parentDB lastError];
}
else {
// If 'next' or 'nextWithError' is called after the result set is closed,
// we need to return the appropriate error.
NSDictionary* errorMessage = [NSDictionary dictionaryWithObject:@"parentDB does not exist" forKey:NSLocalizedDescriptionKey];
*outErr = [NSError errorWithDomain:@"FMDatabase" code:SQLITE_MISUSE userInfo:errorMessage];
}
}
}
else {
// wtf?
NSLog(@"Unknown error calling sqlite3_step (%d: %s) rs", rc, sqlite3_errmsg([_parentDB sqliteHandle]));
if (outErr) {
*outErr = [_parentDB lastError];
}
}
if (rc != SQLITE_ROW) {
[self close];
}
return (rc == SQLITE_ROW);
}
- sqlite3_step返回值
SQLITE_OK = 0; 返回成功
SQLITE_ERROR = 1; SQL错误或错误的数据库
SQLITE_INTERNAL = 2; An internal logic error in SQLite
SQLITE_PERM = 3; 拒绝访问
SQLITE_ABORT = 4; 回调函数请求中断
SQLITE_BUSY = 5; 数据库文件被锁
SQLITE_LOCKED = 6; 数据库中的一个表被锁
SQLITE_NOMEM = 7; 内存分配失败
SQLITE_READONLY = 8; 试图对一个只读数据库进行写操作
SQLITE_INTERRUPT = 9; 由sqlite_interrupt()结束操作
SQLITE_IOERR = 10; 磁盘I/O发生错误
SQLITE_CORRUPT = 11; 数据库磁盘镜像畸形
SQLITE_NOTFOUND = 12; (Internal Only)表或记录不存在
SQLITE_FULL = 13; 数据库满插入失败
SQLITE_CANTOPEN = 14; 不能打开数据库文件
SQLITE_PROTOCOL = 15; 数据库锁定协议错误
SQLITE_EMPTY = 16; (Internal Only)数据库表为空
SQLITE_SCHEMA = 17; 数据库模式改变
SQLITE_TOOBIG = 18; 对一个表数据行过多
SQLITE_CONSTRAINT = 19; 由于约束冲突而中止
SQLITE_MISMATCH = 20; 数据类型不匹配
SQLITE_MISUSE = 21; 数据库错误使用
SQLITE_NOLFS = 22; 使用主机操作系统不支持的特性
SQLITE_AUTH = 23; 非法授权
SQLITE_FORMAT = 24; 辅助数据库格式错误
SQLITE_RANGE = 25; 2nd parameter to sqlite_bind out of range
SQLITE_NOTADB = 26; 打开的不是一个数据库文件
SQLITE_ROW = 100; sqlite_step() has another row ready
SQLITE_DONE = 101; sqlite_step() has finished executing
在- (BOOL)nextWithError:(NSError **)outErr中会判断sqlite3_step返回值如果不等于SQLITE_ROW（即无下一个数据）会自动关闭结果集（当前前提是你调用了next方法）
常用方式
-
自动关闭无需手动关闭
FMDatabaseQueue *queue = [DBUtilities shareDBqueue];
[queue inDatabase:^(FMDatabase *db) {
FMResultSet *rs = [db executeQuery: weakSQL];
while (rs.next) {
NSInteger count = [rs intForColumn:@"totalCount"];
}
}];
[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
FMResultSet *rs1 = [db executeQuery: weakSQL1];
while (rs1.next) {
NSInteger count = [rs1 intForColumn:@"totalCount"];
}
FMResultSet *rs2 = [db executeQuery: weakSQL2];
while (rs2.next) {
NSInteger count = [rs2 intForColumn:@"totalCount"];
}
}];
-
不使用while只要一个结果，并if判断需手动关闭
__block NSInteger score = 0;
NSString *sql = [[NSString alloc] initWithFormat:kSQLGETSubjectLastExampleScore,(long)subjectType,(long)carType];
if (carType >=CarTypeCoach) {
sql =[[NSString alloc] initWithFormat:kSQLGETCartypeLastExampleScore,(long)carType];
}
__weak NSString *weakSQL = sql;
FMDatabaseQueue *queue = [DBUtilities shareDBqueue];
[queue inDatabase:^(FMDatabase *db) {
FMResultSet *rs = [db executeQuery: weakSQL];
if(rs.next){
score = [rs intForColumn:@"score"];
}
if (rs != nil) {
[rs close];
}
}];
return score;
