sqlite3_exec() Callback function clarification

Let's assume you have a very simple table called User that looks something like this:

╔════╦══════════╗
║ ID ║ Name     ║
╟────╫──────────╢
║ 1  ║ Slvrfn   ║
║ 2  ║ Sean     ║
║ 3  ║ Drew     ║
║ 4  ║ mah      ║
╚════╩══════════╝

And you call sqlite3_exec like this (the arguments are described in detail in the documentation):

/* Error handling omitted for brevity */
sqlite3_exec(db, "SELECT * FROM User", my_special_callback, NULL, NULL);

SQLite will execute the passed SQL statement and for every result row that it finds it will call my_special_callback. So with our example User table, my_special_callback will be called 4 times. So let's create my_special_callback:

/*
 * Arguments:
 *
 *   unused - Ignored in this case, see the documentation for sqlite3_exec
 *    count - The number of columns in the result set
 *     data - The row's data
 *  columns - The column names
 */
static int my_special_callback(void *unused, int count, char **data, char **columns)
{
    int idx;

    printf("There are %d column(s)\n", count);

    for (idx = 0; idx < count; idx++) {
        printf("The data in column \"%s\" is: %s\n", columns[idx], data[idx]);
    }

    printf("\n");

    return 0;
}

Given our example table and data, the output will look like this:

There are 2 column(s)
The data in column "ID" is: 1
The data in column "Name" is: Slvrfn

There are 2 column(s)
The data in column "ID" is: 2
The data in column "Name" is: Sean

There are 2 column(s)
The data in column "ID" is: 3
The data in column "Name" is: Drew

There are 2 column(s)
The data in column "ID" is: 4
The data in column "Name" is: mah

Now to how to make this useful, that is where the 4th argument to sqlite3_exec comes in. From the documentation:

The 4th argument to sqlite3_exec() is relayed through to the 1st argument of each callback invocation.

So let's say that we want to run our SQL and build a linked list of the names of all of our users. The first thing we need to do is change how we are calling sqlite3_exec:

/* Create my fictional linked list */
struct my_linked_list *head = my_linked_list_alloc();

/*
 * Pass a pointer to my list as the 4th argument to sqlite3_exec. Error
 * handling omitted for brevity
 */
sqlite3_exec(db, "SELECT * FROM User", my_special_callback, head, NULL);

/* My list is now built, I can do stuff with it... */
my_linked_list_traverse(head, /* ... Stuff ... */);

And modify my_special_callback to use it

/*
 * Arguments:
 *
 *     list - Pointer to a linked list of names
 *    count - The number of columns in the result set
 *     data - The row's data
 *  columns - The column names
 */
static int my_special_callback(void *list, int count, char **data, char **columns)
{
    struct my_linked_list *head = list;

    /*
     * We know that the value from the Name column is in the second slot
     * of the data array.
     */
    my_linked_list_append(head, data[1]);

    return 0;
}

Now, if you were to use the callback you included in your question, you would call it like this:

/*
 * Pass the table name as the 4th argument to sqlite3_exec. Error
 * handling omitted for brevity
 */
sqlite3_exec(db, "SELECT * FROM User", callback, "User", NULL);

The output would be:

User: 
ID = 1
Name = Slvrfn

User: 
ID = 2
Name = Sean

... etc ...

(Except the User: part would be printed to stderr instead of stdout)

Hopefully this helps clear things up for you. Let me know if there is still something that you don't understand.


That tutorial is horrible, because it does not use anything but sqlite3_exec().

In the general case, the only useful way to use sqlite3_exec() is to replace it with sqlite3_prepare_v2()/sqlite3_step()/sqlite3_column_*()/sqlite3_finalize() calls so that you can read the data in the same place where you actually need to handle it:

sqlite3_stmt *stmt;
const char *sql = "SELECT ID, Name FROM User";
int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
    print("error: ", sqlite3_errmsg(db));
    return;
}
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
    int id           = sqlite3_column_int (stmt, 0);
    const char *name = sqlite3_column_text(stmt, 1);
    // ...
}
if (rc != SQLITE_DONE) {
    print("error: ", sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);