Easily Switch Databases with Xojo API 2.0

Xojo has built in database classes for accessing SQLite, Postgres, MySQL, Microsoft SQL Server, Oracle & ODBC. All these classes are sub classes of the Database class which means aside from connection details the same code can be used to access any of these engines. Prior to API 2.0 when using prepared statements, it was necessary to use the bind types specific to that engine. API 2.0 sets the bind types for you and that means you can switch engines by just changing the Super of your database class and updating the connection settings.

When I’m working with a project that will use a database the first thing I do is create my own database class. I do this by inserting a class, giving it a name and setting its super to the engine I am going to use, for example MSSQLServerDatabase. Next, I will add a method called Constructor and make it private – I only want to be able to create an instance of this class by passing the connection parameters required by the engine. Then, I will add a second constructor that will have the connection parameters for the chosen engine. For MSSQLServerDatabases a Host, DatabaseName and optionally a Username and Password.

Public Sub Constructor(Host As String, DatabaseName As String, Optional Username As String, Optional Password As String)
  Me.Host = Host
  Me.DatabaseName = DatabaseName
  Me.UserName = Username
  Me.Password = Password
  
  Connect()
  EddiesDatabase.VersionControl(me)
End Sub

Username and Password are optional for MSSQLServerDatabases because when they are empty, Network Credentials are used.

Then I will add a Shared Method VersionControl that takes a parameter db.

Public Shared Sub VersionControl(db As Database)
  Const SelectVersion = "SELECT version FROM versioncontrol WHERE id = 1;"
  
  Var VersionRow As RowSet
  
  #Pragma BreakOnExceptions Off
  Try
    VersionRow = db.SelectSQL(SelectVersion)
  Catch DatabaseException
    // This will be because the versioncontrol table doesn't exist
    // So Create & Populate the table
    db.ExecuteSQL("CREATE TABLE versioncontrol (id INTEGER NOT NULL PRIMARY KEY, version INTEGER NOT NULL);")
    db.ExecuteSQL("INSERT INTO versioncontrol (id, version) VALUES (1, 0);")
    // And get the newly inserted row
    VersionRow = db.SelectSQL(SelectVersion)
  End Try
  #Pragma BreakOnExceptions Default
  
  Do
    Select Case VersionRow.Column("version").IntegerValue
    Case 0
      db.BeginTransaction
      db.ExecuteSQL("CREATE TABLE Customers (" _
      + "ID INTEGER NOT NULL, " _
      + "FirstName VARCHAR(30), " _
      + "LastName VARCHAR(30), " _
      + "Address VARCHAR(200), " _
      + "City VARCHAR(30), " _
      + "State VARCHAR(2), " _
      + "Zip VARCHAR(30), " _
      + "Phone VARCHAR(30), " _
      + "Email VARCHAR(100), " _
      + "Photo IMAGE, " _
      + "Taxable INTEGER, " _
      + "PRIMARY KEY(ID)" _
      + ");")
      db.ExecuteSQL("CREATE TABLE InvoiceItems (" _
      + "ID INTEGER NOT NULL, " _
      + "InvoiceNo INTEGER, " _
      + "ProductCode VARCHAR(10), " _
      + "Quantity INTEGER, " _
      + "PRIMARY KEY(ID)" _
      + ");")
      db.ExecuteSQL("CREATE TABLE Invoices (" _
      + "InvoiceNo INTEGER NOT NULL, " _
      + "CustomerID INTEGER, " _
      + "InvoiceDate DATE, " _
      + "InvoiceAmount FLOAT, " _
      + "PRIMARY KEY(InvoiceNo)" _
      + ");")
      db.ExecuteSQL("CREATE TABLE Products (" _
      + "Code VARCHAR(10) NOT NULL, " _
      + "Name VARCHAR(30), " _
      + "Price FLOAT, " _
      + "PRIMARY KEY(Code)" _
      + ");")
      db.CommitTransaction
    Case 1
      // Insert Schema Updates & SQL Commands before this line and add a New Case statement below this line
      Exit Do
    Case Else
      Var err As New RuntimeException
      err.Message = "Please upgrade your application to use this database"
      Raise err
      Quit()
    End Select
    db.ExecuteSQL("UPDATE versioncontrol SET version = version + 1 WHERE id = 1;")
    VersionRow = db.SelectSQL(SelectVersion)
  Loop
End Sub

This code creates Eddie’s Electronics tables in the Database and maintains the database schema or structure. This method is shared so it can be called from other objects.

Let us say, for example, I want to add the ability to use SQLite as the database engine. I would create the class the same way but name it EddiesSQLiteDatabase, setting its super to SQLiteDatabase, add the empty constructor making it private and add the second constructor passing the folder item for the database file and optionally the encryption key. This code will either connect to the existing database file or create a new file.

Public Sub Constructor(DatabaseFile As FolderItem, Optional EncryptionKey As String)
  // Calling the overridden superclass constructor.
  Super.Constructor()
  
  Me.DatabaseFile = DatabaseFile
  
  If DatabaseFile.Exists Then
    If EncryptionKey > "" Then
      Me.EncryptionKey = EncryptionKey
    End If
    Connect()
  Else
    CreateDatabase()
    If EncryptionKey > "" Then
      Encrypt(EncryptionKey)
    End If
  End If
  
  EddiesDatabase.VersionControl(me)
  
End Sub

You will notice I am calling the shared method EddiesDatabase.VersionControl on the EddiesDatabase class for this engine too.

The big issue with doing this in the classic framework was the use of prepared statements. I would need to update every bind type for every prepared statement in the code to allow for both engines. Of course while the Xojo code remains 100% compatible, I still need to test to ensure all the SQL statements work with the new engine and make adjustments where necessary, but that is an awful lot less work.

Wayne Golding has been a Xojo developer since 2005 and is a Xojo MVP. He operates the IT Company Axis Direct Ltd which primarily develops applications using Xojo that integrate with Xero www.xero.com. Wayne’s hobby is robotics where he uses Xojo to build applications for his Raspberry Pi, often implementing IoT for remote control.