Connecting to a Database

The first step is to connect to the database you wish to use. This is done with Xojo's DatabaseConnection object:

  1. Create a new project in Xojo or open the project you've created in which you wish to connect to a database.

  2. Choose Insert > Database Connection and then choose the database type to which you wish to connect.

  3. A new Database Connection item will appear in your project's Navigator. Its default name will based upon the database type you choose. You can of course rename it in the Inspector.

  4. In the Navigator, expand the Database Connection item.

../../_images/getting_started_navigator.png

Database connection stages

The Database Connection has four connections, one for each stage of your applications development. This provides the ability to have a different database for use with each stage. For example, you may have a database you personally use while developing your app (Development), another for use by your beta testers (Beta) and yet another for use by the ultimate end users when once your app is shipping (Final). You can of course use just one for all stages. That's entirely up to you.

By default, Xojo will automatically switch to the connection for the appropriate stage when you change the Stage Code of your project by going to Build Settings in the Navigator, clicking on Shared Settings and changing the Stage Code in the Inspector. If you do not want your app to switch automatically, click on the Database Connection you created, then in the Inspector change the Stage from Automatic to whichever stage you want it to always use.

To configure a connection for a stage:

  1. Click on the stage (Development for example).

  2. In the Inspector, you then provide the information your app will need to connect to the database. This depends on the type of database. If it's a SQLite database, use the File property to select the SQLite database file you will be using. If it's MySQL, PostgreSQL or ODBC, you will need to provide the IP address, UserName, Password and other information needed to make the connection.

../../_images/getting_started_inspector.png

  1. Click the Connect button in the Database Viewer's toolbar to view the tables and columns of your database.

../../_images/getting_started_table_viewer.png

In the code of your project, you can now access the database using the name of the database connection in your project's Navigator. For example, say you added a connection to a SQLite database and did not change the default connection name. In your application's Opening event, you could make sure you're connected to your database like this:

If SQLiteDatabase1.IsConnected then
  MessageBox("You are connected to the database.")
Else
  MessageBox("Something went wrong. You're not connected to the database.")
End If

Note

Database Connection is not currently supported for Android.

SQLite DatabaseConnection when building your app

When you use a SQLite DatabaseConnection in your project and you build your app, Xojo will copy your SQLite database into the Resources directory/folder of the built app. You should treat this copy of the database file as read only as applications are often in read-only locations on the end user's computer.

If you are planning to store a copy of the database on the user's computer for either read-only or read-write access, you will need to decide where the copy will be stored. This of course depends on whether the user is going to have a single copy of the database or multiple copies where they are treated more like documents. If it's a single copy, at the point when you want to connect to the database (usually when the app is launched), you can use SpecialFolder to determine if the database file can be found at the location you are expecting to find it. The ApplicationData folder (which can be accessed via SpecialFolder) is a common location. Ideally, your app should create a folder with your app's name so that you have a unique folder in which to store the database and potentially other files.

An example is provided that demonstrates this. In Xojo, go to the example projects and then open the "Installing a SQLite Database" in the Databases folder.

How do I connect to a database server from a mobile app?

Most desktop and web apps maintain a connection to the database server while the application is running. On both Android and iOS, if a connection to any server continues too long, the OS will disconnect it as it will quickly drain the battery. Additionally, due to the nature of mobile devices being mobile, Internet connectivity can be somewhat unreliable making continuous connections impractical.

However, you can still connect to a database server! You just have to do it with a web service. The general concept is that you would create a web service and companion API (using Xojo or another tool) that connects to the database server. The mobile app connects to the web service (using URLConnection).

To learn more, watch this video about accessing web services.