Skip to content

Database Connectivity with Xojo

Published June 19, 2013 by Paul Lefebvre

Xojo can connect to a wide variety of databases, including: SQLite, PostgreSQL, MySQL, Oracle, Microsoft SQL Server and pretty much anything else using ODBC. A must-read for anyone building their first database application.

SQLite

SQLite is built-in to Xojo. As of 2015r4, Xojo uses SQLite 3.9.2 (SQLite 3.8.8 is used on iOS). To use SQLite with Xojo, you use the SQLiteDatabase class A similar SQLiteDatabase class is also used for iOS. SQLite is referred to as an “embedded” database because the database engine is embedded into your application. SQLite is a great choice for local databases used by your desktop apps. SQLite is so useful that it is sometimes used to save app preferences and even as a custom file format for some apps! SQLite also works well with web apps that primarily display data and have a low volume of concurrent database writes.

SQLExample.png

Connecting to a SQLite database is easy:

Dim dbFile As FolderItem = GetFolderItem("MyDatabase.sqlite")
Dim db As New SQLiteDatabase
db.DatabaseFile = dbFile
If db.Connect Then
  MsgBox("Connected!")
Else
  MsgBox("Error: " + db.ErrorMessage)
End If

For more information on using SQLite, refer to its entries in the Reference Guide and User Guide:

Or watch one of our webinars:

Database Servers

The other database options are fall under the “server” category. For Xojo, this means two things:

  1. You’ll need to make sure the appropriate plugin is coped to your Plugins folder.
  2. You’ll need a Desktop, Web, Pro or Enterprise version of Xojo.

A database server is much more powerful than SQLite, but also more complicated. The big advantage of a database server is that they are designed for multiple concurrent users. Xojo has built-in support for these database servers: PostgreSQL, MySQL, Oracle and Microsoft SQL Server.

Although you need to have a database server installed somewhere that is accessible to your app, connecting to one is just as easy as it is with SQLite. This example connects to a PostgreSQL database server:

Dim db As New PostgreSQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "myDatabase"
db.Username = "Bob"
db.Password = "fdf#$uy@"
If db.Connect Then
  //proceed with database operations
Else
  MsgBox("The connection failed.")
End If

Connecting to other database servers is similar. For more information refer to the topics in the Reference Guide, User Guide and our webinars:

ODBC

ODBC is not really a database. It is a database connection protocol. It can be used to connect to database servers for which we do not provide a plugin, such an IBM iSeries (AS/400) database or Microsoft Access. In order to use ODBC to connect to a database you first have to make sure the ODBC driver is installed on your system. Then you usually need to configure it using the ODBC Control Panel or ODBC Adminitrator Tool for your system.

ODBC Control Panel on Windows 8This example prompts you to choose a previously configured ODBC connection (called a DataSourceName or DSN) and attempts to connect to it:

Dim db As New ODBCDatabase
db.DataSource = ""
 // Force user to choose a DSN
If db.Connect Then
 //proceed with database operations
Else
 MsgBox("The connection failed.")
End If

View ODBC in the Reference Guide, User Guide and the ODBC webinar for more information:

Data Access

Regardless of how you connected to the database, accessing the data is achieved the same way. You use the SQLSelect method to query the data and the SQLExecute method to run commands. When you query data, the results are stored in a RecordSet. You can interate through the RecordSet to get the data to display or modify it. These and other database topics are covered in the User Guide.

Working with database using Xojo is easy and consistent. So if you are coming from another tool or are simply looking to make your next great app using Xojo, rest assurred that you can connect to the database you need.

(Updated March 10, 2016)

Published in Database