Sqlite3_Exec() Callback Function Clarification

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.

How to know if function 'callback' in sqlite returned something?

If you just want to see if a record exists in the table, then you could do it with sqlite3_exec() using a callback function like this:

int myCallback(void *pUser, int argc, char **colData, char **colNames) {
  int *flag = (int*)pUser;
  *flag = 1;
  return 1;
}

This works because if there are no records matching the query, then the callback function is not called. By returning 1 instead of 0, we are telling SQLite that we don't want any more rows from the query results.

Then, in the function where you are making the db query:

std::string sql = "SELECT * FROM COMPANY WHERE imie='John' AND surname='Wattson' AND age=31;";
char *pSql = sql.c_str(); // char*'s are better for talking to SQLite, and prior to C++14,
                          // a std::string is not guaranteed to be sequential in memory,
                          // so 'sql[0]' may not work right

char *pError = NULL;
int fHasResult = 0;

// db is an already-opened sqlite3*
int result = sqlite3_exec(db, pSql, myCallback, &fHasResult, &pError);
if (result) {
  cout<<"Error was: "<<pError;
  free(pError);
}
if (fHasResult) {
  cout<<"The row exists in the database.";
}
else {
  cout<<"The row does not exist in the database.";
}

C++ MFC SQLite sqlite3_exec callback

The callback for the sqlite3_exec API is widely underdocumented. The fact that it is purported with misleading parameter names doesn't help much, either. Providing more natural formal parameter names goes a long way:

static int callback(void* context,  // user-provided object (4th param to sqlite3_exec)
                    int columnCount,      // number of columns
                    char** columnValues,  // array of column values as C-style strings
                    char** columnName)    // array of column names as C-style strings

To update the m_Results object from the callback, you simply have to cast context to the correct type, and use that:

static int callback(void* context, int columnCount,
                    char** columnValues, char** columnNames) {
    CMyType& results = *static_cast<CMyType*>(context);
    // Use 'results' which is a reference to 'm_Results'.
    for (int index = 0; index < columnCount; ++index) {
        // Assuming CMyType has operator+=(const char*) (like a CString)
        results += columnNames[index];
        results += ": ";
        results += columnValues[index];
    }
    // Return 0 to continue invoking the callback for the remaining rows in the result
    // set. Returning non-zero will terminate the callbacks, and sqlite3_exec()
    // returns SQLITE_ABORT.
    return 0;

As an alternative, you can pass this in place of &m_Results to sqlite3_exec(), and invoke a public class member from your callback:

static int callback(void* context, int columnCount,
                    char** columnValues, char** columnNames) {
    CDBpracticeDlg* obj = static_cast<CDBpracticeDlg*>(context);
    // Delegate callback to class member implementation
    return obj->MyCallback(columnCount, columnValues, columnNames);
}

...

rc = sqlite3_exec(db, sql, callback, this, nullptr);

why sqlite3_exec fails with callback abort-like symptoms?

problem has no direct relation with sqlite3, but with callback implementation.

as per @Ctx comment, I am mixing between SIGABRT, and SQLITE_ABORT, both has no implications on each others, SIGABRT returns in callback due to memory corruption caused by off by one uninitialized memory usage.

table->current->val[i] = malloc(strlen(argv[i]));
strcpy(table->current->val[i], (const char*) argv[i]);

has to be changed to

table->current->val[i] = malloc(strlen(argv[i])+1);
strcpy(table->current->val[i], (const char*) argv[i]);
//or
table->current->val[i] = strdup(argv[i]);

the same question callback snippet above is actually rubbish, it has other bug unrelated to memory corruption.

SQLite in C/C++. sqlite3_exec: parameter set in callback function is pointing to an empty string

You are writing the pointer to the newly allocated memory into res, but that variable is a local variable inside select_callback, so sql_exec will not know about it.
The same applies to the param parameter: it is just a copy of sqlite3_exec's fourth parameter.

To ensure that your changes to the string are seen, you have to pass a pointer to the string itself (which is a pointer in C, or could be a string object in C++), similar to the error message.
For C:

char *result_str = ...;
rc = sqlite3_exec(..., &result_str, ...);
...
int callback(void *param, ...)
{
    char **result_str = (char **)param;
    *result_str = (char *)realloc(*result_str, ...);
    strcpy(*result_str, ...);
}

---

Note: You will get problems when the tid string contains quotes or other control characters, or when you try to search for Bobby Tables.
Use sqlite3_mprintf to format and allocate the SQL string, or better use parameters.

Using sqlite3_exec() to get single result in C

no matter what I do I can't quite grasp the concept of callback functions

I suppose you understand that "callback function" is a role that a function can have, not a special kind of function. The general idea is that a program calling some module to request services specifies a function that the module should call (a "call back" to the main program) to provide information (via the arguments passed) and / or to request further information (to be supplied via the return value and / or out parameters). The details vary widely, according to the needs of the module and the services it wants to provide.

With sqlite3_exec(), the callback, if provided, serves to process the rows resulting from the specified query. It is called once for each result row, with one argument that you specify, and other arguments that encode the result column count, result column values, and result column names. Since your particular query should produce exactly one result row, the callback will be called once.

I'm inclined to guess that your main confusion centers around the callback's first argument and sqlite3_exec()'s fourth. Not all callback interfaces feature this kind of user data argument, but many do. It provides for the callback to operate on data specified by the main program, about which the module calling it has no particular information. In your case, that provides a mechanism (other than a global variable) by which your callback function can record the result.

First, however, you should pay attention to the warnings emitted by your compiler, and if it is not warning about this call:

sqlite3_exec(database, "SELECT MAX(id) FROM log_events;", max_id_callback, max_event_id, &zErrMsg)

then you should turn up the warning level or get a better one. The fourth parameter to sqlite3_exec has type void *, but you are passing an int. The best thing you can hope for is that the value of the int is converted to a void *, which sqlite will later forward to your callback. Such a conversion from int to void * requires a cast in standard C, but more importantly, it doesn't help you. You presumably want the callback to modify the max_event_id variable, and for that, it needs the variable's address, not its value:

sqlite3_exec(database, "SELECT MAX(id) FROM log_events;", max_id_callback,
             &max_event_id, &zErrMsg)

Conversion between void * and any other object pointer type does not require a cast. You could insert one anyway, but I would advise not to do, because that would interfere with the compiler's ability to warn you when you make a mistake such as -- to choose a random example -- trying to pass an integer where a pointer is required.

Having settled that, the callback implementation for this case is not that hard. You can expect one call, reporting on a result row with one column. The value reported for that column will be the number you want, in text form. So, a template for an appropriate callback might be

#include <assert.h>

static bool max_id_callback( void *max_id, int count, char **data, char **columns) {
    int *max_id_int = max_id; // convert the void * back to int *

    // We expect exactly one column
    assert(count == 1);  // (optional)

    const char *max_as_text = data[0];
    // We can ignore the column name

    // TODO: convert string to int and store it in *max_id_int ...

    // SQLite should continue normally (though there should be no more rows):
    return 0;
}

---

Related Topics