MySQLCommunityServer

Class (inherits from Database)

New in 2010r4

Used to connect to MySQL Community Edition databases.

Properties

DatabaseName	Port	SSLMode
Error	SSLAuthority	SecureAuth
ErrorCode	SSLAuthorityDirectory	Timeout
ErrorMessage	SSLCertificate	UserName
Host	SSLCipher
Password	SSLKey

Methods

Close	GetInsertID	SQLExecute
Commit	IndexSchema	SQLSelect
Connect	InsertRecord	TableSchema
FieldSchema	Prepare
GetAffectedRows	Rollback

Notes

In order to use this class, you must have the MySQLCommunityPlugin database plug-in in your plugins folder. This class connects to the MySQL Community Edition database server. It is the open-source version of MySQL that is freely available. Please refer to the MySQL products page which clarifies the differences between the various editions of MySQL.

The MySQLCommunityPlugin database uses a GPL license, which may have ramifications for any software that uses it. Please be sure to review the GPL carefully. The plugin itself, GPL license and the full source code for the MySQLCommunityPlugin is included with your installation.

MySQL Community Edition server is also GPL, for more information refer to the MySQL Community Edition page.

When the plug-in is installed, this class becomes available and you can also added a MySQL Community Server database connection directly to the project.

MySQLCommunityServer supports only the MoveNext RecordSet navigation method.

Also refer to the official MySQL documentation.

Xojo Cloud

To access MySQL databases from web apps running on Xojo Cloud, you will first have to use the FirewallPort class to open the port used to connect to MySQL, which is usually 3306.

Dim fwp As New XojoCloud.FirewallPort(3306, _
XojoCloud.FirewallPort.Direction.Outgoing)
fwp.Open() // This call is synchronous
If fwp.isOpen() Then
// Do what you need to do
End If

RecordSet Updates

MySQL cannot guarantee the contents of a RecordSet after issuing an Update call (after having previously called Edit). This means you should not try to modify the contents of a RecordSet in a loop. Instead select just the single record you wish to modify.

Threading

SQLSelect and SQLExecute statements do not block when called from within Threads.

Encoding

Note that the MySQL plugin returns strings without a specified encoding. If this is causing problems, you will need to define the encoding on the results returned from the RecordSet:

Dim dbString As String

// rs is a RecordSet returned by SQLSelect
dbString = rs.Field("ColumnName").StringValue.DefineEncoding(Encodings.UTF8)

Security

To establish a secure connection, set MySQLCommunityServer.SSLMode to True and assign the other SSL properties. Depending on the server setup, you may need to set some or all of those properties. For more information, see this page.

Linux Notes

There may be patches that you need to install for Linux. MySQL compiles the libraries using the Intel compiler and this sometimes requires additional support libraries from MySQL. They are available at http://dev.mysql.com/downloads/os-linux.html.

MariaDB

MariaDB is a "drop-in" replacement for MySQL. You can read more about it at their web site: http://mariadb.org/

The MySQLCommunityServer plugin can be used to connect to MariaDB.

Sample Code

This code establishes a connection to MySQL database:

Dim db As New MySQLCommunityServer
db.Host = "192.168.1.172"
db.Port = 3306
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
If db.Connect Then
// Use the database
Else
// Connection error
MsgBox(db.ErrorMessage)
End If 

You can also securely connect to MySQL using the SSLMode and associated properties:

Dim 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

// Specify SSL key file
Dim keyFile As FolderItem
keyFile = GetFolderItem("MySQLKeyFile")
db.SSLKey = keyFile

// Specify SSL certificate file
Dim certFile As FolderItem
certFile = GetFolderItem("MySQLCertificateFile")
db.SSLCertificate = certFile

// Specify SSL authority file
Dim authFile As FolderItem
authFile = GetFolderItem("MySQLAuthFileFile")
db.SSLAuthority = authFile

// Specify SSL authority directory
Dim authPath As FolderItem
authPath = GetFolderItem("SSLCACertFile")
db.SSLAuthorityDirectory = authPath

// Specify SSL cipher
Dim cipher As String
cipher = "DHE-RSA-AES256-SHA"
db.SSLCipher = cipher

If db.Connect Then
// Use the database
End If 