Page is loading ...
NJ/NX SQL 2.0 with MySQL
Stored Procedure and Batching
The most widely used relational databases in the world are MySQL, Oracle and Microsoft SQL
server. This document covers how to configure and run NJ/NX SQL 2.0 with MySQL 8.
Specifically, the document walks you through the configuration and setup of the database,
creating a connection between the controller and database, enabling error logging, controlling
spooling, creating batch inserts and configuring and running stored procedures.
Note: MySQL is Open Source and free to use.
Using TLS for authentication is not covered in this guide.
Basic DB Creation and Connection Testing
Open Sysmac Studio
Connect directly to the controller using the controllers IP address (default IP address: port 1 192.168.250.1, port
2 192.168.251.1). Select “Ethernet connection via hub” and press
“Connect”.
Note: Your computer must be on the same subnet (ex. 192.168.250.XXX) as the controller.
• Sysmac and SYSMAC are trademarks or registered trademarks of OMRON Corporation in Japan and other
countries for OMRON factory automation products.
• Oracle, Java, and MySQL are registered trademarks of Oracle Corporation and/or its affiliates in the
USA and other countries.
Trademarks
The connection will start in online mode.
Note: The program that currently resides on the PLC will not be uploaded. If the program is
required, press the synchronize button followed by “transfer from controller”.
The nice thing about connecting directly to the controller is it configures the device and
connection parameters. To check to see the version of the controller, right click the controller
icon. Select “Change Device”.
Note: SQL 2.0 is only available on firmware 1.33 and up.
Note: Controllers with XX20 have SQL functionality.
If you created a new program and did not connect directly to the controller, you must set the IP
address of the controller in Sysmac Studio to go online. Select Controller>Communication
Setup.
Select “Ethernet connection via hub”. Set the IP Address of the controller and select “Ethernet
Communication Test”. Close the window if the test comes back “OK”. If the test fails, make
sure your computer is connected to the controller.
The next step is to install and setup MySQL 8.0.
Download MySQL 8.0 from the link below.
https://dev.mysql.com/downloads/mysql/
Install The developer version of the software.
Press Next
Press Next
Press Execute to install all the software that is needed.
Press next
Select Standard SQL server and press next.
Write down the port number for the MySQL Server (3306). This will be used in Sysmac Studio
for the database configuration.
Press next.
Use legacy Authentication method. The controller cannot connect using stored password
encryption.
Create a password for your root user. The controller can log in as the root user. However, I will
show how to create another user later in this document. Press next.
Note: To increase security, its recommend to use methods to create the strongest possible
password for a user.
Set the database as a Windows service. If required, change the service name. The database
will start when Windows is loaded.
Press next.
Press execute and then next.
Press next until you see the button “finish”. Press finish.
Note: A nice feature of MySQL is that you can have multiple instance of a database on one
machine. However, this document will not go over multiple database configuration.
Test the connection and press next.
Press finish.
Open the work bench software that was just installed. Work bench is a client application that
allows you to connect and configure the database server. Select the MySQL connections plus
button.
Name the connection and press test. Press OK. Double click the new connection to log in.
Note: The pop up box shows the cypher suite used for the encrypted connection under SSL.
Please refer to my MySQL TLS/SSL document for more information on how to configure TLS.
Create a new database by clicking the database icon with the plus sign.
Name the database and press apply.
Press apply.
Press Finish.
Select the Schema tab and create a new table.
Create the column names. In this example I am using ID as the primary key (PK check
box). Primary keys cannot be null (NN check box).
Press apply.
Press apply.
The table will appear under the Tables tab.
Create a new user by selecting “Users and Privileges” under the administration tab. In this
example I will create a user with root privileges.
Give the user a name.
Set Authentication type to standard. You will not be able to connect using the other mode.
Give the user a password and set the Limiting to host matching entry to %.
/