MBS SQL Plugin Tips and Tricks

As you may know we have our own MBS Xojo SQL Plugin for Xojo, which is an alternative to the built-in database classes in Xojo. Let us show you how we do a few things with our plugin, which you may not do like this with the official database plugins in Xojo.

The MBS Xojo SQL Plugin is a Xojo plugin for accessing multiple SQL databases (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). It uses native APIs of target DBMS so applications developed with this plugin run swiftly and efficiently. The product also provides a low-level interface that allows developers to access database-specific features. By encapsulating a vendor’s API, the plugin acts as middleware and delivers database portability.

Trace Log

Our plugin has a Trace event in SQLDatabaseMBS and SQLConnectionMBS classes. You may decide to either subclass these classes and implement the event. Or you use addHandler to connect the event to your method to log SQL requests. The trace event provides all SQL run by the plugin and is at a low level, so it catches also the insert statements made by AddRow or InsertRecord methods.

e.g. like this sample implementation, which uses a TraceEnabled boolean property in SQLDatabaseMBS subclass to toggle whether we log SQL for debugging:

Sub Trace(traceInfo As Integer, SQL As String, Command As SQLCommandMBS)
	If TraceEnabled Then
		System.DebugLog SQL
	End If
End Sub

Unicode

The MBS Plugin does fully do unicode. For most database drivers, we use unicode APIs directly, so we skip conversion steps. You may have seen SQL commands like “SET NAMES UTF8” or the use of “collate utf8_general_ci” in SQL queries to enable UTF8 processing of characters. We don’t need that. For our plugin reading a field will give you an unicode string (mostly UTF-8) and we take strings you pass to the plugin and convert them to the encoding needed. So no need for DefineEncoding() or ConvertEncoding() like his:

Function StringValueFixEncoding(extends f as DatabaseField) As string
	Dim value As String = f.StringValue
	
	If value.Encoding = Nil Then
		value = DefineEncoding(value, encodings.UTF8)
	End If
	
	Return value
End Function

When I recently moved a project from MySQLCommunityServer class to SQLDatabaseMBS class, I removed a ton of unnecessary calls to handle encodings. This is now all automatically and we save a lot of extra lines of code.

Microsoft SQL Server

We can connect to the Microsoft SQL Servers on all platforms. Here is a sample code, which connects on Windows to SQL Server. User name and password are provided in code. The database is named “crm” and the server is named “CRM”. This uses named instances on Windows, so we don’t need to hard code the IP address. We added the connection parameter “MARS_Connection” to tell the driver, that we may want to have more than one current result set.

Sub Connect()
	Dim m As New SQLDatabaseMBS
	
	m.Option("UseAPI") = "ODBC"
	m.Client = SQLConnectionMBS.kSQLServerClient
	m.UserName = "xxx"
	m.Password = "yyy"
	m.DatabaseName = "crm@CRM;MARS_Connection=yes"
	
	If m.Connect Then
		Return m
	Else
		MsgBox "Can't connect to the SQL database."+EndOfLine+EndOfLine+m.ErrorMessage
		Quit
	end if
End Sub

Run stored procedure

You can use SQLCommandMBS or just SQLPreparedStatementMBS class to run a stored procedure. The SQL you pass is the name of the stored procedure like “dbo.get_new_orders”. The plugin will ask the database for the parameters and create SQLParamMBS objects for them. Doing the processing on the server side in the procedure allows you to change the procedure later without recompiling your application.

	 Dim p As SQLPreparedStatementMBS = app.db.Prepare("dbo.get_new_orders")
	p.Bind("@Name", Name)
	dim r as RecordSet = p.SQLSelect

As you see, we pass the parameter value with Bind for Microsoft SQL with @ as prefix. The type is automatically determinate by our plugin, so we don’t need BindType calls. But if you need, you can do them of course to choose the type, if you need to explicitly pass a specific type:

p.BindType(FahrzeugBindIndex, SQLPreparedStatementMBS.kTypeString)

Our plugin first checks what type you used with BindType. If none is given, we check what the parameter needs. If the SQL database doesn’t tell us the requested type, we check what type your value has.

Customize SQL

If your app supports multiple SQL backends, e.g. MySQL and Microsoft SQL, you have little differences in the SQL commands. When you connect, you know what type of database you use. It may be handy to put various things into a global variable.

e.g. you may have two global properties named PreLimit and PostLimit. For Microsoft SQL Server the PreLimit is set to " TOP 100 ", while for PostgreSQL and MySQL you set the PostLimit variable to " LIMIT 100 ". Now when you build the SQL and you like to limit the result, you can use these properties in the SQL:

Dim sql As String = "SELECT "+PreLimit+" * FROM Clients WHERE "+Condition+PostLimit

It can be tricky to build SQL statements, that work with two databases, but we can usually handle that with a few extra IFs.

Please try our MBS Xojo SQL Plugin and let us know if you have questions or find issues.

3 Likes