Tag: mysql db database

How to Connect to MySQL

Purpose of this article

This article aims to show you how to download, install and connect MySQL with ADISRA SmartView.

MySQL is a relational database management system developed by MySQL AB. This software is primarily intended to store and retrieve data as required by other software applications that may run on the same computer or on another computer over a network.

Downloading MySQL

  1. Click on the link below to access the downloads page.

Link: https://dev.mysql.com/downloads/windows/installer/8.0.html

  • Then click on the link “No Thanks, just start my download”.

Installing MySQL

  1. Run the MySQL installer.
  1. Select the “Developer Default” installation type.
  1. Select the first option “Connector/Python 8.0.2.8” and click on the “Check” button as shown in the image below.
  1. Then click “OK”.
  1. If the application fails, click “OK”.
  1. Then click “Yes” and then “Next”.
  1. Now click on “Next” and wait for the components to be installed.
  1. Click on “Execute”.
  1. Click “Next”.
  1. Click “Next”.
  1. In the “Config Type” field, select the “Development Computer” option.
  1. Select the “User Strong Password Encryption Authentication (RECOMMENDED)” option.
  1. The next step is the most important of the installation. Set the password to access MySQL after installation. Then click on “Next”.

Attention! This password will be used when connecting to the ADISRA SmartView.

  1. Click on “Next”.
  1. Click on “Execute”.
  1. Click on “Finish”.
  1. Click on “Next”.
  1. Click on “Finish”.
  1. Click on “Next”.
  1. Select the option “MySQL Server 8.0.28” and then put the password previously defined and then click the “Check” button.
  1. After confirming “Connection Succeeded”, click on “Next.
  1. Click on “Execute”.
  1. Click on “Finish”.
  1. Click on “Next”.
  1. Keep only the option “Start MySQL Workbench after Setup” selected and then click on “Finish”.

Create the MySQL Database

After installing MySQL, let’s create a database and then a table.

  1. With MySQL Workbench open, click on the option “Local Instance MySQL80” as shown in the image below.
  1. Then, click on the first line of “Query 1” and enter the command: “CREATE DATABASE DB_ADISRA;” and then click on the button that looks like a lightning bolt as shown in the image below.
  1. On line 2, enter the command “USER DB_ADISRA;”. This command informs that we will use the “DB_ADISRA” database.
  1. In the navigation tree, verify that the new database has been created. If not, update the navigation tree.
  1. Now, expand the new database and right-click on the item “tables” and then on “Create Table…”.
  1. As shown in the image below, enter the table name as “Person” and enter two columns. First column called “idPerson” of type “INT” and another column called “Names” of type “VARCHAR(50) and then click “Apply”.
  1. Then click on “Apply”.
  1. Click on “Finish”.
  1. Then expand the item “Tables”, right-click the new table “Person” and select the option “Select Rows – Limit 1000”.
  1. Next, the columns with empty rows will be shown. Fill in the lines as shown in the image below. From now on the MySQL database is active, with the Database “DB_ADISRA” created and a table called “Person” has two columns with some data.

Adding DSN in ODBC Data Source

Now we will add a DSN in the Windows ODBC Data Source to prepare the connection to ADISRA SmartView.

  1. Minimize the MySQL Workbench. In the Windows start menu, search for “ODBC Data Source”. Click on ODBC Data Source x64 or x86 bits. It is also possible to access the “ODBC Data Source” configuration via the path (Control Panel -> Administrative Tools -> ODBC Data Sources).
  1. In the ODBC Data Source settings window, click on the “Add…” button to add a new source.
  1. Select the MySQL ODBC 8.0 ANSI Driver option and then click “OK”.

Attention! If this option is not available, you will need to install the ODBC Driver.

Download the ODBC driver compatible with your processor (x64 or x86) available from the link below.

Link: https://dev.mysql.com/downloads/connector/odbc/

After installing, go back to the step above. Click the option mentioned in the image above, and then click “Finish”.

  1. Fill in the fields as shown in the image below. In password, enter the password created earlier. Then click the “Test” button and then the “OK” button.

Configuring ADISRA SmartView

After creating the “DB_ADISRA” database and the “Person” table with some data, you will learn how to create an ADISRA SmartView connection with the MySQL Database and how to query the data in the “Person” table through the DataGrid object.

  1. Open ADISRA SmartView and create a new application. On the top menu, click on the “Settings” tab and then click on the “Database Connections” option.
  1. Click the “+” button to add a new connectivity.
  1. Select the “Microsoft MySQL” option.
  1. Select the option “Use user or system data source name:” and then click on the option “MYSQLDB”, in which the DSN was created.
  1. Enter the User name “root” and the password created at the time of installation and click the “Test Connection” button. After reporting successful connection to MySQL database, click OK button.
  1. Keep the database name as “ODBC001” and click OK.
  1. Create a new Graphic, and insert a “Datagrid” object.
  1. In the property list of the datagrid object, in the “Datagrid Config” property, select the option “Table”, “ODBC001” and “Person”.
  1. Now, save the application and set “Graphic1” as the initial Graphic of the Runtime and then start the Runtime. Below the expected result.