SQLDatabase sample

MBS Xojo SQL Plugin connects to CubeSQL, Centura SQLBase, DB2, DuckDB, Firebird, Informix, InterBase, MariaDB, Microsoft Access, Microsoft SQL Server, MySQL, ODBC, Oracle Database Server, PostgreSQL, SQL Anywhere, SQLite, SQLCipher and Sybase)

We recently got an example project for SQLite with MBS Xojo SQL Plugin and let's explain a bit on how it works.

First you decide which database client library to use. For SQLite this can be a sqlite3.dll/dylib/so library to load or you use the built-in library inside our plugin. The one in InternalSQLiteLibraryMBS module includes various extensions and encryption support, so we can activate it with the Use method.

// use internal sqlite library call InternalSQLiteLibraryMBS.Use

Create a database connection object with SQLDatabaseMBS class. That is our MBS class subclasses from Database, so it supports the database interface in Xojo. But alternatively we also have the SQLConnectionMBS class, which implements the our own interface and works with Xojo Lite. Which side you use doesn't matter much as both can do about the same things.

var db as new SQLDatabaseMBS // where is the library? 'db.Option(SQLConnectionMBS.kOptionLibrarySQLite) = "/usr/lib/libsqlite3.0.dylib"

Next you need a connection string and potential credentials. For SQLite, the connection string is the path to the database file, usually a native path. For an in-memory database, you can use the path ":memory:" for SQLite. This allows you to have a very fast database, but it is forgotten when the app quits. You can achieve nearly the same with a file based database and setting a huge cache, e.g. "PRAGMA cache_size = 2000000", which is a 2 GB maximum cache size. Then data is preserved and still it mostly operates in memory.

Our internal SQLite library includes the following extensions:

JSON1, the extension to provide JSON functions
FTS5, the full text search extension in version 5
R*Tree index extension
SOUNDEX function
SQL math functions
Geopoly extension
ICU extension for unicode handling
The RBU Extension
SQLite Encryption Extension

You can turn on these SQLite extensions at runtime with MBS Plugin:

UUID extension
Base64 extension
CSV extension

For our plugin which connects to 15 different types of databases, you need to inform the plugin which one you like. Either you set the client property to one of the constants or for SQLDatabaseMBS class, you specify it as a prefix of the connection string. For SQLite we include "SQLite:" as prefix. Then we connect with the Connect Method or function. The method raises an exception while the function returns false on failure and sets ErrorMessage property.

// connect to database // in this example it is SQLite, // but can also be Sybase, Oracle, Informix, DB2, SQLServer, // InterBase, MySQL, SQLBase and ODBC var path as string path = "/tmp/test.db" // put the database in the temporary folder // or in-memory? path = ":memory:" db.DatabaseName = "sqlite:"+path db.Connect System.DebugLog "We are connected!"

Once connected, we can query the version for SQLite with a SELECT statement. This will give us a record set with one record with one field containing the version number. Which we could also get with InternalSQLiteLibraryMBS' Version function.

// check SQLite version Var r As RecordSet = db.SQLSelect("select sqlite_version()") If r = Nil Or r.eof Then System.DebugLog "Failed to query version." Else System.DebugLog "Version: "+r.IdxField(1).StringValue End If

Next we can execute a SQL command to create a table. SQLite knows the "IF NOT EXISTS" term, so it doesn't give an error, if we created the table before.

// create table db.SQLExecute "CREATE TABLE IF NOT EXISTS test_tbl(fid integer, fvarchar20 varchar(20), fblob blob)"

We can insert a record with a SQL statement and put the data in the statement. That is excellent to pre-fill a database with some basic data, that doesn't need to be adjusted.

// insert value db.SQLExecute "Insert into test_tbl(fid, fvarchar20) values (1, 'Some string (1)')"

If data is coming from the user like text fields or external sources, we need to use prepared statements to avoid SQL injections. Otherwise someone may type text in your field and delete your table, e.g.

"; DROP TABLE test_tbl; --

First quote and semicolon end the current command. Then the DROP TABLE command would delete a table. And the two dashes make the rest of the line a comment.

We avoid that all with the prepared statement and pass data to fill parameters in the statements. You can provide types, but otherwise the automatic mode looks on the variant type.

// insert with prepared statements by index Var ps As SQLPreparedStatementMBS = db.Prepare("Insert into test_tbl(fid, fvarchar20) values(:1, :2)") ps.BindType(0, ps.kTypeLong) ps.BindType(1, ps.kTypeString) ps.SQLExecute 12345, "Hello World by index"

Instead of referring to parameters by ID or index, we can use named parameters which is much more secure. Especially if you later add more parameters, you can't mess up the indexes.

// by name Var sql As String = "Insert into test_tbl(fid, fvarchar20) values(:fid, :fvarchar20)" Var p As SQLPreparedStatementMBS = db.Prepare(sql) p.BindType("fid", p.kTypeLong) p.BindType("fvarchar20", p.kTypeString) p.Bind("fid", 2345) p.Bind("fvarchar20", "Hello World by name") p.SQLExecute

The prepared statement for inserting the record is automatically made for you if you use DatabaseRecord or DatabaseRow classes. We can do both with our plugin. So insert one with DatabaseRecord first:

// insert with DatabaseRecord var d As New DatabaseRecord d.IntegerColumn("fid")=2 d.Column("fvarchar20")="test insert" d.BlobColumn("fblob")="Just a test" db.InsertRecord("test_tbl", d) If db.Error Then MsgBox db.ErrorMessage End If

And the same for the newer DatabaseRow:

// insert with DatabaseRow var NewRow As New DatabaseRow NewRow.Column("fid")=3 NewRow.Column("fvarchar20")="another insert" NewRow.Column("fblob")="Hello" db.AddRow("test_tbl", NewRow)

Technically both are the same, just different syntax sugar.

Let us query data with the RowSet class and loop over the rows and add them to the listbox:

// now query with RowSet Var r As RowSet = db.SelectSQL("Select fid, fvarchar20 from test_tbl") // fetch results row by row and print results Var list As listbox = MainWindow.List While Not r.AfterLastRow Var fid As Integer = r.Column("fid").IntegerValue Var fvarchar20 As String = r.Column("fvarchar20").StringValue list.AddRow Str(fid), fvarchar20 r.MoveToNextRow Wend

Do the same with older RecordSet. Yes, it's the old API, but we have still a ton of code with that and so we support it in the plugin.

// now query with RecordSet var r As RecordSet = db.SQLSelect("Select fid, fvarchar20 from test_tbl") // fetch results row by row and print results var list as listbox = MainWindow.List While Not r.EOF var fid As Integer = r.Field("fid").IntegerValue var fvarchar20 As String = r.Field("fvarchar20").StringValue list.AddRow Str(fid), fvarchar20 r.MoveNext Wend

For SQLDatabaseMBS and SQLConnectionMBS classes, we automatically close the connection when you loose the last reference. But keeping a SQLCommandMBS, RecordSet or RowSet around may delay that. We can only release the connection once the last object is released. You can explicitly close the connection, but then please don't use anything that may try to talk to the database as that may fail with an error or exception.

// Disconnect is optional // autodisconnect will ocur in destructor if needed db.close System.DebugLog "We are disconnected!"

Enjoy our MBS Xojo SQL Plugin and connect to all the databases.