OpcUa Extender Server/IoT Studio 如何建立資料庫與串接資料庫等?

Questions and Answers

Moderator: OpcUa Support

Post Reply
OpcUa Support
Posts: 36
Joined: Thu Nov 17, 2022 7:15 am

OpcUa Extender Server/IoT Studio 如何建立資料庫與串接資料庫等?

Post by OpcUa Support »

For example:
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
db.png (20.14 KiB) Viewed 209 times

Step 2:
Prepare historical update nodes.
Open the OPC UA Extender Utility and select the "Historical" tab.
圖片.png
圖片.png (19.61 KiB) Viewed 209 times
Launch the "OpcUa Extender Server Historical Editor" to add nodes.
Historical_editor.PNG
Historical_editor.PNG (14.21 KiB) Viewed 209 times
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.
mysql_query.PNG
mysql_query.PNG (181.01 KiB) Viewed 209 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":{}}]
Running the flow will obtain the updated node values in JSON array format, as shown below:

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"}]
Best Regards,
OpcUa Support Team
Post Reply