CODEWITHSUNDEEP
iphoneobjective-ciossqlite
Thomas K
Thomas K

Reputation: 6196

Use column name instead of number with sqlite3_column_text

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)

Upvotes: 7

Views: 9836

Answers (3)

Ruchira Randana
Ruchira Randana

Reputation: 4179

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];
        }

Upvotes: 5

hajikelist
hajikelist

Reputation: 1176

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.

Upvotes: 2

Graham Perks
Graham Perks

Reputation: 23390

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

Upvotes: 5

Related Questions