Use column name instead of number with sqlite3_column_text

Firstly, have a look at the FMDB wrapper for Objective-C, it'll save you a lot of raw SQLite coding. (FMDB info: http://gusmueller.com/blog/archives/2008/06/new_home_for_fmdb.html)

I don't know of a way to pass a column name. However it would simplify things, and be more robust, if you alter your SQL and name the columns there, rather than 'select *'.

const char *sql = "select f.name, b.address, b.phone ... 

The accessors then simply pass column numbers 1, 2, 3, 4, ... Much better than magic numbers 1 & 7!

name    = sqlite3_column_text(statement, 1)address = sqlite3_column_text(statement, 2)phone   = sqlite3_column_text(statement, 3)...

It seems like there's no direct way of doing it. What I did was toa) get the number of fields in result by sqlite3_column_count()

b) I then created a dictionary to map the values

c) I would then iterate over the columns and populate the dictionary with key/value pairs usingcolumn_name/index

Now, you have a mapping dictionary which holds the indexes of the column names. You can use this to get the index of the column by using the column name.

Here's my code snippet. Hope it helps !!!

        int columnCount = sqlite3_column_count(statement);        NSMutableDictionary *mapper=[[NSMutableDictionary alloc] init];        for(int i=0;i<columnCount;i++){            const char *_columnName=sqlite3_column_name(statement, i);            NSString *columnName=[[NSString alloc] initWithUTF8String:_columnName];            [mapper setObject:[NSNumber numberWithInteger:i] forKey:columnName];        }

you could even go as far as to make a struct to map your sql field names to column numbers, then you could access your fields like so; name = sqlite3_column_text(statement, tablename_struct.name) - I'm working on something like this now actually.

    struct ElementsStructure {        int ID;         int Slug;         int Title;         int Value;         int Type;         int Flags;         int Index;        int Options;     };    //field to column mappings for sql lite queries    struct ElementsStructure oElements;    oElements.ID = 0;    oElements.Slug = 1;     oElements.Title = 2;     oElements.Value = 3;     oElements.Type = 4;     oElements.Flags = 5;     oElements.Index = 6;    oElements.Options = 7; ...    NSNumber *ID = [NSNumber numberWithInt: sqlite3_column_int(statement, oElements.ID)];

I just like this method since if the struct is named as the actual table name it provides a super easy way to visually correlate what you're assigning... especially for others who may be looking through your code.