MySQLCommunityServer

From Xojo Documentation

Class (inherits from Database)


New in 2010r4

Used to connect to MySQL Community Edition databases.

Properties
Port SSLCertificate SSLKey
SSLAuthority SSLCipher SecureAuth
SSLAuthorityFolder SSLEnabled Timeout
Methods
AffectedRowCount LastInsertedRowID

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 Update call (after having previously called Edit). 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)

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

See Also

Database Class, MySQLPreparedStatement, PreparedSQLStatement, PostgreSQLDatabase, MSSQLServerDatabase, RowSet classes