OpcUa Extender Server/IoT Studio 如何建立資料庫與串接資料庫等？
Posted: Fri Feb 16, 2024 5:39 am
For example:
The OPC UA Extender Server connects to MySQL, updates data, and then retrieves data using IoT Studio.
Environment:
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:
Step 2:
Prepare historical update nodes.
Open the OPC UA Extender Utility and select the "Historical" tab.
Launch the "OpcUa Extender Server Historical Editor" to add nodes.
After adding nodes, restart the OPC UA Extender Server.
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.
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:
Running the flow will obtain the updated node values in JSON array format, as shown below:
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 1221 times
Step 2:
Prepare historical update nodes.
Open the OPC UA Extender Utility and select the "Historical" tab.
- 圖片.png (19.61 KiB) Viewed 1221 times
- Historical_editor.PNG (14.21 KiB) Viewed 1221 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 1221 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"}]