Omron NJ/NX SQL 2.0 User guide

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 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.

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 %.

Page is loading ...

Omron NJ/NX SQL 2.0 User guide

Omron NJ/NX SQL 2.0 User guide

Related papers

Other documents