I want to create a custom function, which while being called does one or two SQL queries. The two queries are always the same, and so I want to reuse the prepared statements to save time.

I have that working just fine so far, but now my problem is finalizing the two statements when closing the DB connection. Is there some way of registering a destructor for an extension that I'm just not finding? Or is there something else I should do? All I can think of on my own is creating a utility function that finalizes the statements when I'm done, but that doesn't seem like the best way of doing it.

Before closing the connection, you can find all the unfinalized statements associated with that connection using the sqlite3_stmt_next https://sqlite.org/c3ref/next_stmt.html API.

You can then iterate though any unfinalized statements and reset/finalize them before closing the connection.

Good to know, but doesn't help much in this case, unfortunately.

Extra background info: this will mainly be used in PHP, but also when using the CLI.

The CLI cannot use prepared statements. It prepares, binds (using your parameters if appropriate), steps and finalizes a statement all in one operation. A statement is destroyed once it has been executed.

If you're using the SQLite3 module, PHP will destroy remaining statements of a connection when you issue SQLite3::close, or when the PHP environment for a server query is terminated. It uses the method Keith mentioned upthread to ensure remaining statements are destoyed. As a programmer, you are expected to issue SQLite3Result::reset or SQLite3Result::finalize for each statement when you have read the last row it returns. You can also close the statement yourself, if you like.

The CLI can use prepared statements when part of an extension though, which is the case here.

Also, I could just be missing something, but I do not see sqlite3_stmt_next when searching the source code for PHP's SQLite extension, meaning it probably only finalizes statements it has prepared itself, and knows nothing of anything done by extensions.

You seem to be describing the xDestroy argument of the sqlite3_create_function_v2 function.

(See documentation.)

sqlite3_close checks for unfinalized statements (and aborts the close) before destroying function definitions. You can't finalize statements in the xDestroy method because the method won't be called.

Eventually you will be able to use sqlite3_prepare_v3 with caching enabled. Then you simply prepare/finalize the statements every time you use them, and let SQLite3 itself worry about "caching" the VDBE program and avoiding unnecessary re-prepares.

I do not know what the status of this update is, but the sqlite3_prepare_v3 API has been added already.

I do not know what the status of this update is, but the sqlite3_prepare_v3 API has been added already.

That enhancement is on a branch, but the branch has been abandoned for now because I cannot find a case where it is actually helpful. It just seems to be extra complication with no clear benefit.

caching via sqlite3_prepare_v3 does seem overly complicated, especially in languages without GC.

Any chance of something like a sqlite3_create_function_v3 which allows you to pass in an xBeforeDisconnect hook to finalize prepared statements?

Could the undocumented feature of using negative column numbers in auxdata become a documented feature? That would certainly address this issue in that once a context prepares a statement, that prepared statement can be used by that context until it gets discarded.

You will still have to prepare the statements inside the function for each new containing context (outer VDBE program), however, the statement will only be prepared once. Eg:

select x(value) from wholenumber where value between 1 and 1000;

would execute the function x 1000 times. If it required running an SQL statement then that "inner statement" would be compiled, executed, and finalized 1000 times.

However, if the use of negative column numbers were a documented feature, then that very same function x, if it stashed the prepared statement in a negative auxdata slot, would still execute the function 1000 times but would only prepare the "inner statement" once, execute it 1000 times, and finalize it once.

Of course, one could always use a negative column number ever though it is undocumented and just be aware that it is an internal undocumented feature (used by the JSON extension) that may "explode" at any time.