While Xojo has always supported adding database connections, the old method was far from ideal. Let’s delve into the improved approach using the new Database Connection project items and explore some recent additions to our database API.
Out With The Old
The old database connections were quite limiting, requiring you to create a new connection and re-enter all the parameters whenever one changed. With our new Database Connection items, you can now easily update specific connection parameters directly in the inspector.
Additionally, you can now choose whether the item will auto-connect or if you prefer to connect to it later in your code.
Connecting to a Specific Database
There are times when you need to connect to a database for debugging and testing, but for the final builds, you want to connect to your production database/server. Setting this up correctly with the old database connection method required creating two separate connection items—one for debug and one for release. With the new Database Connection items, this process is much simpler and fits more easily into the build process. You now have several new Database Connection subitems that can be customized based on the stage of your build. These subitems are linked to the Stage Code in your Shared Build Settings, ensuring that when you build a final release, your database connection uses the correct settings automatically.
Additional Database Features
In addition to this change, there are two new database APIs that may be useful to you:
- A new method, IsConnected, has been added to the SQLiteDatabase, ODBCDatabase, PostgreSQLDatabase, and MySQLCommunityServer classes. This method returns true if the connection is still active, and false if it is not.
- We’ve enhanced Database.AddRow with a new signature that returns the unique ID of the newly inserted row. This feature is supported for SQLiteDatabase, ODBCDatabase, PostgreSQLDatabase, and MySQLCommunityServer classes. It is recommended to use this new API instead of the deprecated SQLiteDatabase.LastRowID and MySQLCommunityServer.LastInsertedRowID
Let’s explore how the new Database.AddRow feature can assist you. Previously, only SQLiteDatabase and MySQLCommunityServer had APIs to retrieve the unique ID of the last inserted row. Now, PostgreSQLDatabase and ODBCDatabase also have this capability by using the new Database.AddRow(tableName As String, row As DatabaseRow, idColumnName As String = "") As Integer
The new aspect of this signature is the idColumnName parameter and the return value. For databases that support the SQL RETURNING clause, the idColumnName specifies the column whose value will be returned. For databases that do not support the RETURNING clause, we will use the suitable method for each specific database to return a unique value.
We recognize the significance of databases in your Xojo apps. So with these updates to database connectivity for your desktop, web and console apps, and the addition of new features, we aim to make your database programming easier and more efficient.
William Yu grew up in Canada learning to program BASIC on a Vic-20. He is Xojo’s resident Windows and Linux engineer, among his many other skills. Some may say he has joined the dark side here in the USA, but he will always be a Canadian at heart.