MySQLCommunityServer

Class (inherits from Database)

New in 2010r4

Used to connect to MySQL Community Edition databases.

Properties

DatabaseName	SSLAuthorityFolder	SecureAuth
Host	SSLCertificate	Timeout
Password	SSLCipher	UserName
Port	SSLEnabled
SSLAuthority	SSLKey

Methods

AddRow	Connect	SelectSQL
AffectedRowCount	ExecuteSQL	TableColumns
BeginTransaction	LastInsertedRowID	TableIndexes
Close	Prepare	Tables
CommitTransaction	RollbackTransaction

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.

Var 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 RowSet after issuing an SaveRow call (after having previously called EditRow). This means you should not try to modify the contents of a RowSet in a loop. Instead select just the single record you wish to modify.

Threading

SelectSQL and ExecuteSQL 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 RowSet:

Var dbString As String

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

If your database name has characters in it that require special encoding you can try setting the encoding yourself like this:

MyDB.SQLExecute("set names utf8 collate utf8_general_ci")
MyDB.SQLExecute("set character set utf8")
MyDB.SQLExecute("use dbname")

Security

To establish a secure connection, set SSLEnabled 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:

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

// Specify SSL key file
db.SSLKey = New FolderItem("MySQLKeyFile")

// Specify SSL certificate file
db.SSLCertificate = New FolderItem("MySQLCertificateFile")

// Specify SSL authority file
db.SSLAuthority = New FolderItem("MySQLAuthFileFile")

// Specify SSL authority directory
db.SSLAuthorityFolder = New FolderItem("SSLCACertFile")

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

Try
db.Connect
Catch error As DatabaseException
Beep
MessageBox "Connection failed. Error: " + error.Message
End Try