SQlite query - How to get multiple data columns?

Question

I find it difficult to find a working example on the net regarding getting multiple values ​​from SQlite DB using xcode and cocos2dx. Here is the sql query I have:

char sql_query[100]; sprintf(sql_query, "SELECT * FROM SQList WHERE ColumnD BETWEEN %d AND %d ORDER BY RANDOM() LIMIT 1", MinColumnD, MaxColumnD); 

It seems that it works itself, the main problem is how to get the values ​​that I collect from "select *" into another int or char parameter so that I can use it?

In some example, I found a link to using a callback for a structure or a mention of using sqlite3_prepare_v2 and the step method.

I can not find an example for any methods, although please help!

Answer 1

When using sqlite3_exec you need to convert all the values ​​from the strings, and you need to use the void * callback pointer or some global variable to return the data:

 struct MyData { string A; int B, C; }; int exec_callback(void *ptr, int argc, char *argv[], char *names[]) { vector<MyData> *list = reinterpret_cast<vector<MyData> *>(ptr); MyData d; dA = argv[0] ? argv[0] : ""; dB = atoi(argv[1]); dC = atoi(argv[2]); list->push_back(d); return 0; } void query_with_exec() { vector<MyData> list; char *errmsg = NULL; sqlite3_exec(db, "SELECT a, b, c FROM SQList /* WHERE ... */", exec_callback, &list, &errmsg); if (errmsg) { printf("error: %s!\n", errmsg); return; } // use list ... } 

When using sqlite3_prepare* you should call sqlite3_step in a loop until it returns SQLITE_ROW (if you expect only one entry, you can only call it once):

 void query_with_step() { vector<MyData> list; sqlite3_stmt *stmt; int rc = sqlite3_prepare_v2(db, "SELECT a, b, c FROM SQList /* WHERE ... */", -1, &stmt, NULL); if (rc != SQLITE_OK) { printf("error: %s!\n", sqlite3_errmsg(db)); return; } for (;;) { rc = sqlite3_step(stmt); if (rc == SQLITE_DONE) break; if (rc != SQLITE_ROW) { printf("error: %s!\n", sqlite3_errmsg(db)); break; } MyData d; const char *text = (const char *)sqlite3_column_text(stmt, 0); dA = text ? text : ""; dB = sqlite3_column_int(stmt, 1); dC = sqlite3_column_int(stmt, 2); list.push_back(d); } sqlite3_finalize(stmt); // use list ... } 

Answer 2

Get the values ​​using the "SELECT" query and store the char and int values ​​in an array. Create an NSMUtableArray and save the values ​​you get from the SELECT query. The following code will help you

 -(int)find_putts_count:(int)holeno{ sqlite3 *database; NSString *databasePath; NSMUtableArray *arr_tracking_details = [[NSMUtableArray alloc] init]; int putts = -1; NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDir = [documentPaths objectAtIndex:0]; databasePath = [documentsDir stringByAppendingPathComponent:@"GolfElite.sqlite"]; if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) { // Setup the SQL Statement and compile it for faster access NSString *sqlStatement = nil; sqlStatement = [NSString stringWithFormat:@"select Putts from Holes WHERE HoleNo == %d and ShotNo == %d and PlayerId == '%@'",holeno,1,[arr_players_id objectAtIndex:scorecard_player_no]]; sqlite3_stmt *compiledStatement; if(sqlite3_prepare_v2(database, [sqlStatement UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK) { if(sqlite3_step(compiledStatement) == SQLITE_ROW) { [arr_tracking_details addObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)]]; [arr_tracking_details addObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement,6)]]; [arr_tracking_details addObject:[NSString stringWithFormat:@"%d",(int)sqlite3_column_int(compiledStatement, 4)]]; putts = (int)sqlite3_column_int(compiledStatement, 0); } } sqlite3_finalize(compiledStatement); } sqlite3_close(database); return putts; }