Platforms to show: All Mac Windows Linux Cross-Platform
/SQL/Xojo SQLite custom function
You find this example project in your Plugins Download as a Xojo project file within the examples folder: /SQL/Xojo SQLite custom function
This example is the version from Tue, 29th Jan 2024.
Project "Xojo SQLite custom function.xojo_binary_project"
Class App Inherits Application
Const kEditClear = "&Löschen"
Const kFileQuit = "Beenden"
Const kFileQuitShortcut = ""
EventHandler Sub Open()
// a test function with subclass
functions.Append New TestFunction
// 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
// this test is with Xojo's SQLite version!
Dim db As New SQLiteDatabase
db.LoadExtensions = True
// no database name, so in-memory database
If db.Connect Then
// 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
// 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
// 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
// should show A591A6D40BF420404A011733CFB7B190D62C65BF0BCDA32B57B277D9AD9F146E
end if
End EventHandler
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
Property Functions() As SQLiteFunctionMBS
End Class
MenuBar MenuBar1
MenuItem FileMenu = "&Ablage"
MenuItem FileQuit = "#App.kFileQuit"
MenuItem EditMenu = "&Bearbeiten"
MenuItem EditUndo = "&Rückgängig"
MenuItem UntitledMenu1 = "-"
MenuItem EditCut = "&Ausschneiden"
MenuItem EditCopy = "&Kopieren"
MenuItem EditPaste = "&Einfügen"
MenuItem EditClear = "#App.kEditClear"
MenuItem UntitledMenu0 = "-"
MenuItem EditSelectAll = "&Alles auswählen"
End MenuBar
Sign
End Sign
Class TestFunction Inherits SQLiteFunctionMBS
EventHandler Sub Perform(ArgumentCount as Integer, Arguments() as Variant)
#Pragma BackgroundTasks False
#Pragma BoundsChecking False
#Pragma StackOverflowChecking False
ResultText "Hello World"
End EventHandler
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
End Class
End Project
The items on this page are in the following plugins: MBS SQL Plugin.