With the documentation:

int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

I need clarification on

  int (*callback)(void*,int,char**,char**),  /* Callback function */

I understand

1. void* = whatever I choose it to be, it is passed verbatim
2. int = (the second) number of columns in the result
3. char** = column names
4. char** = column values

My questions:

A. char** = column values is ALWAYS text regardless of whether the source table is strict or the column is numeric, leaving the calling programme to parse as appropriate. Correct? (Another way of asking is this: Does SQLite apply affinity before passing the values?)

B. Is it possible to change the signature of the callback function and add a 5th (boolean) argument? If yes, how could I tell SQLite whether to pass true or false?

A. char** = column values is ALWAYS text regardless of whether the source table is strict or the column is numeric

Correct. That's why exec() is only useful for very basic cases. By and large, you'll want to use sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize() to traverse result sets.

Is it possible to change the signature of the callback function and add a 5th (boolean) argument?

No. Backwards compatibility constraints on the library prohibit any function signature changes once a function is in widespread use. To pass in a boolean (or anything else), use the 4th argument to exec(), which gets passed as-is as the first argument to your callback.

To pass in a boolean (or anything else), use the 4th argument to exec(), which gets passed as-is as the first argument to your callback.

I am already using the 4th argument. I suppose I could have an object[] with various items as the 4th argument. Must try.

I am already using the 4th argument. I suppose I could have an object[] with various items as the 4th argument. Must try.

Don't use an object array. Use a custom struct, which allows for an amount of data in a type-safe manner:

struct MyExecState {
  int foo;
  char const * bar;
  ...
};

...

static int my_callback(void* arg, int n, char**vals, char**colNames){
  struct MyExecState * my = (struct MyExecState *)arg;
  ...
}

...

struct MyExecState s = {1,"Hi"};
int rc = sqlite3_exec(db, sql, callback, &s, NULL);
...

sqlite3_exec() calls sqlite3_value_text() internally on all values in order to retrieve a textual representation.

Thanks for confirming (I had been thinking as much).

I am using sqlite3_exec & converting the text values as appropriate in the callback function; timing wise:

Record Count: 100000 Execution Time: 1.274s
Record Count: 200000 Execution Time: 2.409s
Record Count: 300000 Execution Time: 3.636s

I can live with that (is acceptable for serious work).

If an error occurs while evaluating the SQL statements passed into sqlite3_exec(), then execution of the current statement stops and subsequent statements are skipped.

When the 2nd parameter to sqlite3_exec contains multiple sql statements, according to the documentation, it

1. evaluates each complete statement (separated by ;) sequentially
2. will stop if any particular sql statement does NOT yield SQLITE_OK and abandon the sequential execution.

Given multiple statements, when a statement results in SQLITE_OK, what is the API can can retrieve the sql statement just executed i.e. that yielded SQLITE_OK?

The 5th argument of sqlite3_exec is an output variable (pointer).

If it is non-zero:

1. Does my application need to free the pointer after reading its text?
2. Will sqlite3_free take the pointer (errMsg) and free it?

If it is non-zero:

Your question is answered in detail in the 3rd paragraph of the docs:

If the 5th parameter to sqlite3_exec() is not NULL then any error message is written into memory obtained from sqlite3_malloc() and passed back through the 5th parameter. To avoid memory leaks, the application should invoke sqlite3_free() on error message strings returned through the 5th parameter of sqlite3_exec() after the error message string is no longer needed. If the 5th parameter to sqlite3_exec() is not NULL and no errors occur, then sqlite3_exec() sets the pointer in its 5th parameter to NULL before returning.

From the API's doc page: "To avoid memory leaks, the application should invoke sqlite3_free() on error message strings returned through the 5th parameter of sqlite3_exec() after the error message string is no longer needed."

If that does not answer question #1, please explain.

As for the effect of sqlite3_free(), please read its API doc. A link to the C API function docs is on the home page labeled "List of C-language APIs".

If that does not answer question #1, please explain.

Perhaps I am being obtuse: should I call sqlite3_free() OR sqlite3_free(errmsg)?

According to the documentation, sqlite3_free has an argument *void - I am not clear whether this means an argument is needed? (In terms of C#, either IntPtr.Zero or the errmsg pointer if non zero).

Here's a trimmed version of some RAII code around sqlite3_exec that answers your questions:

PS: I wrote the wrapper for old code I didn't write, to make it RAII. I never use sqlite3_exec myself, since as several people already pointed out it's best avoided.

struct SQLiteCmd {
    char* err = nullptr;
    int rc = 0;

protected:
    SQLiteCmd() = default;

    SQLiteCmd(const SQLiteCmd&) = delete;
    SQLiteCmd& operator=(const SQLiteCmd&) = delete;

    SQLiteCmd(SQLiteCmd&&) = delete;
    SQLiteCmd& operator=(SQLiteCmd&&) = delete;

    ~SQLiteCmd() {
        reset();
    }

    void reset() {
        sqlite3_free(err);

        err = nullptr;
        rc = 0;
    }

public:
    bool ok() const { return rc == 0; }
    explicit operator bool() const { return ok(); }
};

struct SQLiteExec final : SQLiteCmd {
    SQLiteExec() = default;

    SQLiteExec(sqlite3* db, const std::string& sql_cmd) {
        operator()(db, sql_cmd);
    }

    void operator()(sqlite3* db, const std::string& sql_cmd) {
        reset();
        rc = sqlite3_exec(db, sql_cmd.c_str(), nullptr, nullptr, &err);
    }
};

struct SQLiteTable final : SQLiteCmd {
    char** data = nullptr;
    int rows = 0;
    int cols = 0;

    SQLiteTable(sqlite3* db, const std::string& sql_cmd) {
        get(db, sql_cmd);
    }

    ~SQLiteTable() {
        reset();
    }

    void get(sqlite3* db, const std::string& sql_cmd) {
        reset();
        rc = sqlite3_get_table(db, sql_cmd.c_str(), &data, &rows, &cols, &err);
    }

    void reset() {
        SQLiteCmd::reset();

        sqlite3_free_table(data);

        data = nullptr;
        rows = cols = 0;
    }
};

Mentioning C#, I asked a binary question & I get a response that gives me lines of code in another language!

Still, within the response is

    sqlite3_free(err);

I guess the answer is that I need to call sqlite3_free(errMsg) (in my context).

Mentioning C#, I asked a binary question & I get a response that gives me lines of code in another language!

Yes, that is a well known and often discussed aspect of programming language choice and "debate". Hiding little chores like freeing objects is why adapters to SQLite from languages like C#, C++, Java, etc. are popular.

Still, within the response is

sqlite3_free(err);

I guess the answer is that I need to call sqlite3_free(errMsg) (in my context).

I must quibble here. There is no need to guess; it's as plain as day (during daylight hours.)

FYI, the notation "some_function()" is often used to refer to a function named "some_function" taking an unspecified number of parameters. It is a convenient shorthand. If you had consulted the API doc for sqlite3_free() as I suggested, and as should be done at least once for any API one uses, then there would be no mistaking the number of parameters required. This is true independently of any consideration that the function would be entirely useless if it took no parameters.

I'm not trying to be rude here. Rather, it seems to me that people (including you just now) need to understand the utility of reading the documentation. I hope that has become (more) obvious.

I should think that in order to free something, then one must specify the something that is to be free'd as the argument to the call, and that if nothing is specified then nothing will be free'd, and therefore the call is a nullity, so why bother making it at all?

That is why the void* in void sqlite3_free(void*); bothered me.

void* means 'a pointer to something'.

Contrast int* (pointer to an integer), char* (pointer to a character), char*[] (pointer to an array of characters -- same as char**, by the way).

void* can be used anywhere and is the "generic pointer type" (ie, it represents how the underlying hardware sees a "pointer" -- it merely points -- what is pointed at is an excercise for whatever is generating the machine code.

void* can be used anywhere and is the "generic pointer type"

This makes it easy to remember & will clarify future perusal of the documentation.

Thanks for making it clear.

from (random website which addresses the void* question):

https://byjus.com/gate/void-pointer-in-c/#:~:text=both%20the%20same.-,The%20void%20pointer%20in%20C%20is%20a%20pointer%20that%20is,called%20the%20general%20purpose%20pointer.

While the above deals with the question, it ventures outside the scope of SQL, and sqlite (IMO). However, since the question was raised, might as well answer it rather than a dangling forum thread.

extract on void* follows:

Frequently Asked Questions Is there any difference between the null pointer and the void pointer in C? The null pointer is basically used in a program to assign the value 0 to a pointer variable of any data type. The void pointer, on the other hand, has no value assigned to it and we use it to store the addresses of other variables in the program- irrespective of their data types. Is there any disadvantage to using the void pointer in C? The void pointer is pretty viable and safe to use in a code, and it makes things very easy to manage. But, its polymorphism due to the void * can be unsafe. Once we cast any pointer to the void*, we cannot prevent it from being cast to the wrong pointer by fault, in case there is an error in the program.

What is the difference between a general pointer and a void pointer in C? They are both the same. The void pointer in C is a pointer that is not associated with any data types. It points to some data location in the storage. This means it points to the address of variables. It is also called the general purpose pointer. In C, malloc() and calloc() functions return void * or generic pointers.

Why do we use a void pointer in C programs? We use the void pointers to overcome the issue of assigning separate values to different data types in a program. The pointer to void can be used in generic functions in C because it is capable of pointing to any data type. One can assign the void pointer with any data type’s address, and then assign the void pointer to any pointer without even performing some sort of explicit typecasting. So, it reduces complications in a code.

---

You are going to see (pardon the pun) quite a bit of void* in C, and since sqlite is written in C, in sqlite as well. It might be a good exercise to code up a C program using the C-API of sqlite regardless of what other language one uses just to understand the docs better.