For a few years now it is possible to load an extension both in MBS Xojo SQL Plugin using SQLite as well as into Xojo's built-in SQLiteDatabase. But now we have something new for you here:
You can register custom functions to use in the SQLite database and define them in Xojo.
To provide a function, you use SQLiteFunctionMBS class. The constructor automatically registers it and the destructor cleans up. You are required to keep objects alive by e.g. storing them in a global array.
Let's implement a test function using a subclass TestFunction and implement there the Perform event:
As you see we like to use #pragmas to avoid a few extras Xojo does. And then we return the result of the function as text. The arguments array passed by the plugin contains the values. Strings, Double and Integers are passed as variants. The variant may also be a MemoryBlock for a BLOB value or simply nil for a NULL value.
Next we overwrite the constructor to initialize properties like the name of the function. You can define how many arguments the function takes and pass -1 for a variable number of arguments. Flags define details about the function. Like we prefer text in UTF-8, the function is deterministic and can be cached in a lookup table. And since the function has no side effects, it can be declared innocuous. This allows SQLite to further optimize calling your function.
We can initialize the function and add it to our global functions array with a line like this:
Now we can try it by using SQLSelect or SelectSQL to run it:
Instead of a subclass, we can just create an object and use addHandler to connect it to a method:
For the SHA256 function we can define it in a method in a module:
As you see we pick parameter and read it as MemoryBlock or as string with conversion to MemoryBlock. Then we can run SHA2 with 256-bit and return the result as BLOB value.
We can use the SHA256 with the Hex function like this in SQLite:
The function shows the hash A591A6D40BF420404A011733CFB7B190D62C65BF0BCDA32B57B277D9AD9F146E as result.
If you prefer using Xojo's SQLiteDatabase, you can load our plugin file as extension. First you enable extensions early by setting LoadExtensions parameter to true:
After connect, you can load the extension into the database connection:
Next we can try the function and it works as well:
Please try the new functions with 24.1 pre-release and the new MBS Xojo SQL Plugin.