Use column name instead of number with sqlite3_column_text

Question

Is there any way to use a column name instead of an int for the columnnumber in: (char*)sqlite3_column_text(statement, 1)

This is what I do now:

sqlite3 *db = [MyAppDelegate getNewDBConnection];
sqlite3_stmt *statement = nil;

const char *sql = "select * from foo f join bar b on b.id = f.id;";

if(sqlite3_prepare_v2(db, sql, -1, &statement, NULL) != SQLITE_OK)
   NSAssert1(0,@"Error preparing statement %s", sqlite3_errmsg(db));
       else
       {
           while(sqlite3_step(statement) == SQLITE_ROW) {

               [lorem addObject:[NSString stringWithFormat:@"%s",(char*)sqlite3_column_text(statement, 1)]];

               [ipsum addObject:[NSString stringWithFormat:@"%s",(char*)sqlite3_column_text(statement, 7)]];

           }   

           sqlite3_finalize(statement);

       }

But ideally I would do (char*)sqlite3_column_text(statement, SomeColumn)

Answer 1

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)
...

Answer 2

It seems like there's no direct way of doing it. What I did was to a) 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 using column_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];
        }