Objectives
This article describes the basic procedure for accessing and manipulating a database with the NJ Series Database Connection CPU Unit using the SELECT, UPDATE, INSERT and DELETE functions.
Introduction
The NJ Series Database Connection CPU Unit (NJ501-1320/1420/1520) has a database connection service that provides functions to insert, update, retrieve and delete records to/from a relational database on a server connected to the built-in EtherNet/IP port of the CPU Unit. These CPU Units support special instructions called "DB Connection Instructions" that handle all database access functions.
This article includes the following sections to describe the implementation of basic database functions with the NJ-series DB CPU.
*This article does not address advanced database functions such as spooling and logs. See the NJ-series Database Connection CPU Units User's Manual (Cat. No. W527) for more information.
Content
The following databases types are supported as of CPU version 1.08.
- Oracle from Oracle Corporation: Oracle Database 10g/11g
- MySQL from Oracle Corporation: MySQL Community Edition 5.1, 5.5, 5.6 (storage engines of the DB are InnoDB and MyISAM)
- Microsoft SQL Server from Microsoft Corporation: SQL Server 2008, 2008 R2, 2012
- DB2 for Linux, UNIX and Windows from IBM Corporation: 9.5, 9.7, 10.1, 10.5
- Firebird from Firebird Foundation Incorporated: Firebird 2.1, 2.5
See the attached manual for more details about connection service specifications.
The example provided below uses the following details.
Hardware Details
- NB7W-TW01B
- Raspberry Pi Server*
*Note: The Raspberry Pi is not an industrial device and is used in this example only as a quick and easy development tool.
Database Basic Details
- Database Type: MySQL
- Server specification method: IP address 192.168.1.121
- Database Name: Production Line 1
- Database Table Name: Data
- Database Table Columns: id, Part_Number, Local_Time, Value
- Database Column data types: id - int(4), Part_Number - text, Local_Time - datetime, Value - int(4)
- User Name: NJ501-1320-1
- Password: qwerty
Production Line 1 Database Table Details
Table Name: Data
The table has 4 columns named id, Part Number, Local Time, and Value. Each column has the following data type established.
- id: int(4)
- Part Number: text(8)
- Local Time: int(6)
- Value: int(4)
NJ-series CPU Unit Details
IP Address: 192.168.1.132
Version 1.08
NB HMI Details
IP Address: 192.168.1.133
Basic System
The following figure shows the system for database manipulation functions.
- Establish an initial connection to the database. This is accomplished by making the appropriate DB connection settings in Sysmac Studio.
- Create a structure data type. This is required to map variables according to corresponding columns in the database table of interest.
- Incorporate the database connect function. This opens an active connection to the database and is required before any database activity occurs.
- Incorporate the database mapping function. This is required when inserting, updating or selecting records and provides the method for variables to be mapped to database rows in a specified table.
- Incorporate the database insert function. This will insert a new row of data into the database.
- Incorporate the database close function. This is required to end the active connection to the database.
The steps below provide the basic functions to open a connection to a database, insert a row of data to a table, and close the connection. The new data will appear in the specified table of the database.
*An example Sysmac Studio project and NB Designer project are attached that include additional functions such as update, retrieve and delete functions. See attachments below for details.
- Start Sysmac Studio (v1.06 or higher) and create a new project.
- Make the settings such as IP Address, Subnet and Gateway in the Built-in EtherNet/IP Port Settings Tab page.
- Add a new database connection. Right-click DB Connection Settings under Configurations and Setup - Host Connection Settings - DB Connection in the Multiview Explorer and select Add - DB Connection Settings from the menu. Or, select DB Connection Settings from the Insert Menu.
- Connection name: DBConnection01 (default name)
- Database type: MySQL
- Server specification method: IP address
- IP address: 192.168.1.121
- Service name/Database name: Production Line 1
- User name: NJ501-1320-1
- Password: qwerty
- Password (for confirmation): qwerty
- As an option, execute the Sysmac Studio DB Communications Test by clicking the Communications Test button. A successful connection will produce the following results.
- Create a variable called "MapVar_Insert_1" using the above structure to be used with the DB_CreateMapping function.
- Add a DB_CreateMapping function in the user program with all necessary parameters and variables.
Precautions
Each database name in the server must have unique user names.
Database table column names cannot have spaces and must follow NJ Series structure member naming restrictions such as 0 to 9, A to Z, a to z and underscores. See Cat. No. W527 for details.
These details and references were made at the time of CPU version 1.08 release. Newer versions may operate and function differently. Contact your local Omron representative for more information.
Summary
The NJ Series Database CPU provides powerful database access and manipulation services. This article aims to provide an introduction to these services with accompanying examples.
References
The Raspberry Pi or other Linux based hardware can be easily transformed into a mySQL server with a few easy steps. These instructions can be found on a number of web resources such as:
