Using sqlite3_exec() to get single result in C

Question

I know there is a lot of posts and documentation out there on exactly how to use this function, but no matter what I do I can't quite grasp the concept of callback functions in regards to this API. So here's what I'm trying to do, I have a relation that looks like this:

+----+-------+
| id | event |
+----+-------+
|  1 |    45 |
|  2 |    22 |
|  3 |    12 |
+----+-------+

My goal is to use the following SQL statement SELECT MAX(id) FROM events; to get the value 3 back for later use in my C Code. I'm not exactly sure how to use sqlite3_exec() to get this single value. I'll leave my currently written code below to show where I'm currently at. Btw I'm not including my code to open the database because I know that works. Callback is empty because I'm not exactly sure how to use it. Thanks in advance for any help! (Please go easy on me, I'm trying to get community help to learn :))

Current sqlite3_exec() call

int max_event_id;
char *zErrMsg;

//Error handling omitted for readability
sqlite3_exec(database, "SELECT MAX(id) FROM log_events;", max_id_callback, max_event_id, &zErrMsg)

max_id_callback() function (Currently Empty)

static bool max_id_callback( void *max_id, int count, char **data, char **columns)
{
}

Doesn't that generate a compiler warning? ("Integer converted to pointer without a cast" or something of that form?). Or are you compiling without asking for compiler warnings? — rici
– rici, Commented Jul 22, 2020 at 14:48
Yeah I am getting warnings for the cast, I'll have to fix that haha. More concerned with the functionality of the callback() function right now though — Justanothermatt
– Justanothermatt, Commented Jul 22, 2020 at 14:50
I appreciate the concern, but in this post I'm looking for more of a conceptual answer on the usage of callback functions rather than help with smaller errors in the code — Justanothermatt
– Justanothermatt, Commented Jul 22, 2020 at 14:54
Here's what the documentation says: "If the callback function of the 3rd argument to sqlite3_exec() is not NULL, then it is invoked for each result row coming out of the evaluated SQL statements." invoked is just a fancy way of saying called: the callback function is called with each row from the result. (That's why it's a call back.) It's called with particular arguments, also described in the documentation, one of which is the fourth argument to sqlite3_exec, which the callback function receives as its first argument. — rici
– rici, Commented Jul 22, 2020 at 14:54

John Bollinger · Accepted Answer · 2020-07-22 15:32:47Z

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

Nobel needs to have a "Stack Overflow Answer" prize so we can nominate this answer for one. Really well done.
So i'm understanding most of this. I define a variable and feed it's address to my callback function via my exec() function. Because of the callback function definition, this variable's address is implicitly converted to void. Since I want an int, I convert it back. The data collected from the SQL statement is stored as a string, so I need to make a new pointer that points to that string. Next step is to convert that string into an int, I'll just use atoi(). After that I want to set the original pointer to this new int, thus making the data of my main variable be that int? Is that correct?

Collectives™ on Stack Overflow

Using sqlite3_exec() to get single result in C

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related