August 22, 2020

Working With MySQL Databases in Regul PLC

Working With MySQL Databases in Regul PLC

This tutorial contains information on working with the MySQL database available in the selected Regul programmable logic controllers. 

Introduction

From a REGUL PLC you can connect not only to the local MySQL database, running on the controller itself, but also to a MySQL database located in the local network.

To operate with a MySQL database there is a mechanism in Epsilon LD, called DB_STRUCTS. Name of this plugin is DB_Functions.

The structure Cities is used to transform the data received from the MySQL database to the instance of this structure.

Then we can drag and drop our component into the grey area. After adding, mark also Tables and update the code with the button above the area.

If you get an error, for example, for LEN function, you need to add libraries to your project. To find, where the particular function is located, you can use library search, when adding a library from the Library Manager tab.

As a result of updating the code, we obtain a function block DB_Functions with a number of methods:

  • This method has a variable, string_buffer, into which is stored an instance of the query to the SQL database.
  • SQL WriteRequestFor_Cities. This method is used to add new data or update data in the database. It is a specific method for MySQL database. For example, if we run this method, setting ID for the city to 5 and this ID already exists in the database, then its name is updated.

The shortcoming of this plug-in is the absence of the commands to run preparatory steps. In particular, those that we discussed before: create database, switch to the database context, create a table in the database. This means that before going to the step 4, we need to run steps 1-3 manually. In this tutorial we are going to extend this plugin with our own code.

First, we need to add commands to the function block commands to create database and table.

FUNCTION_BLOCK DB_Functions

VAR_INPUT
END_VAR

VAR_IN_OUT
END_VAR

VAR_OUTPUT
    SQL_CreateCities : STRING(1551) := 'CREATE TABLE IF NOT EXISTS   `Cities` ( `id` INT AUTO_INCREMENT, `name` varchar(500), PRIMARY KEY (`id`) ENGINE=MyISAM DEFAULT CHARSET=cp1251;'; // create table
    SQL_CreateDB : STRING(1551) := 'CREATE DATABASE IF NOT EXISTS `MyDB`;';
    SQL_UseDB : STRING(1551) := 'USE `MyDB`;';
END_VAR

VAR
    string_buffer	: TSTringBuffer;
    json_reader	: JsonReader;
    json_writer	: JsonWriter;
END_VAR

VAR CONSTANT
    Fields_Cities	: STRING(71) := 'id,name';  // TABLE fields
END_VAR

Here we see that the commands were transferred from the section VAR CONSTANT to VAR_OUTPUT since we will need them to run operations from outside.

Then, we need to create a program to operate with this function block. For this we add a new POU element and fill it with the code below.

PROGRAM DB
VAR
    InitCompleted:BOOL;	// flag shows that database and table initialization is finished and we can add or select data
    dbcreated:BOOL;  // initialization step – database has been created
    dbused:BOOL;  //initialization step – switch to the database execution context
    tablecreated:BOOL;  // initialization step – table has been created
    mysql:TMysqlClient;  // object to work with MySQL
    dbf: DB_Functions;  // program code (function block), generated by the DB_Functions object
    set_data, read_data:BOOL; // for debugger, we can initialize operation with database, by setting any of these flags
    city:cities:=(id:=1, name:='Kassel');
    read_city:cities;
    pDataRow: pointer to POINTER to string;
END_VAR

This code will be running steps before we can start using the database and the table.

We add a method Init that will return True after initialization has finished.

Into the method we insert our implementation.

//----------- declaration -----------
METHOD Init : bool
VAR_INPUT
END_VAR

//-----------implementation--------
Init :=0;
IF NOT dbcreated THEN
	mysql.xExecute:=1;
	mysql.strSQLQuery:=ADR(dbf.SQL_CreateDB);
	mysql();
	IF mysql.xDone THEN
		mysql(xExecute:=0);
		dbcreated:=1;
	ELSIF mysql.xError THEN
		Mysql(xExecute:=0);
		RETURN;
	END_IF
	RETURN;
END_IF

IF NOT dbused THEN
	mysql.xExecute:=1;
	mysql.strSQLQuery:=ADR(dbf.SQL_UseDB);
	mysql();
	IF mysql.xDone THEN
		mysql(xExecute:=0);
		dbused:=1;
	ELSIF mysql.xError THEN
		mysql(xExecute:=0);
		RETURN;
	END_IF
	RETURN;
END_IF

IF NOT tablecreated THEN
	mysql.xExecute:=1;
	mysql.strSQLQuery:=ADR(dbf.SQL_CreateCities);
	mysql();
	IF mysql.xDone THEN
		mysql(xExecute:=0);
		tablecreated:=1;
		Init:=1;
	ELSIF mysql.xError THEN
		Mysql(xExecute:=0);
		RETURN;
	END_IF
	RETURN;
END_IF
This code checks first if database is already created. If not, it creates one. Then checks if the database is already in use. If not, then runs a command for it and sets the corresponding flag. And finally, a check if the table has been already created. Then we create another method, Execute.
//----------- declaration -----------
METHOD Execute : bool
VAR_INPUT
	pSQL:POINTER TO STRING;
END_VAR
//-----------implementation--------
Execute:=0;
IF mysql.xDone OR mysql.xError THEN
	mysql(xExecute:=0);
END_IF
mysql.xExecute:=1;
mysql.strSQLQuery:=pSQL;
mysql();
IF mysql.xDone THEN
	Execute:=1;
END_IF

We see in the code that in this method is passed a pointer to the line of request. And the method runs an instance of TMysqlClient object, that we declared as mysql.

The method is quite simple by itself. Once the request has finished running after we started it with xExecute:=0 we set it again to 1.

The method has no error processing since the purpose of this method is to demonstrate operation with MySQL. We could also check xError in the function block. And stop execution or pass error information to the user.

Then, we add to the implementation of the program itself, DB, the following code.

IF NOT InitCompleted THEN
	InitCompleted:=Init();
END_IF
IF NOT InitCompleted THEN
	RETURN;
END_IF

IF set_data THEN
	IF Execute(dbf.SQLWriteRequestFor_Cities(city)) THEN
		set_data:=0;
		//AddLog.Info('insert successful');
		city.id:=city.id+1;
	END_IF
END_IF

IF read_data THEN
	IF Execute(dbf.SQLRequestFor_Cities()) THEN
		//AddLog.Info(concat('Number of fields: ',UDINT_TO_STRING(mysql.NumFields)));
		pDataRow:=mysql.FetchNextRow();
		WHILE pDataRow<>0 DO
			dbf.ParseSQLResponseFor_Cities(pDataRow, read_city);
			//AddLog.Info(read_city.name);
			pDataRow:=mysql.FetchNextRow();
		END_WHILE
		read_data:=0;
		//AddLog.Info('read succesful');
	END_IF
END_IF

In the previous lessons of the tutorial we added a program, AddLog for logging. It allows us to write event logs to the user log. To have it running, we need to drag and drop the DB program to our tasks in TASK_PLC_PRG.

What does our application do.

Init method runs three SQL instructions: create database (this method is declared in DB_Functions), switch context via SQL_UseDB, then we create a table with SQL_CreateCities.

Structure of the declared table in DB_Functions is similar to the structure of the table declared in the structure Cities, we have an ID for the city and name of the city. And our auto-incremented ID is also declared as a primary key for the table. If you would like to have a different primary key, then you can adjust the SQL command.

Once all three flags are set to 1, Init method returns 1. And in the main program we see that once Init equals to 1 we can proceed to operation with data. If we have any data to add then we set the flag set_data, for reading – read_data.

Let’s see how it works. To do this, we connect to our controller and update the application in it with the one we have just created.

Once we download the application and before we start it, we see that all the flags are set to False. This also leads to the flag InitCompleted to be False. After starting the application, all flags change to True.

After this, we are ready to work with our database. For example, we can set the flag read_data that will start the part of the code to read the data from the database. The method that we use in our DB program fetches one row from the database into pDataRow, then takes the city name from this row.

We set two breakpoints and set the read_data flag to True in the prepared values. Press Ctrl + F7 and the executions halts at the first breakpoint. Continue execution to the second breakpoint and in the logs we will see that there were two fields extracted from our database.

Next, let’s add some city to our table. We have a default name set as “Kassel” for the variable city.name. First, we set variable set_data to True and then we set again read_data to True to see that our city name was selected from the database table.

Delete Data From Database

Then, we can also add a command for deleting data from the database. To do this we extend our DB program with the block providing deletion of data.

We extend our declaration and implementation with the following lines.

//declaration
set_data, read_data, delete_data:BOOL; // for debugger, we can initialize operation with database, by setting any of these flags

//implementation
IF delete_data THEN
	IF Execute(ADR(dbf.SQL_DeleteCities)) THEN
		delete_data:=0;
		AddLog.Info('delete successful');
	END_IF
END_IF
To the DB_Functions we add the SQL line that is called in the added code block.
SQL_DeleteCities : STRING(1551) := 'DELETE FROM `Cities`';

Once we run the updated application and force delete_data to True, then we see the information on it in the log file. And our MySQL table becomes empty.

To do the check directly from the application, we set the flag read_data to True, and see that the data was read, but no city name was read from the table. Unlike in the previous case.

Additional for Version 1.6.x.x

As we saw from the previous examples, to write something to the MySQL database we need to make several steps:

  1. Check if previous operation has finished and xExecute flag is 0.
  2. Set xExecute flag to 1.
  3. Run SQL command.

And only in the next iteration of the cycle verify that our command has run successfully. It will not be verified immediately, since the controller operates asynchronously, and will be verified only in the next cycle. MySQL server receives command, parses it, processes it, sends a respond to the application. It takes time and several cycles.

It looks like we cannot write real-time data to the database. But there is a method. MySQL has a command LOAD DATA INFILE that allows to process the real-time data. We can conclude that by adding file processing to our application, and storing line with data into a text file, we can run the command, for example, every 100 cycles.

To work with files, there is a library, SysFile. It is important to note that we cannot write data to files from the same user that runs MySQL Client. Because it requires ample permissions and connection from a user with ample permissions too, then it creates a security risk. In version 1.6.x.x of Epsilon LD, user connects to the MySQL database under username user, that has limited rights. And does not have a right to write data to files. To circumvent this limitation, was created a separate library, PsMySQL library.

The PsMySQL library offers two methods: one for creating a database and another for dropping a database. The difference with the PsMySQLClient is that the latter runs with user and cannot be used to create a database.

In the application for version 1.6.x.x is added CheckDBState integer variable. And add this code to our DB.Init method.

IF CheckDBState=0 THEN
	mysql.xExecute:=1;
	mysql.strSQLQuery:=ADR(dbf.SQL_CreateDB);
	mysql();
	IF mysql.xDone THEN
		pDataRow:=mysql.FetchNextRow();
		WHILE pDataRow<>0 DO
			pDBName := pDataRow^;
			IF pDBName^ = dbf.DBName THEN
				CheckDBState := 1;
			END_IF
			pDataRow:=mysql.FetchNextRow();
		END_WHILE
		IF CheckDBState=0 THEN
			CheckDBState := 2; //db not found
		END_IF
		mysql(xExecute:=0);
		
		IF CheckDBState = 1 THEN // already found
			dbcreated:=1;
		ELSE	// must be created
			dbcreated := PsMySQL.create_database(dbf.DBName, 'utf8, utf8_general_ci');
		END_IF

	ELSIF mysql.xError THEN
		Mysql(xExecute:=0);
		RETURN;
	END_IF
	RETURN;
END_IF

en | v 1.0

Table of Contents

Download project from this tutorial:
Tutorial 16 – MySQL project
Tutorials