>
>
>
> 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`.
>
>
forsearch
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.
>
> Passing values as parameters protects your database by automatically creating a `MySQLPreparedStatement`.
>
>
>
>
>
> Tip
>
>
>
> You can also pass an `array` of values as the first and only parameter instead of a `ParamArray`. This array **must** be of type `Variant`.
>
>
>
>
>
> Warning
>
>
>
> A `DatabaseException` will be raised if the SQL passed is invalid or if an error occurs.
>
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
forsearch
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.
>
>
>
>
>
> Note
>
>
>
> Depending upon the version you are using, only a subset of these types may be supported.
>
>
forsearch
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:
>
> ``` xojo
> 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
> ```
forsearch
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:
>
> ``` xojo
> ' App.db is a connected database
> Var tables As RowSet = 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](http://www.mysql.com/products/) page which clarifies the differences between the various editions of MySQL.
The MySQLCommunityPlugin database uses a [GPL](http://www.gnu.org/licenses/gpl.html) 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](http://www.mysql.com/products/community/) 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](http://dev.mysql.com/doc/).
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
### 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.
``` xojo
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`:
``` xojo
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:
``` xojo
MyDB.ExecuteSQL("set names utf8 collate utf8_general_ci")
MyDB.ExecuteSQL("set character set utf8")
MyDB.ExecuteSQL("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](http://dev.mysql.com/doc/refman/5.7/en/ssl-connections.html).
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
### 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 .
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
### MariaDB
MariaDB is a "drop-in" replacement for MySQL. You can read more about it at their web site:
The MySQLCommunityServer plugin can be used to connect to MariaDB.
## Sample code
This code establishes a connection to MySQL database:
``` xojo
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", FolderItem.PathModes.Native)
' Specify SSL certificate file
db.SSLCertificate = New FolderItem("MySQLCertificateFile", FolderItem.PathModes.Native)
' Specify SSL authority file
db.SSLAuthority = New FolderItem("MySQLAuthFileFile", FolderItem.PathModes.Native)
' Specify SSL authority directory
db.SSLAuthorityFolder = New FolderItem("SSLCACertFile", FolderItem.PathModes.Native)
' Specify SSL cipher
Var cipher As String = "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
| | |
|-----------------------|-----------------------|
| **Project Types** | Console, Desktop, Web |
| **Operating Systems** | All |
`Database` parent class; `MySQLPreparedStatement`, `RowSet` classes