ios - 从 sqlite iOS 中选择数据有时有空语句

Question

我正在从 sqlite 数据库中选择数据。问题是使用以下代码它可以工作一段时间。但是假设我们为任何其他表调用此方法两次，然后它显示其他方法的 null select 语句。方法中是否有任何错误，以至于如果我们有时对其他表使用相同的方法，有时它会不起作用。

-(void)getAssessmentNumber:(NSString *)dbPath{
    appDelegate=[[UIApplication sharedApplication]delegate];
    NSString*fileDBPath=[[NSBundle mainBundle] pathForResource:@"Database" ofType:@"sqlite"];
    if (sqlite3_open([fileDBPath UTF8String], &database) == SQLITE_OK)
    {
        //  NSLog(@"%@",[self getDBPath]);
        NSString *querySQL = [NSString stringWithFormat:@"Select Am.AssessmentID , Am.AssessmentName From AssessmentMaster Am LEFT JOIN AssessmentDepartmentMapping M ON M.AssessmentID = Am.AssessmentID LEFT JOIN DepartmentListing d ON d.departmentID =M.departmentID where d.departmentID = '%@'",appDelegate.departmentID];
        NSLog(@"%@",querySQL);
        const char *sql = [querySQL UTF8String];
        sqlite3_stmt *selectstmt;
        NSError *error;
        [appDelegate.assessmentNumberArray removeAllObjects];

        if (sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(selectstmt) == SQLITE_ROW)
            {
                NSInteger primaryKey = sqlite3_column_int(selectstmt, 0);
                AssessmentListening   *asmObj = [[AssessmentListening alloc] initWithPrimaryKey:primaryKey];
                asmObj.assessmentID=[NSString stringWithFormat:@"%d",primaryKey];

                asmObj.assessmentName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)];

                [appDelegate.assessmentNumberArray addObject:asmObj];
            }
        }
        else {
            NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
        }

        //  sqlite3_finalize(selectstmt);
    }
    sqlite3_close(database);
}

这样可以从表中获取数据吗？

Answer 1

您的代码需要进行一些清理。

- (void)getAssessmentNumber:(NSString *)dbPath {
    NSString *fileDBPath = [[NSBundle mainBundle] pathForResource:@"Database" ofType:@"sqlite"];
    if (sqlite3_open([fileDBPath UTF8String], &database) == SQLITE_OK) {
        appDelegate = [[UIApplication sharedApplication] delegate];
        [appDelegate.assessmentNumberArray removeAllObjects];

        const char *sql = "Select Am.AssessmentID, Am.AssessmentName From AssessmentMaster Am LEFT JOIN AssessmentDepartmentMapping M ON M.AssessmentID = Am.AssessmentID LEFT JOIN DepartmentListing d ON d.departmentID = M.departmentID where d.departmentID = ?";
        sqlite3_stmt *selectstmt;

        if (sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
            sqlite3_bind_text(selectstmt, 0, [appDelegate.departmentID UTF8String], -1, SQLITE_TRANSIENT);

            while (sqlite3_step(selectstmt) == SQLITE_ROW) {
                NSInteger primaryKey = sqlite3_column_int(selectstmt, 0);
                AssessmentListening *asmObj = [[AssessmentListening alloc] initWithPrimaryKey:primaryKey];
                asmObj.assessmentID = [NSString stringWithFormat:@"%d", primaryKey];

                asmObj.assessmentName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)];

                [appDelegate.assessmentNumberArray addObject:asmObj];
            }

            sqlite3_finalize(selectstmt);
        } else {
            NSLog(@"Unable to prepare statement: %s", sqlite3_errmsg(database));
        }

        sqlite3_close(database);
    } else {
        NSLog(@"Unable to open the database from %@: %s", fileDBPath, sqlite3_errmsg(database);
    }
}

注意所有的变化:

1. 仅在数据库打开时才将其关闭。
2. 仅在准备好声明后才最终确定该声明。
3. 不要使用stringWithFormat构建查询。使用正确的 sqlite3_bind_xxx 函数将值绑定(bind)到查询。这可以确保特殊字符被转义并正确处理引用。
4. 使用适当的错误检查。使用 sqlite3_errmsg 获取错误。