MySQL and methods of accessing it in MAPS/SmartUI

[damiand]

As MySQL doesn’t have full blown OLEDB plugin, it can get tricky to connect to it.

One of the methods is using provided .NET connector inside C# script under graphic form or server script datasource. Using this manual .NET Connector Manual it is straight-forward, look at this example:

1. Add references to Your graphic form script:

using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;

Now draw a datagridview on Your graphic form, add a button, expose the datagridview to script and double-click on the button to create a click event inside the script.
All is left to do is to put Your MySQL connector code behind the button_click event.

private void button_Click(System.Object sender, System.EventArgs e)
{
DataSet dsCountry;
MySqlDataAdapter daCountry;
string connStr = “server=localhost;user=root;database=world;port=3306;password=Passw0rd”;
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
// Perform database operations
string sql = “SELECT Code, Name, HeadOfState FROM Country WHERE Continent=‘North America’”;
daCountry = new MySqlDataAdapter(sql, conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(daCountry);

        dsCountry = new DataSet();
        daCountry.Fill(dsCountry, "Country");
        dataGridView.DataSource = dsCountry;
        dataGridView.DataMember = "Country";
    }
    catch (Exception ex)
    {
       MessageBox.Show(ex.ToString());
    }
    conn.Close();

}

Results are promising:

Second method is for logging/interaction with agents using DBAccess agent and ODBC connector. Now, since the last 32-bit ODBC connector provided by oracle is 5.3.0 this is the one You need to use.
It is not provided as default installation with MySQL server anymore so You need to download it from Oracle website separatelyand install it also separately
Bare in mind it does not support sha256 authentication so You need to have user which supports older method of authentication
It is crucial to understand that since MAPS/SmartUI for now is a 32-bit application, it requires to have 32-bit ODBC.
Take a look at these step-by-step screenshots starting from creation of dbaccess agent inside Agent Configurator:

Leave the connection string as it filled out automatically. Do not fill user credentials, as you did it in
connection string. Once again select a proper database (initial catalog).

Clicking on table name should show us all the tables located in our DB.

Please check also DBAccess Help topic inside MAPS/SmartUI help for better understanding of this mechanism.