In this article, we will show you how to connect the Elipse E3 to a MySQL database.
PLEASE NOTE: From Elipse E3’s version 6.5 on, we have introduced native connection to the MySQL database. For versions prior to 6.5, check out the procedure below:
Since this database is not native to the Elipse platform, the connection will be established via ODBC driver.
Requirements:
First of all, in order to connect MySQL to Elipse E3, you will need the following software:
- Elipse E3: In this example, we use version 5.1.195.
- MySQL Server (local or on the network): In this example, we use version 8.0.21.
- Visual Studio 2019 Redistribuível x86: required for Connector/ODBC.
- Connector/ODBC (32 bits) do MySQL.
Introduction:
Firstly, install the 32-bit version (x86) of Visual Studio 2019 Redistributable.
Then, install the 32-bit version of MySQL’s Connector/ODBC.
Establishing communication:
At version 8.0.21, the connection string is:
Provider=MSDASQL;Driver={MySQL ODBC 8.0 ANSI Driver}; Server=IP_SERVIDOR; Database=BANCO; User=USUARIO;Password=SENHA;Option=3;
Or
Provider=MSDASQL;Driver={MySQL ODBC 8.0 Unicode Driver}; Server=IP_SERVIDOR; Database=BANCO; User=USUARIO;Password=SENHA;Option=3;
You can check the driver at the OBDC’s datasources for Windows (32 bits).
If you’re using a different version, check the connection string in the following link:
https://www.connectionstrings.com/mysql/
With the connection string, you can now assemble the script that will establish the connection and retrieve the data.
Since this communication happens via ODBC, it will happen entirely via scripts.
At the attached example, we will check the first five instances in the world table; this table is installed alongside MySQL.
Script Structure:
- The connection to the database is established with the previously identified connection string.
'Cria conexão com o banco de dados
Set db_con = CreateObject("ADODB.Connection")
str_con = "Provider=MSDASQL;Driver={MySQL ODBC 8.0 ANSI Driver};Server="&Screen.Item("spIP").Value&";Database=world;User="&Screen.Item("spUsuario").Value&";Password="&Screen.Item("spSenha").Value&";Option=3;"
'ou Driver={MySQL ODBC 8.0 Unicode Driver}
db_con.Open str_con 'Abre a conexao ODBC
- After the connection has been established, you will need to create a query that will return the desired values.
Set rs = CreateObject("ADODB.Recordset")
sqlstr = "SELECT * FROM city" 'SQL da consulta. Buscando todos os registros da tabela City
rs.ActiveConnection = db_con
rs.Open sqlstr, db_con 'Executa a consulta definia na variavel sqlstr
rs.MoveFirst 'Vai para o primeiro registro
- From this moment on, the data will return at points where the fields variable starts with 0. Example:
Msgbox rs.fields(0)
For i = 0 to 4
For j= 0 to 4
Execute "Screen.Item("&Chr(34)&"Texto"&((j+1)+(5*i))&Chr(34)&").Value = rs.fields("&j&")" 'Percorre todos os campos e preenche os displays da tela
Next
rs.MoveNext 'Passa para o proximo registro
Next
Attention: Don’t use the root to establish this connection.
Attachment:
