Did you know that you can load extensions in SQLite?

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.

Subclass SQLiteFunction

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:

EventHandler Sub Perform(ArgumentCount as Integer, Arguments() as Variant) #Pragma BackgroundTasks False #Pragma BoundsChecking False #Pragma StackOverflowChecking False ResultText "Hello World" End EventHandler

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.

Sub Constructor() // Calling the overridden superclass constructor. Super.Constructor Me.Name = "Test" Me.ArgumentCount = 0 Me.Flags = Me.kFlagUTF8 OR Me.kFlagDeterministic OR Me.kFlagInnocuous // Text encoding UTF8, function is deterministic and can be cached, function is innocuous as it depends only on parameters End Sub

We can initialize the function and add it to our global functions array with a line like this:

// a test function with subclass functions.Append New TestFunction

Now we can try it by using SQLSelect or SelectSQL to run it:

// try our Test function Dim r As RecordSet = db.SQLSelect("SELECT test()") if r = nil or r.eof then MsgBox "Failed to query version." else MsgBox "Test function returned: "+r.IdxField(1).StringValue End If

Using AddHandler

Instead of a subclass, we can just create an object and use addHandler to connect it to a method:

// and one with handler Dim f As New SQLiteFunctionMBS f.name = "SHA256" f.ArgumentCount = 1 f.Flags = f.kFlagDeterministic OR f.kFlagUTF8 OR f.kFlagInnocuous // Text encoding UTF8, function is deterministic and can be cached, function is innocuous as it depends only on parameters AddHandler f.Perform, AddressOf PerformSHA256 functions.Append f // keep reference

For the SHA256 function we can define it in a method in a module:

Sub PerformSHA256(f as SQLiteFunctionMBS, ArgumentCount as Integer, Arguments() as Variant) #Pragma BackgroundTasks False #Pragma BoundsChecking False #Pragma StackOverflowChecking False Try Dim a As Variant = Arguments(0) Dim mem As MemoryBlock If a IsA MemoryBlock Then mem = a Else mem = a.StringValue End If Dim Hash As MemoryBlock = Crypto.SHA2_256(mem) f.ResultBlob hash Catch r As RuntimeException f.ResultError "Exception: "+r.message End Try End Sub

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:

// try our SHA256 function r = db.SQLSelect("SELECT hex(SHA256(""Hello World""))") If r = Nil Or r.eof Then MsgBox "Failed to query version." Else MsgBox "SHA256 function returned: "+r.IdxField(1).StringValue End If

The function shows the hash A591A6D40BF420404A011733CFB7B190D62C65BF0BCDA32B57B277D9AD9F146E as result.

In SQLiteDatabase

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:

db.LoadExtensions = True

After connect, you can load the extension into the database connection:

// query path for loading extension Dim path As String = InternalSQLiteLibraryMBS.path // Load MBS SQLite plugin as extension Dim sql As String = "SELECT load_extension ('"+path+"', 'sqlite3_extension_init')" db.SQLExecute sql

This loading happens automatically when using SQLDatabaseMBS or SQLConnectionMBS classes with InternalSQLiteLibraryMBS module. For SQLiteDatabase class, you need to load it manually.

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.