Store machine data of a S7 PLC in a SQL database
Some machine operators still use Excel files to document their data. In this article, we will show you why a database is always worthwhile and how you can store machine data from an S7 PLC in an SQL database.
Why a database is better than Excel
In order to be able to work with extensive S7 machine data, we recommend an SQL database. What this is exactly and how it works, we will explain in the next step. Afterwards, we will show you in practice how you can enter a data record of your Siemens S7 PLC into a database table.
But first, we want to clarify the question of why you should use a database at all. You can simply manage your machine data in Excel tables and graphs, right? That is not wrong, but a database brings decisive advantages:
- Multi-user
- More security
- Suitable for large amounts of data
As you can see, managing your machine data in a database offers decisive advantages. In summary, the central management of data simplifies all processes. Data from a database is additionally more readable and easier to manage. For example, if you are searching for specific data records, this search is completed in a database in less than a second.
Our goal today: Store machine data in an SQL database
Read this article to learn how to take advantage of an SQL database for your machinery and equipment. Our goal at the end is the following structure:
Better than Excel
But if you store your data in Excel files, the search is often time-consuming and inconvenient. Excel is therefore suitable for small amounts of data needed by a single person. However, an SQL database is the next logical step if you want to store and manage larger amounts of data. Another plus: Increased security of your data. Multi-user capability, i.e. the ability for several people to work with the data at the same time, makes your production more professional and more efficient.
Databases are everywhere these days, even your Google searches are recorded in a structured way in a database. Considering the increasing amount of data we generate and have at our disposal every day, the importance of databases cannot be overlooked.
What an SQL database is and its functions
A database allows you to collect, store and manage data. For larger amounts of data, a database is worthwhile in any case, because you can keep track of all the data. Now you know what a database is. But what does SQL mean?
The “Standard Query Language” is the database language for relational databases. It is supported and used by almost all database systems. In addition, it has a simple structure and is based on the English language. An advantage is that SQL is case-insensitive. This means that the upper and lower case is unimportant. The editing and creation of database tables is done in SQL via the “Data Definition Language” (DDL). In addition, the SQL commands are simple in structure and in English.
However, it is important to know which functions an SQL database offers. So what can you do with such a database?
- Define databases
- Edit databases
- Query databases
Defining a database means that a database and the different tables are basically created. One can also define the records and attributes. Editing a database includes the function of adding and removing records. In addition, individual attribute values can be changed. Querying a database is particularly interesting. With a simple command, you can search a database full of data for individual attributes and then display them separately. We will also discuss this function later in this article.
What is a database system?
A database system consists of the actual database and a database management system (DBMS). The database is only the physical data itself, but without DMBS no operations can be performed on it. The DMBS is basically just a software that enables operations on the actual database.
As you now know, SQL is the database language. To be able to use the functions mentioned above, you need a database system. In simple terms, a database system is a program that allows you to access and configure a database.
There are different database management systems. Among others, Microsoft SQL Server, MySQL, MariaDB and Postgres are well known. However, in this case we will use MySQL, which is one of the most popular and well-known options.
MySQL is free, widely used, easy to use and is available for all popular operating systems. It was developed in the mid-1990s and appeared in 2000. Other advantages are its extensive documentation and security. MySQL is considered to be particularly secure and stable, since you can encrypt your data extensively. It is also extremely flexible: there are interfaces to many scripting languages for web applications. The DMBS is under the GPL license (OpenSource), has a high storage volume and evaluates queries quickly.
Here is an overview of the most important advantages:
- High compatibility
- Reliable and fast
- High safety standard
MySQL is recommended due to its longevity and popularity. It is perfect for getting started, so later in the article we will show you how to write machine data from an S7 to an SQL database and vice versa.
You would like to get a live insight into DataSuite 3?
In a live online demonstration, we will go into detail about the individual functions and answer any questions you may have. Together we will find out whether DataSuite 3 is suitable for your needs.
Creating a MySQL database for S7 machine data
In this chapter, we will show you step by step how to create a SQL database using MySQL to write machine data to and from a S7 PLC. First, you need the MySQL Community Server. With this version there are no costs and all important functions are included. You can find the link to the download here.
Select your operating system above and then click on the recommended download in the middle. After that, please select the lower of the two versions to download, as shown in this image:
Now you can start the download. If you are not interested in creating an Oracle account, you can simply click “No thanks” in the next window below. This will start the download automatically. After the download, the installer will open, select “Developer Default” to install all the necessary data. Since you need to pay attention to a few things during the installation, we recommend watching a YouTube tutorial that explains the installation in detail.
Next step: creating the database using SQL script
Once you have installed all the necessary files, you must first open MySQL Workbench. On the main page, you will see your connections. Click on the “plus” to add a new connection. Define a connection name and a username there. Then click on the connection and enter the password you set during the installation.
Now we can create our first own database using SQL script. To do this, enter the command shown in the screenshot, where the last part is the name.
Create the first table and enter data
Click on the left lightning bolt to execute the command. Now your database is already created, but still without content. Therefore, we will now create a first table. Here, we create a table with plant parts as an example.
As you can see, the commands are all actually relatively simple. On this page, there is an overview of the most important MySQL commands.
To fill the table now, you can use the following commands:
Display table columns
The next screenshot shows the finished table in MySQL. With the command “select *” you can display all the data from your table:
This table can now be used to enter machine data or also to read out the existing data.
Thanks to the functions of DataSuite, once you have created the database, you basically don’t need any SQL commands. While you can use commands you have written yourself, you can also use one of our templates to transfer data to and from a database. Strictly speaking, you do not need any SQL knowledge to do this. In the next step, we connect the SQL database to the S7 PLC.
Store machine data in a MySQL database
If everything has worked, then you have now created a database. Now you can transfer the data from your S7 PLC with DataSuite into a SQL database and vice versa. We have already shown you at the beginning of this article why saving data in a database makes sense. To make the most of these advantages, we will use our DataSuite from here on. You can download a 30-day DataSuite trial to try out all the features.
Our DataSuite can connect to the following databases: MSSQL, MySQL, MariaDB and Postgres. However, it should be noted that no new databases, tables, views, etc. can be created in DataSuite. DataSuite can only connect to an existing database.
We will now use an example to explain how you can have a record from your control written to the database table you just created. First, we will show you how simple the system is set up for such a communication. All you need is a plant with a PLC, a computer with DataSuite installed and an SQL database.
The data of your machine is stored in the PLC. Using Profinet or OPC UA, you establish a connection between it and your computer on which DataSuite is installed. DataSuite can read the data from your PLC to write it into a database table. This is very easy, you just have to follow the steps below.
Establish and configure database connection with DataSuite
To follow all steps from here on, please open the DataSuite software. Once there, you will find the overview of your database connections in the “Databases” menu. Click on “Create new database connection” to add a new database there. Thereby, the number of new database connections is unlimited.
In the following, you can configure your database connection. For this purpose, enter your connection data of your database. These consist of the IP address, the port, the username and the corresponding password of your database.
If you have entered all data correctly, you can test the database connection below. If the connection works, this will be displayed, and you can then select the database below. Now the connection is established, and you can, for example, write a record into a database table.
Create triggers and assign variables
To do this, create a new trigger in the “Trigger” menu. Then search for “database” in the search field and click on “Write record (insert)”. First, you need to select a handshake variable at the top, which is the trigger condition. After that, select your database and the table where you want to write the machine data. Below that, you have to assign the table rows to the corresponding variables.
Once you have done this, you can save the trigger and then execute it. If it has worked, the “Trigger” menu will show the exact time when the trigger was executed. In the next screenshot, you can see the values in the controller that we transferred to the database with the trigger.
Trigger executed: Data successfully transferred
To check if the data has reached the database, you have to open the MySQL Workbench again and display the database with the command “select * from table name;”. We have already used this command above.
As you can see, the data from the PLC has arrived in the database. The trigger has therefore worked. You can see this from the last entry with the workpiece number 4711, since this was not yet in the database table before the trigger was executed.
Other possibility: Have table data written to your PLC
But the whole thing works the other way around: You can have data written from an SQL database to your PLC without having SQL knowledge. You can find out how to do this in the DataSuite software manual. There you will find detailed instructions with screenshots.
If you are interested in DataSuite and its features, please feel free to visit the product page or contact us directly.
Further information and assistance free of charge from our experts:
Phone: +49 2742 72927 80
E-mail: [email protected]
Conclusion
The great thing about DataSuite is that once a database is created, SQL knowledge is no longer required. Thanks to our templates, your colleagues can, for example, perform an SQL database query with just a few clicks. This saves you time and money in your production. Writing and reading records to and from databases is also no problem.
Related posts
Retrofitting – Old machines with new possibilities
The useful life of solid machine technology is increased by retrofitting. Old machines and new possibilities.
DataSuite News EN 1/23
You know our DataSuite software and therefore know that it is very versatile and offers many functions. In conversations with customers, I notice again and again that particularly exciting features are often not known. I have therefore compiled some interesting little-known features in this newsletter.
DataSuite News EN 5/22
wouldn’t it be nice if you could provide data of machines and plants easily and quickly as web pages? Maybe even with a diagram and your logo? We have built exactly this function and many more into our [MB] DataSuite. Forget the S7 web server and use the fast and easy alternative in the form of our software.
DataSuite News EN 4/22
After a long search we have found a new home. From now on you will find us in the middle of the city of Wissen (Sieg) in one of the most modern office buildings in the region.
How to provide machine data as HTML-files
You may know it: You want to make machine data available to many users centrally, quickly and easily. Our DataSuite
Send SMS with a S7 PLC
When it comes to reducing unplanned downtime, problems can often arise. We offer you an easy way to keep track