Class

MySQLCommunityServer

Description

Used to connect to MySQL Community Edition databases.

Property descriptions

MySQLCommunityServer.DatabaseName

DatabaseName As String

The name of the database to open.

The DatabaseName is typically used to identify the specific database to use on the server.

This code connects to a database called "BaseballLeague":

Var db As New MySQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
Try
  db.Connect
  ' Use the database
Catch error As DatabaseException
  ' DB Connection error
  MessageBox(error.Message)
End Try

MySQLCommunityServer.Host

Host As String

The database host name or IP address of the database server.

This examples connects to a database called "BaseballLeague":

Var db As New MySQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
Try
  db.Connect
  ' Use the database
Catch error As DatabaseException
  ' DB Connection error
  MessageBox(error.Message)
End Try

MySQLCommunityServer.Password

Password As String

The password that is required for access to the database. Typically used in conjunction with UserName.

This examples connects to a database called "BaseballLeague":

Var db As New MySQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
Try
  db.Connect
  ' Use the database
Catch error As DatabaseException
  ' DB Connection error
  MessageBox(error.Message)
End Try

MySQLCommunityServer.Port

Port As Integer

The port to use to connect to the MySQL Community Server database. The default is 3306.

' db is a MySQLCommunityServer
db.Port = 3400

MySQLCommunityServer.SecureAuth

SecureAuth As Boolean

This property allows users to authenticate using old password hashes (i.e. pre-4.1.1 authentication protocol) if needed. It is True by default.

MySQLCommunityServer.SSLAuthority

SSLAuthority As FolderItem

Specifies the authority certificate file. This property is ignored if an SSL connection is not made.

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

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
 MessageBox("Connection failed. Error: " + error.Message)
End Try

MySQLCommunityServer.SSLAuthorityFolder

SSLAuthorityFolder As FolderItem

The path to a folder that contains trusted SSL CA certificates in PEM format. This property is ignored if an SSL connection is not made.

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

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
 System.Beep
 MessageBox("Connection failed. Error: " + error.Message)
End Try

MySQLCommunityServer.SSLCertificate

SSLCertificate As FolderItem

Specifies the file for the client SSL certificate. This property is ignored if an SSL connection is not made.

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

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
 MessageBox("Connection failed. Error: " + error.Message)
End Try

MySQLCommunityServer.SSLCipher

SSLCipher As String

A list of permissible ciphers to use for SSL encryption. This property is ignored if an SSL connection is not made.

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

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
 MessageBox("Connection failed. Error: " + error.Message)
End Try

MySQLCommunityServer.SSLEnabled

SSLEnabled As Boolean

Set to True to enable an SSL connection.

You can securely connect to MySQL using SSLEnabled and associated properties:

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
 System.Beep
 MessageBox("Connection failed. Error: " + error.Message)
End Try

MySQLCommunityServer.SSLKey

SSLKey As FolderItem

Specifies the key file. This property is ignored if an SSL connection is not made.

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

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
 MessageBox("Connection failed. Error: " + error.Message)
End Try

MySQLCommunityServer.TimeOut

TimeOut As Integer

The connection timeout value (seconds). The default is 15.

' db is a MySQLCommunityServer database
db.TimeOut = 60

MySQLCommunityServer.UserName

UserName As String

The username that is required for access to the database.

This code connects to a database called "BaseballLeague":

Var db As New MySQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
Try
  db.Connect
  ' Use the database
Catch error As DatabaseException
  ' DB Connection error
  MessageBox(error.Message)
End Try

Method descriptions

MySQLCommunityServer.AddRow

AddRow(tableName As String, row As DatabaseRow)

Inserts Data (a populated DatabaseRow) as a new row in TableName.

Always look for a DatabaseException to verify whether or not the data was added.

This code adds a row to an existing Team table with the columns "Name", "Coach" and "City". It's assumed that the variable db contains an active database connection:

Var row As New DatabaseRow
' ID will be updated automatically
row.Column("Name") = "Penguins"
row.Column("Coach") = "Bob Roberts"
row.Column("City") = "Boston"

Try
  db.AddRow("Team", row)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

MySQLCommunityServer.AffectedRowCount

AffectedRowCount As UInt64

Returns the number of rows that were modified by the most recent ExecuteSQL statement.

Get the number of rows that were changed by an UPDATE statement:

' db is a previously connected MySQLCommunityServer
db.ExecuteSQL("UPDATE Team SET City = 'Boston' Where Coach = 'Bob Roberts'")
Var changeCount As UInt64
changeCount = db.AffectedRowCount

MySQLCommunityServer.BeginTransaction

BeginTransaction

Creates a new transaction. Changes to the database made after this call can be saved with CommitTransaction or undone with RollbackTransaction.

A DatabaseException will be raised if the transaction could not begin or another transaction is already in progress.

You typically want to Commit changes after ensuring there were no database errors:

' Prior DB code has run

Try
  DB.BeginTransaction
  DB.ExecuteSQL("CREATE TABLE AddressBook name VARCHAR, email VARCHAR")
  DB.CommitTransaction
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
  DB.RollbackTransaction
End Try

MySQLCommunityServer.Close

Close

Closes or disconnects the database.

Calling Close does not issue a Commit, but some databases will automatically Commit changes in a transaction when you Close the connection and some database will automatically Rollback changes in a transaction when the connection is closed. Refer to the documentation for your database to check what its behavior is.

For desktop apps, you will often Connect to the database when the app starts and Close it when the app quits.

For web apps, you usually Connect to the database when the Session starts and Close it when the Session quits.

This code in the App.Closing event handler closes a previously connected database:

DB.Close ' DB is a property on App

MySQLCommunityServer.CommitTransaction

CommitTransaction

Commits an open transaction. This permanently saves changes to the database.

A DatabaseException will be raised if the transaction could not be committed.

You have to have an open transaction to be able to use CommitTransation. Use BeginTransaction to begin a transaction:

DB.BeginTransaction

You typically want to commit changes after ensuring there were no database errors:

' Prior DB code has run

Try
  DB.CommitTransaction
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
  DB.RollbackTransaction
End Try

MySQLCommunityServer.Connect

Connect

Connects to the database so that you can begin using it.

Warning

A DatabaseException will be raised if the connection could not be made.

This example connects to the database object in the variable db:

Try
  db.Connect
  MessageBox("Connected!")
Catch error As DatabaseException
  MessageBox("Error connecting to the database: " + error.Message)
End Try

MySQLCommunityServer.ExecuteSQL

ExecuteSQL(command As String, ParamArray values() As Variant)

Used to execute an SQL command. Use this for commands that do not return any data, such as CREATE TABLE or INSERT. The command parameter contains the SQL statement.

Tip

You can also pass an array of values as the first and only parameter instead of a ParamArray.

Warning

A DatabaseException will be raised if the SQL passed is invalid or if an error occurs.

Passing values as parameters protects your database by automatically creating a MySQLPreparedStatement.

In this example, the database is being updated without the use of parameters and thus leaves the database vulnerable to a SQL injection attack:

' Updates a table in a database (db)
Var sql As String
sql = "UPDATE Customer SET City='" + CityField.Text + "' WHERE PostalCode='" + PostalCodeField.Text + "'"
Try
 db.ExecuteSQL(sql)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

Here's the same example but using parameters which protects you against a SQL injection attack:

' Updates a table in a database (db)
Var sql As String
sql = "UPDATE Customer SET City=? WHERE PostalCode=?"
Try
 db.ExecuteSQL(sql, CityField.Text, PostalCode.Text)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

The parameter values can also be passed in as a variant array:

Var sql As String
sql = "UPDATE Customer SET City=? WHERE PostalCode=?"
Var values(1) As Variant
values(0) = CityField.Text
values(1) = PostalCode.Text
Try
 db.ExecuteSQL(sql, values)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

This code creates the Team table:

' db is a database
Var sql As String
sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
Try
  db.ExecuteSQL(sql)
  MessageBox("Team table created successfully.")
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

MySQLCommunityServer.LastInsertedRowID

LastInsertedRowID As UInt64

Returns an Int64 containing the value of the primary key column (or the AUTOINCREMENT column if there is no primary key column) for the last row added to any table in the database.

MySQLCommunityServer.Prepare

Prepare(statement As String) As PreparedSQLStatement

Creates a PreparedSQLStatement using the SQL statement for use with the MySQLPreparedStatement class. A prepared statement is an SQL statement with parameters that has been pre-processed by the database so that it can be executed more quickly if it is re-used with different parameters. Prepared statements also mitigate the risk of SQL injection in web apps.

If the provided SQL statement has an error, a DatabaseException will occur when you call SelectSQL or ExecuteSQL.

SQL Injection is a way to attack database-driven applications.

To create a prepared statement for MySQL:

' db is a previously connected MySQLDatabase object

Var ps As MySQLPreparedStatement
ps = db.Prepare("SELECT * FROM Country WHERE code = ?")

MySQLCommunityServer.RollbackTransaction

RollbackTransaction

Cancels an open transaction restoring the database to the state it was in before the transaction began.

A DatabaseException will be raised if the rollback could not be completed.

You will generally want to rollback database changes if a DatabaseException occurs within the transaction.

Important

While rolling back a transaction restores the database to its previous state, it has no impact on the data you access in a RowSet. To update the RowSet so that its data matches the database, you will need to re-query the database to recreate the RowSet.

You have to have an open transaction to be able to use Rollback. Call BeginTransaction to begin a transaction:

DB.BeginTransaction

This code uses rollback to revert database changes in a transaction when an error occurs:

' Prior DB code has run

Try
  db.CommitTransaction
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
  db.RollbackTransaction
End If

MySQLCommunityServer.SelectSQL

SelectSQL(query As String, ParamArray values() As Variant) As RowSet

Executes a SQL SELECT statement and returns the results in a RowSet. The query contains the SQL statement.

Tip

You can also pass an array of values as the first and only parameter instead of a ParamArray.

Warning

A DatabaseException will be raised if the SQL passed is invalid or if an error occurs.

Passing values as parameters protects your database by automatically creating a MySQLPreparedStatement.

This sample adds the names of all customers in a particular postal code to a ListBox. It passes the entire SQL select as a single statement and appends the value from a TextField called PostalCode, leaving the database vulnerable to an SQL injection attack:

' db is a valid connection to a database
Var rowsFound As RowSet
Try
  rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=" + PostalCode.Text)
  For Each row As DatabaseRow In rowsFound
    ListBox1.AddRow(row.Column("Name").StringValue)
  Next
  rowsFound.Close
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

This is the same code as above but instead it uses a value identifier (?) and then passes the value in separately to avoid a SQL injection attack:

' db is a valid connection to a database
Var rowsFound As RowSet
Try
  rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=?", PostalCode.Text)
  For Each row As DatabaseRow In rowsFound
    ListBox1.AddRow(row.Column("Name").StringValue)
  Next
  rowsFound.Close
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

Multiple values can be passed to SelectSQL. In this example, Age and PostalCode are both DesktopTextField controls:

' db is a valid connection to a database
Var rowsFound As RowSet
Try
  rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE Age=? AND PostalCode=?", Age.Value, PostalCode.Text)
  For Each row As DatabaseRow In rowsFound
    ListBox1.AddRow(row.Column("Name").StringValue)
  Next
  rowsFound.Close
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

MySQLCommunityServer.TableColumns

TableColumns(tableName As String) As RowSet

Returns a RowSet with information about all the columns (fields) in the specified tableName.

Note

If an error occurs, a DatabaseException will be raised.

TableColumns returns a RowSet with these columns:

  • ColumnName: A string that is the name of the column.

  • FieldType: An integer that describes the type of the column (refer to the table below).

  • IsPrimary: A boolean that indicates if the column is part of the primary key.

  • NotNull: A boolean that indicates if the column can be set to NULL.

  • Length: An integer that describes the length of the field (for some text fields), the precision of numeric data (for some numeric fields) or the default value for a field (for SQLite columns).

  • cid: (SQLite Only) The ID of the column.

This table identifies the Column Type based on the FieldType Integer:

Field Type

Value

Description

Null

0

Denotes the absence of any value, i.e., a missing value.

Byte

1

Stores the byte representation of a character string.

SmallInt

2

A numeric data type with no fractional part. The maximum number of digits is implementation-specific, but is usually less than or equal to INTEGER. SQLite supports 4-byte smallints. If you are using another data source, check the documentation of your data source.

Integer

3

A numeric data type with no fractional part. The maximum number of digits is implementation-specific. SQLite supports 8-byte integer columns and the FieldType evaluates to 19 (64-bit integer).

Char

4

Stores alphabetic data, in which you specify the maximum number of characters for the field, i.e., CHAR (20) for a 20 character field. If a record contains fewer than the maximum number of characters for the field, the remaining characters will be padded with blanks.

Text or VarChar

5

Stores alphabetic data, in which the number of characters vary from record to record, but you don't want to pad the unused characters with blanks. For example, VARCHAR (20) specifies a VARCHAR field with a maximum length of 20 characters.

Float

6

Stores floating-point numeric values with a precision that you specify, i.e., FLOAT (5).

Double

7

Stores double-precision floating-point numbers.

Date

8

Stores year, month, and day values of a date in the format YYYY-MM-DD. The year value is four digits; the month and day values are two digits.

Time

9

Stores hour, minute, and second values of a time in the format HH:MM:SS. The hours and minutes are two digits. The seconds values is also two digits, may include a optional fractional part, e.g., 09:55:25.248. The default length of the fractional part is zero.

TimeStamp

10

Stores both date and time information in the format YYYY-MM-DD HH:MM:SS. The lengths of the components of a TimeStamp are the same as for Time and Date, except that the default length of the fractional part of the time component is six digits rather than zero. If a TimeStamp values has no fractional component, then its length is 19 digits If it has a fractional component, its length is 20 digits, plus the length of the fractional component.

Currency

11

This is a 64-bit fixed-point number format that holds 15 digits to the left of the decimal point and 4 digits to the right.

Boolean

12

Stores the values of TRUE or FALSE.

Decimal

13

Stores a numeric value that can have both an integral and fractional part. You specify the total number of digits and the number of digits to the right of the decimal place, i.e., DECIMAL (5.2) specifies a decimal field that can contain values up to 999.99. DECIMAL (5) specifies a field that can contain values up to 99,999.

Binary

14

Stores code, images, and hexadecimal data. Consult the documentation of your data source for information on the maximum size of a Binary field.

Long Text (Blob)

15

Stores a text object. Consult the documentation of your data source for information on the maximum size of a Blob.

Long VarBinary (Blob)

16

Stores a binary object. SQLite supports blobs of up to any size. Furthermore, a blob can be stored in a column of any declared data affinity. If you are using another data source, check the documentation of your data source.

MacPICT

17

Stores a Macintosh PICT image. SQLite does not support this data type. Use a Blob to store images.

String

18

Text up to about 2 billion bytes. The same as VarChar.

Int64

19

Stores a 64-bit integer. Integer fields in SQLite are 64 bits and FieldType returns 19.

Unknown

255

Unrecognized data type.

Note

Depending upon the version you are using, only a subset of these types may be supported.

The following code creates a table and then display each column name one at a time:

'db  is a valid database connection
 Try
   db.ExecuteSQL("CREATE TABLE Invoices (ID INTEGER, CustID INTEGER, Amount Double, Note TEXT)")

   Var columns As RowSet = db.TableColumns("Invoices")

   For Each c As DatabaseRow In columns
     MessageBox("Column: " + c.Column("ColumnName").StringValue)
   Next
 Catch error As IOException
   MessageBox("The database could not be created: " + error.Message)
 Catch error As DatabaseException
   MessageBox("Database error: " + error.Message)
 End Try

MySQLCommunityServer.TableIndexes

TableIndexes(tableName As String) As RowSet

Returns a RowSet containing the list of indexes for the passed TableName. Returns Nil if the table has no indexes or the database source does not support indexes.

A DatabaseException will be raised if an error occurs.

The RowSet returns one row for each index on the table and it has one field: IndexName As String.

This code displays the indexes for the "Invoices" table (if it exists) in the specified database:

Try
  db.Connect
  Var indexRS As RowSet
  indexRS = db.TableIndexes("Invoices") ' A table with indexes in the db
  For Each row As DatabaseRow In indexRS
    MessageBox("Index: " + row.ColumnAt(0).StringValue)
  Next
  indexRS.Close
Catch error As NilObjectException
    MessageBox("This database has no indexes.")
Catch error As DatabaseException
    MessageBox("Could not connect to database. Error: " + error.Message)
End Try

MySQLCommunityServer.Tables

Tables As RowSet

Returns a RowSet with a list of all tables in the database.

A DatabaseException will be raised if an error occurs.

Tables returns a RowSet with one field: TableName As String.

The following code gets and displays the table names for the connected database:

' App.db is a connected database
Var tables As RowSet
tables = App.db.Tables

Try
  For Each row As DatabaseRow In tables
    MessageBox(row.ColumnAt(0).StringValue)
  Next
  tables.Close
Catch error As NilObjectException
  MessageBox("This database has no tables.")
End Try

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 RowSet 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

Rowset 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")</pre>

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
 System.Beep
 MessageBox "Connection failed. Error: " + error.Message
End Try

Compatibility

All project types on all supported operating systems.

See also

Database parent class; MySQLPreparedStatement, MySQLCommunityServer, RowSet classes