Abdullah Nurum Abdullah Nurum - 5 months ago 18
iOS Question

Read data from Sqlite in Objective-C

I am trying to read data from sqlite in Objective-C, but the result is QuestionNumber and TotalRate both is 0, can not read value from sqlite. Please helper how should I change my code?

-(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

You never actually execute the query with sqlite3_step. You also never close the database.

The following assumes you will only get a single record back from your query:

-(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.");
    }
}