Base64 function for SQLite in MBS SQL Plugin

Extras
1

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.

1 Like