how to iterate through all the columns in sqlite using fmdb in objective-c

Question

I need to do a search on all the columns, so instead of specifying each column name in the where clause I would like to create a method that can search through all the columns.

-(void) fillSomeobjectNames:(NSString *)filter
{
    FMDatabase *db = [FMDatabase databaseWithPath:databasePath];

    [db open];

    NSString *sql = [NSString stringWithFormat:@"SELECT id, name, imagefile FROM %@ %@", mainTableName, filter];
    FMResultSet *results = [db executeQuery:sql];
    while([results next]) 
    {
        Someobject *someobject = [[Someobject alloc] init];
        [someobject setID:[results intForColumn:@"id"]];
        [someobject setName:[results stringForColumn:@"name"]];
        NSString *iconName = [[results stringForColumn:@"imagefile"] stringByReplacingOccurrencesOfString:@".jpg" withString:@""];
        [someobject setIconPath:[NSString stringWithFormat:@"%@-ico.jpg",iconName]];
        [someobjects someobject];
    }

    [db close];
}

'filter' can be 'WHERE remark=[searchText] AND description=[searchText] AND so on...'

Answer 1

I don't think this can be done with a single SELECT statement. The best way to get column names is PRAGMA table_info(table_name), unfortunately this cannot be used in the SELECT statements, so you need one additional SELECT to get the column names, and then you can build your sql and do what you want:

-(void) fillSomeobjectNames:(NSString *)filter
    {
        FMDatabase *db = [FMDatabase databaseWithPath:databasePath];

        [db open];

        FMResultSet columnNamesSet* = [db executeQuery:[NSString stringWithFormat:@"PRAGMA table_info(%@)", mainTableName]];
        NSMutableArray* columnNames = [[NSMutableArray alloc] init];
        while ([columnNamesSet next]) {
            [columnNames addObject:[columnNamesSet stringForColumn:@"name"]];
        }
        // put whatever you need to look for as mySearchPhrase
        NSString* partToAppend = [NSString stringWithFormat:@"=%@ OR ", @"mySearchPhrase"];
        NSString* filter = [columnNames componentsJoinedByString:partToAppend];
        filter = [filter stringByAppendingString:[NSString stringWithFormat:@"=%@", @"mySearchPhrase"]];

        // now your sql would look like this
        NSString *sql = [NSString stringWithFormat:@"SELECT id, name, imagefile FROM %@ WHERE %@", mainTableName, filter];
        FMResultSet *results = [db executeQuery:sql];
        while([results next]) 
        {
            Someobject *someobject = [[Someobject alloc] init];
            [someobject setID:[results intForColumn:@"id"]];
            [someobject setName:[results stringForColumn:@"name"]];
            NSString *iconName = [[results stringForColumn:@"imagefile"] stringByReplacingOccurrencesOfString:@".jpg" withString:@""];
            [someobject setIconPath:[NSString stringWithFormat:@"%@-ico.jpg",iconName]];
            [someobjects someobject];
        }

        [db close];
    }

Note that I moved WHERE word out of the filter, and also I used OR instead of AND, which is what I think you intended to do.

One additional thing to keep in mind is the data type in each column. You compare each one to a string, but is each column in your table of type text? The pragma statement that I used also returns the type of the given column (in the column named type), so you could use that to filter the columns that are of type text.