Problem getting data from SQLite3 on iPhone

Question

I've been trying to return data from a table after already having accessed two before it, but in this case it get's into the while statement but does not assign any values as everything is set to null.

The code is:

NSMutableArray *all_species = [[NSMutableArray alloc] init];
sqlite3 *db_species;
int dbrc_species;
Linnaeus_LiteAppDelegate *appDelegate = (Linnaeus_LiteAppDelegate*) [UIApplication sharedApplication].delegate;
const char* dbFilePathUTF8 = [appDelegate.dbFilePath UTF8String];
dbrc_species = sqlite3_open (dbFilePathUTF8, &db_species);
if (dbrc_species) {
    return all_species;
}
sqlite3_stmt *dbps_species;
const char *queryStatement = "SELECT species_id, species_name, species_latin, species_genus FROM \
                                linnaeus_species;";
if (sqlite3_prepare_v2 (db_species, queryStatement, -1, &dbps_species, NULL) == SQLITE_OK) {
    sqlite3_bind_int(dbps_species, 1, [the_species_id intValue]);
    while (sqlite3_step(dbps_species) == SQLITE_ROW) {
        Species *species = [[Species alloc] init];
        NSLog(@"%@", sqlite3_column_int(dbps_species, 0));
        [species setSpecies_id:[[NSNumber alloc] initWithInt:sqlite3_column_int(dbps_species, 0)]];
        char *new_name = (char *) sqlite3_column_text(dbps_species, 1);
        [species setSpecies_name:nil];
        if (new_name != NULL) {
            [species setSpecies_name:[NSString stringWithUTF8String:(char *) sqlite3_column_text(dbps_species, 1)]];
        }
        char *new_latin = (char *) sqlite3_column_text(dbps_species, 2);
        [species setSpecies_latin:nil];
        if (new_latin != NULL) {
            [species setSpecies_latin:[NSString stringWithUTF8String:(char *) sqlite3_column_text(dbps_species, 2)]];
        }
        [species setSpecies_genus:[NSNumber numberWithInt:sqlite3_column_int(dbps_species, 3)]];

        [species setEdited:0];
        [all_species addObject:species];
        [species release];
    }
    sqlite3_finalize(dbps_species);
}
else {
    sqlite3_close(db_species);
}

I've also tried using NSLog(@"Data: %@", sqlite3_column_text(dbps_species, 1)); and it causes a EXC_BAD_ACCESS error which suggests it could be memory related but I can't see why.

Answer 1

NSLog(@"Data: %@", sqlite3_column_text(dbps_species, 1));

Will cause EXC_BAD_ACCESS because the result of sqlite3_column_text is a C string (char*), not an NSString*. To print C strings you need the %s format specifier:

NSLog(@"Data: %s", sqlite3_column_text(dbps_species, 1));

---

Also, don't waste time to call sqlite3_column_text twice, e.g.

    char *new_name = (char *) sqlite3_column_text(dbps_species, 1);
    [species setSpecies_name:nil];
    if (new_name != NULL) {
        [species setSpecies_name:[NSString stringWithUTF8String:new_name]];
    }

Answer 2

You could also try using the FMDB classes. These make using sqlite a LOT easier.

http://gusmueller.com/blog/archives/2008/03/fmdb_for_iphone.html