TechTip: SQL Query With Weintek cMT Series HMI

In addition to being able to syncronise datalogs and alarm/event logs to a MySQL or MS SQL database, you can now read, update, delete and append databases using the SQL query object.

Steps
1. Create your database on your MySQL or MS SQL database server. I have called mine “database” and created a table on it called “plcdata“. idplcdata is the primary key and data is the column for the data I want to record.

2. Setup Database Server in EasyBuilder Pro project. I am running MySQL locally on my PC for testing, so IP address is set to local – 127.0.0.1 (I would change this to the IP address of the server when implementing on an HMI – be sure that if you’re running MySQL on a Windows machine that it is running as a service).

The credentials for my database are entered here too – I would recommend stronger passwords than those used for the demo!

Also set up Status and control words on the second tab. I am using LW-0 for status address and LW-10 for the control address.

3. As a first step, I would recommend checking the database connection by running a simulation with a simple project showing the status word (LW-0) and using set word buttons with LW-10 – setting to 1 to start and 2 to stop the service.

4. Setup SQL Query. Use “Import from server”. Make sure you have only one primary key!

LW-100 is the starting address and is in this case the primary key and the ID – in my database this is “idplcdata“. LW-101 is the address for “data”

LW-200 is my control address. In the demo project, this is used to create, read, update and delete using set word objects – this could, of course, be a PLC address or tag on your application.

5. So far so good? We have a successful database connection… so we’ll add an SQL Query result object. Not really much to set on here.

6. With the demo project running and database successfully connected we can query the database in MySQL workbench.

SELECT idplcdata as “Record ID”, data as “PLC Data”
FROM
database.plcdata;

7. A demo project… is available to download from here.