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.
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
//----------- 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
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:
- Check if previous operation has finished and xExecute flag is 0.
- Set xExecute flag to 1.
- 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