The OPC UA Extender Server connects to MySQL, updates data, and then retrieves data using IoT Studio.
Environment:
- OPC UA Extender
- MySQL 8.x
- IoT Studio
- UaExpert
Step 1:
Open the OPC UA Extender Utility and select the "Database" tab.
Database: Choose "MySQL 8.x"
Hostname: localhost
Port: 3306
Username: root
Password: 0000
Confirm Password: 0000
Default Schema: OpcUaExtender_DB
Default Table: OpcUaExtender_Table
Log Policy: Data Update
Click the "Test Connection" button to connect to MySQL.
Then click the "Init. Database" button to initialize the tables in MySQL.
like in below:
- db.png (20.14 KiB) Viewed 1220 times
Step 2:
Prepare historical update nodes.
Open the OPC UA Extender Utility and select the "Historical" tab.
- 圖片.png (19.61 KiB) Viewed 1220 times
- Historical_editor.PNG (14.21 KiB) Viewed 1220 times
Step 3:
Use UaExpert to connect to the OPC UA Extender Server.
Update the values of the selected nodes.
Step 4:
Open MySQL and select opcuaextender_db and opcuaextender_table.
Create a query: SELECT * FROM opcuaextender_db.opcuaextender_table;
You will see the updated values of the selected nodes in the table.
- mysql_query.PNG (181.01 KiB) Viewed 1220 times
Step 5:
Open IoT Studio in a browser at http://127.0.0.1:10200.
Use the node-red-node-mysql node. If it does not exist, install it in the Manage palette.
Add the MySQL database.
Edit the host/port/username/password/database.
Set the query in msg.topic.
msg.topic must contain the query for the database, and the result will be returned in msg.payload.
msg.payload can contain an array of values to bind to the topic.
Deploy the following flow:
Code: Select all
[{"id":"903f7e5c5689667a","type":"mysql","z":"88e6265029725295","mydb":"017ff5ee821c868d","name":"opcuaextender_db","x":390,"y":180,"wires":[["378ca3213fc46e83"]]},{"id":"e9f9f0c7a4c1597a","type":"inject","z":"88e6265029725295","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM opcuaextender_table;","payload":"","payloadType":"date","x":220,"y":180,"wires":[["903f7e5c5689667a"]]},{"id":"378ca3213fc46e83","type":"debug","z":"88e6265029725295","name":"debug 1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":560,"y":180,"wires":[]},{"id":"017ff5ee821c868d","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"opcuaextender_db","tz":"","charset":"UTF8","credentials":{}}]Code: Select all
[{"SerialNum":1,"ServerTimestamp":"2024-02-16T03:41:09.000Z","SourceTimestamp":"2024-02-16T03:41:09.000Z","NodeId":"ns=2;i=6003","NodeBrowseName":"Int32_Var01","NodeValue":"0","LogTimestamp":"2024-02-16T03:41:18.000Z"},{"SerialNum":2,"ServerTimestamp":"2024-02-16T03:41:09.000Z","SourceTimestamp":"2024-02-16T03:41:09.000Z","NodeId":"ns=2;s=Tags.TagType.BCD.TagBCD","NodeBrowseName":"TagBCD","NodeValue":"9999","LogTimestamp":"2024-02-16T03:41:18.000Z"},{"SerialNum":3,"ServerTimestamp":"2024-02-16T03:41:09.000Z","SourceTimestamp":"2024-02-16T03:41:09.000Z","NodeId":"ns=2;s=Prot.ModbusTcp.InputReg.STRING.TagString_4_40100","NodeBrowseName":"TagString_4_40100","NodeValue":"","LogTimestamp":"2024-02-16T03:41:18.000Z"},{"SerialNum":4,"ServerTimestamp":"2024-02-16T03:41:10.000Z","SourceTimestamp":"2024-02-16T03:41:09.000Z","NodeId":"ns=2;s=Prot.ModbusTcp.InputReg.STRING.TagString_4_40100","NodeBrowseName":"TagString_4_40100","NodeValue":"hijk","LogTimestamp":"2024-02-16T03:41:18.000Z"},{"SerialNum":5,"ServerTimestamp":"2024-02-16T03:42:09.000Z","SourceTimestamp":"2024-02-16T03:42:07.000Z","NodeId":"ns=2;s=Prot.ModbusTcp.InputReg.STRING.TagString_4_40100","NodeBrowseName":"TagString_4_40100","NodeValue":"abcd","LogTimestamp":"2024-02-16T03:42:19.000Z"},{"SerialNum":6,"ServerTimestamp":"2024-02-16T03:42:16.000Z","SourceTimestamp":"2024-02-16T03:42:16.000Z","NodeId":"ns=2;i=6003","NodeBrowseName":"Int32_Var01","NodeValue":"1234","LogTimestamp":"2024-02-16T03:42:19.000Z"},{"SerialNum":7,"ServerTimestamp":"2024-02-16T03:42:27.000Z","SourceTimestamp":"2024-02-16T03:42:27.000Z","NodeId":"ns=2;s=Tags.TagType.BCD.TagBCD","NodeBrowseName":"TagBCD","NodeValue":"2222","LogTimestamp":"2024-02-16T03:42:29.000Z"}]