ios - 在Objective-C中从Sqlite读取数据

Question

我试图在Objective-C中从sqlite读取数据，但结果是QuestionNumber和TotalRate均为0，无法从sqlite读取值。请 helper 我应该如何更改我的代码？

-(void)readSqlite{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *filePath =  [documentsDirectory stringByAppendingPathComponent:@"Questiondata.db"];
    NSLog(@"filePath,%@",filePath);
    NSFileManager *fileManager = [NSFileManager defaultManager];

    if([fileManager fileExistsAtPath:filePath]){
        NSLog(@"database is exist.");
        NSMutableArray *retArray =  [[NSMutableArray alloc] init];
        const char *dbpath = [filePath UTF8String];
        if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK) {

            NSLog(@"THIS IS OK");
            const char *insert_stmt = "SELECT QuestionNumber, TotalRate FROM Questions WHERE QuestionNumber = 2";
            sqlite3_stmt *statement;

            if (sqlite3_prepare_v2(_contactDB, insert_stmt, -1, &statement, NULL) == SQLITE_OK) {

                NSLog(@"SQLITE_OK");
                int QuestionNumberx = sqlite3_column_int(statement, 0);
                int TotalRate = sqlite3_column_int(statement, 1);

                NSLog(@"QuestionNumberx %d",QuestionNumberx );
                NSLog(@"TotalRate %d",TotalRate );

                [retArray addObject:[NSNumber numberWithInt:QuestionNumberx]];
                [retArray addObject:[NSNumber numberWithInt:TotalRate]];

                NSLog(@"%s db err '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(_contactDB), sqlite3_errcode(_contactDB));

                NSLog(@"retArray %@",retArray );

                sqlite3_finalize(statement);
            }
        }
    }else{
        NSLog(@"database is not exist.");
    }
}

Answer 1

您实际上从未使用sqlite3_step执行查询。您也永远不会关闭数据库。

以下假设您将从查询中仅获得一条记录:

-(void)readSqlite{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *filePath =  [documentsDirectory stringByAppendingPathComponent:@"Questiondata.db"];
    NSLog(@"filePath,%@",filePath);
    NSFileManager *fileManager = [NSFileManager defaultManager];

    if([fileManager fileExistsAtPath:filePath]){
        NSLog(@"database is exist.");
        NSMutableArray *retArray =  [[NSMutableArray alloc] init];
        const char *dbpath = [filePath UTF8String];
        if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK) {

            NSLog(@"THIS IS OK");
            const char *insert_stmt = "SELECT QuestionNumber, TotalRate FROM Questions WHERE QuestionNumber = 2";
            sqlite3_stmt *statement;

            if (sqlite3_prepare_v2(_contactDB, insert_stmt, -1, &statement, NULL) == SQLITE_OK) {

                NSLog(@"SQLITE_OK");
                if (sqlite3_step(statement) == SQLITE_ROW) {
                    int QuestionNumberx = sqlite3_column_int(statement, 0);
                    int TotalRate = sqlite3_column_int(statement, 1);

                    NSLog(@"QuestionNumberx %d",QuestionNumberx );
                    NSLog(@"TotalRate %d",TotalRate );

                    [retArray addObject:[NSNumber numberWithInt:QuestionNumberx]];
                    [retArray addObject:[NSNumber numberWithInt:TotalRate]];

                    NSLog(@"retArray %@",retArray );
                }

                sqlite3_finalize(statement);
            } else {
                NSLog(@"Unable to prepare statement: %s db err '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(_contactDB), sqlite3_errcode(_contactDB));
            }

            sqlite3_close(_contactDB);
        } else {
            NSLog(@"Unable top open database.");
        }
    }else{
        NSLog(@"database is not exist.");
    }
}