Select data from sqlite iOS has null statement sometime

Question

I am selecting data from sqlite database. Problem is that using following code it works some time. But lets say we call this method for any other table twice then it shows null select statement for other method. Is any thing wrong in method so that if we use same for other tables some time it works some time not.

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

Is this fine for getting the data from table?

Answer 1

Your code needs a little cleanup.

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

Note all of the changes:

1. Only close the database if it is opened.
2. Only finalize the statement if it is prepared.
3. Don't build queries with stringWithFormat. Use the proper sqlite3_bind_xxx function to bind the values to the query. This ensures special characters are escaped and properly deals with quoting.
4. Use proper error checking. Use sqlite3_errmsg to get the error.