How do I select data from an sqlite3 database into variables using sqlite3_exec?

Question

There are a million examples on the interweb detailing the use of sqlite3_exec to select rows from a table and printing them using this callback function:

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i<argc; i++){
    printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
  }
  printf("\n");
  return 0;
}

This is the code that implements callback:

[...]
rc = sqlite3_exec(db, sql, callback, &nrecs, &zErrMsg);
[...]

Now, I would assume that using the fetched data for something other than just printing would be a rather standard scenario, but for the life of me I cannot find out how this is supposed to be accomplished.

What I wish to do is fetch one row (argc will be 1) containing two columns and have these two values (argv[0] and argv[1]) accessible from where sqlite3_exec was executed.

I suppose this has something to do with the void *NotUsed. (What in the world is this a pointer to and why do all examples insist on not using it?)

If someone could help me with this i would be ever so grateful. If you could also explain to me why this seemingly trivial task has been made so complicated you will have my undying love. (The fetched data is handled in a static function called from another function. This effectively kills the purpose of using an object oriented language, right?)

marom · Accepted Answer · 2015-06-24 15:10:49Z

15

The 'key' is the fourth parameter of sqlite3_exec.

Usually the scenario is like this: You have this struct you weant to fill in with sqlite query:

struct xyz_t
{
   int a ;
   int b ;
} ;

then you call sqlite3_exec like this:

//fill in query text
xyz_t xyz; // struct you want to fill
sqlite3_exec(db, sql, callback, &xyz, &zErrMsg);

And the callback does this:

static int callback(void *veryUsed, int argc, char **argv, char **azColName){
  int i;
  xyz_t *xyz = (xyz_t *)veryUsed ;
  for(i=0; i<argc; i++){
    if (strcmp(azColName[i], "a") == 0)
       xyz->a = atoi(argv[1]) ;
    else if ...
  }
  return 0;
}

You can pass even a std::vector and push_back a new xyz_t on every call. Not nice for a C++ programmer but this is a mixed C/C++ world...

answered Jun 24, 2015 at 15:10

marom

5,23813 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Over a year ago

That was a flawless and well explained solution to my problem. Thank you very much!

Collectives™ on Stack Overflow

How do I select data from an sqlite3 database into variables using sqlite3_exec?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related