Access sqlite database from user function
(1) By anonymous on 2020-07-16 07:38:14 [link] [source]
Is there an example of how to access the SQLite database from a user function?
(2) By Keith Medcalf (kmedcalf) on 2020-07-16 13:37:17 in reply to 1 [source]
See for example https://www.sqlite.org/src/file?name=ext/misc/eval.c&ci=tip
The short form is that the first argument passed to a user function is the context pointer. There is a function called sqlite3_context_db_handle which will retrieve the db_handle (sqlite3*) from the context.
https://www.sqlite.org/c3ref/context_db_handle.html
Once you have that you can prepare and execute SQL statements in the normal fashion with the caveat, of course, that if you "change" anything, and those changes affect the running statement, then the result is "undefined" (meaning whatever happens is the intended and correct result).
(3) By Vadim Goncharov (nuclight) on 2022-05-09 13:49:54 in reply to 2 [link] [source]
with the caveat, of course, that if you "change" anything, and those changes affect the running statement, then the result is "undefined" (meaning whatever happens is the intended and correct result).
What "change" means here? UPDATEs and INSERTs?
(4) By MBL (UserMBL) on 2022-05-09 16:03:02 in reply to 3 [link] [source]
Yes, updates, inserts and deletes, all of them.
Think about a select with a user defined function, which does such changes to record 2 while it was called in record 1. Then the select will read when the cursor is at record 2 what was done in record 1 earlier during the same query. This is meant.
All these changes are running in the same transaction; means when at the end a rollback happens then all the changes done by the user defined function will also be rolled back and will not remain persistent to the database.