From Xojo Documentation
New in 2010r4
Used to connect to MySQL Community Edition databases.
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.
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.
fwp.Open() // This call is synchronous
If fwp.isOpen() Then
// Do what you need to do
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.
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:
// 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")
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.
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 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.
This code establishes a connection to MySQL database:
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
Catch error As DatabaseException
MessageBox "Connection failed. Error: " + error.Message