Based on recent conversations with a couple Xojo users, here are a few quick tips for uploading and working with SQLite and MySQL databases on Xojo Cloud.
If you aren’t already familiar with Xojo Cloud, it’s simple, secure, maintenance-free hosting for your Xojo web apps.
SQLite Database Tip
For the first tip, which involves SQLite databases, this is the scenario: Let’s say you added a SQLite database to the project using a Build step and set the proper Folder/Subfolder, and then you connect to it from the Session.Opening event (or any other, for this case) in order to access its tables.
Even if you are running your Web app in local or debug mode (from the IDE) the connection to the SQLite database works, no matter if you’re typing the database file name in either upper or lowercase. Remember, when you are deploying the database to Xojo Cloud, you need to pass along the database name using the proper lowercase and uppercase characters in its original name.
Let’s suposse that the original database name on disk is “MyPrettyDatabase.sqlite”, then you’re using the following code in order to connect to it:
#if DebugBuild then
f=SpecialFolder.Desktop.Child("myprettydatabase.sqlite")
#else
f=specialfolder.documents.child("myprettydatabase.sqlite")
#EndIf
BBDD.DatabaseFile=f
Try
BBDD.Connect
catch e as IODatabaseException
End Try
In this case, when running the web app from your computer everything will work fine (because most desktop operating systems use a case-insensitive file system, although Linux is often case-sensitive); but if you deploy the web app to Xojo Cloud you’ll find yourself with a database that does not connect. Fixing this is as simple as typing the string representing the database filename observing the uppercase characters existing in its original name:
#if DebugBuild then
f=SpecialFolder.Desktop.Child("myprettydatabase.sqlite")
#else
f=specialfolder.documents.child("MyPrettyDatabase.sqlite")
#EndIf
BBDD.DatabaseFile=f
Try
BBDD.Connect
catch e as DatabaseException
End Try
SQLite Tip: Xojo 2020r1 updated to SQLite 3.31.1, which adds some cool new features!
MySQL/MariaDB Database Tip
The second problem we’re solving involves the connection with MySQL/MariaDB databases hosted on a remote server from your Xojo Cloud app.
Although connecting to a remote MySQL database is well documented here, let’s review. If you need to establish this kind of connection, remember to open the Xojo Cloud firewall port first.
For example, something like this code snippet will let the web app work when deployed on Xojo Cloud and when you are doing a local test running it from the IDE (debug mode):
#If TargetXojoCloud
Var fwp As New XojoCloud.FirewallPort(3306, XojoCloud.FirewallPort.Direction.Outgoing)
fwp.Open
If fwp.isOpen Then
connectDatabase
End If
#Else
ConnectDatabase
#EndIf
Where, in this case, ConnectDatabase is the method in charge of doing the real connection setting the usual parameters agains the MySQLCommunityServer instance.