Base64 function for SQLite

For the MBS Xojo SQL Plugin 25.5, we add a few extensions and include UUID, Base64 and CSV. The base64 extension allows us to make an base64 encoding in a SQL query as the extension adds the base64() function. It converts BLOB to base64 encoded text or back.

To use it in your Xojo application, you need to set the Base64ExtensionEnabled property in the InternalSQLiteLibraryMBS module to true. Then we enable the internal library with the Use method.

// use internal sqlite library with Base64 extension InternalSQLiteLibraryMBS.Base64ExtensionEnabled = True Call InternalSQLiteLibraryMBS.Use

Now we connect to an in-memory database to try the new function:

Var db As New SQLDatabaseMBS Var path As String = ":memory:" // in-memory database without a file db.DatabaseName = "sqlite:"+path db.Connect

Once we are connected, we can just try the function and encode some value. For demonstration we hard code a BLOB value with the content "hello" using CAST. We pass the BLOB to the base64 function and get back the encoded text.

// make an Base64 Var r As RowSet = db.SelectSQL("SELECT Base64(CAST('hello' AS BLOB))") MessageBox "Base64: " + r.ColumnAt(0).StringValue // shows aABlAGwAbABvAA== r = nil

We create a test table to store labels and blob data in a table.

// create table with Base64 id field db.ExecuteSQL "CREATE TABLE Test(id INTEGER PRIMARY KEY AUTOINCREMENT, Label TEXT, Data BLOB)"

When we fill the table, we can use base64 decoding right in the INSERT statements. This can be directly as text in the parameter or using a parameter passed separately.

// insert value using base64 to convert text to BLOB db.ExecuteSQL "INSERT INTO Test(Label, Data) VALUES ('Bob', base64('aGVsbG8='))" db.ExecuteSQL "INSERT INTO Test(Label, Data) VALUES ('John', base64(?))", "dGVzdA=="

Next we like to query the values back. In the SQL we can query the blob directly and use base64() to get the blob as base64 encoded text.

// now query back the BLOB and also encode it as Base64 again Var rec As RowSet = db.SelectSQL("SELECT Label, Data as DataBlob, base64(Data) as DataText FROM Test") Var list As Listbox = SQLWindow.List While Not rec.AfterLastRow list.AddRow rec.Column("Label"), rec.Column("DataBlob"), rec.Column("DataText") rec.MoveToNextRow Wend rec = Nil

Finally we can dump the database and show the SQL commands:

// dump the database Var lines() As String = InternalSQLiteLibraryMBS.DumpToStrings(db.SQLiteConnectionHandle, "main", "") SQLWindow.TextArea1.Text = string.FromArray(lines, EndOfLine)

Please try the new uuid function and let us know if you need other extensions.

See also InternalSQLiteLibraryMBS module and the SQLite UUID extension blog article.