UserGuide

MySQL Database

From Xojo Documentation

MySQL is a powerful, cross-platform, open-source database server. To use it, you need to ensure the MySQLCommunityServerPlugin file is in the Plugins folder (it is there by default). The plugin supports connecting to MySQL Community Edition from Windows, Mac and Linux.

You can connect to MySQL from Desktop, Web and Console projects, but not iOS projects.

You can learn more about MySQL at their web site: www.MySQL.com]

Licensing

MySQL’s licensing is considerably more complex than licensing for other database servers. For more information about its licensing options, refer to their web site: MySQL Licensing

Connecting to MySQL

To connect to MySQL, you need to have a MySQL server installed on either your computer or an accessible server. You’ll need to know several things about this installation, including:

  • The Host IP address or name
  • The Port being used (usually 3306)
  • The name of the database on the server
  • The username to use to connect to the server
  • The password to use to connect to the server

With this information, you can connect to the database on the server using the MySQLCommunityServer class:

Var db As New MySQLCommunityServer
db.Host = "192.168.1.172"
db.Port = 3306
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
Try
db.Connect
// Use the database
Catch error As DatabaseException
// Connection error
MessageBox(error.Message)
End If

Secure Connections

You can also connect to a MySQL database using SSL for a secure connection. You do this using the SSLMode property (and optionally other SSL properties) to specify the type of secure connection to use:

Var db As New MySQLCommunityServer
db.Host = "192.168.1.172"
db.Port = 3306
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
db.SSLMode = True

Var keyFile As FolderItem
keyFile = New FolderItem("MySQLKeyFile")
db.SSLKey = keyFile

Var certFile As FolderItem
certFile = New FolderItem("MySQLCertificateFile")
db.SSLCertificate = certFile

Var authFile As FolderItem
authFile = New FolderItem("MySQLAuthFileFile")
db.SSLAuthority = authFile

Var authPath As FolderItem
authPath = New FolderItem("SSLCACertFile")
db.SSLAuthorityDirectory = authPath

Var cipher As String
cipher = "DHE-RSA-AES256-SHA"
db.SSLCipher = cipher

Try
db.Connect
// Use the database
Catch error As DatabaseException
MessageBox(error.Message)
End Try

Creating a Table

This SQL creates the Team table used in previous examples:

CREATE TABLE Team (ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Name TEXT, Coach TEXT, City TEXT);

In place of the TEXT data type, which allows an unlimited length string, you might also use the VARCHAR data type which allows you to specify a maximum size for the string:

CREATE TABLE Team (ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(100), Coach VARCHAR(100), City VARCHAR(100));

Auto-Incrementing Primary Keys

If a table has the AUTO_INCREMENT attribute assigned to a primary key, then that column auto-increments when a row is added to the table. When you INSERT data into a table with a primary key, you omit the primary key from the INSERT SQL:

INSERT INTO Team (Name) VALUES ('Seagulls');

After adding a row to the database, you can get the value of the last primary key value by calling the GetInsertID method:

Var lastValue As Integer lastValue = db.LastInsertedRowID

Example Projects

  • Examples/Database/MySQL/MySQLExample

See Also

MySQLCommunityServer class; UserGuide:Framework, UserGuide:Database Overview, UserGuide:Database Operations topics