[Device] IoT Studio obtain current and voltage, and upload to MySQL database by OpcUa Extender

Questions and Answers

Moderator: OpcUa Support

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

[Device] IoT Studio obtain current and voltage, and upload to MySQL database by OpcUa Extender

Post by OpcUa Support »

Description:
IoT Studio collects power meter data, transfers to OpcUa Extender Server and then upload to the MySQL database.
In this case, IoT studio retrieve power meter data via the Modbus Rtu interface. OpcUa Extender Server serves as an OpcUa server which obtain data from IoT Studio and upload to MySQL.

System block:
Power meter_IoT Studio_OpcUa Extender Server_MySQL.png
Power meter_IoT Studio_OpcUa Extender Server_MySQL.png (61.86 KiB) Viewed 562 times
Environment:
  • Power meter: PM-3133-100P
  • RS485 to USB adapter
  • Working machine: NIFE-105
System architecture:
硬體配置圖.png
硬體配置圖.png (85.68 KiB) Viewed 562 times
Operating step:
Step1:
Hardware connection:
Install clip-on CT onto the power cable, and according to the user manual, connect the L and N terminals of the power meter in parallel with the corresponding terminals of the cable(1P2W-1CT).
power meter wiring.png
power meter wiring.png (22.5 KiB) Viewed 562 times
Also connect PM-3133 to working machine NIFE-105 with USB to RS485 adapter.

Step2:
IoT Studio retrieve power meter data:
Look up the registers for voltage, current and power in the user manual, and configure the register addresses in the IoT Studio node.
Each data could be obtain by giving a payload including modbus address to the "Modbus Flex Getter" node in the IoT studion.
Node implement:
IoT Studio node get power meter.png
IoT Studio node get power meter.png (3.23 KiB) Viewed 561 times
Funtion code:

Code: Select all

msg.payload = {
    value: 1,
    'fc': 4,
    'unitid': 1,
    'address': 4352,
    'quantity': 2
};
return msg; 
p.s. Refer to the user manual of PM3133 for the modbus register.
Register.png
Register.png (54.73 KiB) Viewed 561 times
Step3:
Transfer power meter data to OpcUa Extender Server:
Once getting current, voltage and power data from power meter, transferring them to OpcUa Extender Server is much easier.
IoT Studio transmits data to the OpcUa Extender Server by placing it within an "OpcUa item".
Give a nodeId to the "OpcUa-item" and configure endpoint detail in the"OpcUa-Client".
Node implement:
IoT Studio node get power meter detail.png
IoT Studio node get power meter detail.png (24.08 KiB) Viewed 561 times
IoT Studio flow example:

Code: Select all

[{"id":"02277d531456c4fe","type":"function","z":"4a9845b7074be3de","name":"function 6","func":"msg.payload = {\n    value: 1,\n    'fc': 4,\n    'unitid': 1,\n    'address': 4352,\n    'quantity': 2\n};\nreturn msg; ","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":440,"y":280,"wires":[["606331c441e119dd"]]},{"id":"effe4f43cf25abf4","type":"inject","z":"4a9845b7074be3de","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"1","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":220,"y":180,"wires":[["02277d531456c4fe","57af9717f47cf8e6","1c80619117423e64"]]},{"id":"606331c441e119dd","type":"modbus-flex-getter","z":"4a9845b7074be3de","name":"","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"a826b8f80f821478","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":false,"x":630,"y":280,"wires":[["bbb230d373fb05c8","f0193603b3ea4107"],[]]},{"id":"bbb230d373fb05c8","type":"function","z":"4a9845b7074be3de","name":"data transform","func":"\nlet ds = msg.payload;\n\nfunction WordToByteArray(_word) {\n    // we want to represent the input as a 8-bytes array\n    var byteArray = [0, 0];\n\n    for (var index = 0; index < byteArray.length; index++) {\n        var byte = _word & 0xff;\n        byteArray[index] = byte;\n        _word = (_word - byte) / 256;\n    }\n\n    return byteArray;\n};\n\nfunction dt(w1, w2) {\n    let a = WordToByteArray(w1);\n    let b = a.concat(WordToByteArray(w2));\n    const buf = Buffer.from(b);\n    let ret = buf.readFloatLE(0);\n    return ret;\n}\n\nlet DataObj = {};\n\nDataObj.V_a = dt(ds[0], ds[1]);\n\n\nmsg.payload = DataObj;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":860,"y":280,"wires":[["16481a3e4c43503c","0041b6f1db562652"]]},{"id":"16481a3e4c43503c","type":"debug","z":"4a9845b7074be3de","name":"debug 21","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1040,"y":280,"wires":[]},{"id":"f0193603b3ea4107","type":"debug","z":"4a9845b7074be3de","name":"debug 26","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":840,"y":240,"wires":[]},{"id":"02e53e2785a607b6","type":"modbus-flex-getter","z":"4a9845b7074be3de","name":"","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"a826b8f80f821478","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":false,"x":630,"y":380,"wires":[["bdc277aa9162504b","edeb80e0cb0fb662"],[]]},{"id":"57af9717f47cf8e6","type":"function","z":"4a9845b7074be3de","name":"function 10","func":"msg.payload = {\n    value: 1,\n    'fc': 4,\n    'unitid': 1,\n    'address': 4354,\n    'quantity': 2\n};\nreturn msg; ","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":450,"y":380,"wires":[["02e53e2785a607b6"]]},{"id":"edeb80e0cb0fb662","type":"function","z":"4a9845b7074be3de","name":"data transform","func":"\nlet ds = msg.payload;\n\nfunction WordToByteArray(_word) {\n    // we want to represent the input as a 8-bytes array\n    var byteArray = [0, 0];\n\n    for (var index = 0; index < byteArray.length; index++) {\n        var byte = _word & 0xff;\n        byteArray[index] = byte;\n        _word = (_word - byte) / 256;\n    }\n\n    return byteArray;\n};\n\nfunction dt(w1, w2) {\n    let a = WordToByteArray(w1);\n    let b = a.concat(WordToByteArray(w2));\n    const buf = Buffer.from(b);\n    let ret = buf.readFloatLE(0);\n    return ret;\n}\n\nlet DataObj = {};\n\nDataObj.I_a = dt(ds[0], ds[1]);\n\n\n\nmsg.payload = DataObj;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":860,"y":380,"wires":[["c1a7fe50951a3a65","a65ec7476eaecb9d"]]},{"id":"bdc277aa9162504b","type":"debug","z":"4a9845b7074be3de","name":"debug 32","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":840,"y":340,"wires":[]},{"id":"c1a7fe50951a3a65","type":"debug","z":"4a9845b7074be3de","name":"debug 33","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1060,"y":380,"wires":[]},{"id":"ea5ae6e48f992b32","type":"modbus-flex-getter","z":"4a9845b7074be3de","name":"","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"server":"a826b8f80f821478","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":false,"x":630,"y":480,"wires":[["d4501061101b050d","332c6841e892f4b7"],[]]},{"id":"1c80619117423e64","type":"function","z":"4a9845b7074be3de","name":"function 11","func":"msg.payload = {\n    value: 1,\n    'fc': 4,\n    'unitid': 1,\n    'address': 4356,\n    'quantity': 2\n};\nreturn msg; ","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":450,"y":480,"wires":[["ea5ae6e48f992b32"]]},{"id":"332c6841e892f4b7","type":"function","z":"4a9845b7074be3de","name":"data transform","func":"\nlet ds = msg.payload;\n\nfunction WordToByteArray(_word) {\n    // we want to represent the input as a 8-bytes array\n    var byteArray = [0, 0];\n\n    for (var index = 0; index < byteArray.length; index++) {\n        var byte = _word & 0xff;\n        byteArray[index] = byte;\n        _word = (_word - byte) / 256;\n    }\n\n    return byteArray;\n};\n\nfunction dt(w1, w2) {\n    let a = WordToByteArray(w1);\n    let b = a.concat(WordToByteArray(w2));\n    const buf = Buffer.from(b);\n    let ret = buf.readFloatLE(0);\n    return ret;\n}\n\nlet DataObj = {};\n\nDataObj.kW_a = dt(ds[0], ds[1]);\n\n\n\nmsg.payload = DataObj;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":860,"y":480,"wires":[["01d21ce6b697e069","d7a9479be42d6029"]]},{"id":"d4501061101b050d","type":"debug","z":"4a9845b7074be3de","name":"debug 34","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":840,"y":440,"wires":[]},{"id":"01d21ce6b697e069","type":"debug","z":"4a9845b7074be3de","name":"debug 35","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1060,"y":480,"wires":[]},{"id":"fbfe95c0f20ad194","type":"comment","z":"4a9845b7074be3de","name":"Read Voltage","info":"","x":210,"y":280,"wires":[]},{"id":"9921fcfcdf7884f8","type":"comment","z":"4a9845b7074be3de","name":"Read Current","info":"","x":210,"y":380,"wires":[]},{"id":"f02967e2d2c42847","type":"comment","z":"4a9845b7074be3de","name":"Read Power","info":"","x":210,"y":480,"wires":[]},{"id":"dd3a0b038091a3c3","type":"comment","z":"4a9845b7074be3de","name":"Read Voltage/Current/Power and Write to OpcUa","info":"","x":320,"y":100,"wires":[]},{"id":"56c12103dbf1c302","type":"ui_chart","z":"4a9845b7074be3de","name":"Voltage","group":"6b5c2916b1f5733a","order":8,"width":10,"height":6,"label":"Voltage(Volt)","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1240,"y":200,"wires":[[]]},{"id":"c02a66fb33f919df","type":"ui_chart","z":"4a9845b7074be3de","name":"Current","group":"6b5c2916b1f5733a","order":27,"width":10,"height":6,"label":"Current(mA)","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1240,"y":300,"wires":[[]]},{"id":"04e16dd6937b26af","type":"ui_chart","z":"4a9845b7074be3de","name":"Power","group":"6b5c2916b1f5733a","order":16,"width":10,"height":6,"label":"Power(kW)","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1230,"y":400,"wires":[[]]},{"id":"e01e7e7203012a19","type":"ui_text_input","z":"4a9845b7074be3de","name":"Current","label":"Current","tooltip":"","group":"6b5c2916b1f5733a","order":24,"width":10,"height":1,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":1240,"y":340,"wires":[[]]},{"id":"12eeee655093e1f5","type":"ui_text_input","z":"4a9845b7074be3de","name":"Power","label":"Power","tooltip":"","group":"6b5c2916b1f5733a","order":5,"width":10,"height":1,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":1230,"y":440,"wires":[[]]},{"id":"81e84f9d7eee5232","type":"ui_text_input","z":"4a9845b7074be3de","name":"Voltage","label":"Voltage","tooltip":"","group":"6b5c2916b1f5733a","order":2,"width":10,"height":1,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":1240,"y":240,"wires":[[]]},{"id":"0041b6f1db562652","type":"function","z":"4a9845b7074be3de","name":"function 13","func":"var voltage = msg.payload.V_a;\n\nreturn { payload: voltage };","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1070,"y":240,"wires":[["56c12103dbf1c302","81e84f9d7eee5232","a368e131898a9330"]]},{"id":"a65ec7476eaecb9d","type":"function","z":"4a9845b7074be3de","name":"function 14","func":"var current = msg.payload.I_a;\n\nreturn { payload: current };","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1070,"y":340,"wires":[["e01e7e7203012a19","c02a66fb33f919df","2c255df5d3c46f1c"]]},{"id":"d7a9479be42d6029","type":"function","z":"4a9845b7074be3de","name":"function 15","func":"var power = msg.payload.kW_a;\n\nreturn { payload: power };","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1070,"y":440,"wires":[["12eeee655093e1f5","04e16dd6937b26af","8f2a407ad5f0791d"]]},{"id":"7254b2079be74c47","type":"OpcUa-Client","z":"4a9845b7074be3de","endpoint":"7598fc58543d2bf0","action":"write","deadbandtype":"a","deadbandvalue":1,"time":10,"timeUnit":"s","certificate":"n","localfile":"","localkeyfile":"","securitymode":"None","securitypolicy":"None","folderName4PKI":"","name":"","x":1640,"y":160,"wires":[[]]},{"id":"a368e131898a9330","type":"OpcUa-Item","z":"4a9845b7074be3de","item":"ns=2;i=6012","datatype":"Float","value":"","name":"","x":1420,"y":160,"wires":[["7254b2079be74c47","8492d9b6ece81b7b"]]},{"id":"8492d9b6ece81b7b","type":"OpcUa-Client","z":"4a9845b7074be3de","endpoint":"7598fc58543d2bf0","action":"read","deadbandtype":"a","deadbandvalue":1,"time":10,"timeUnit":"s","certificate":"n","localfile":"","localkeyfile":"","securitymode":"None","securitypolicy":"None","folderName4PKI":"","name":"","x":1640,"y":240,"wires":[["0a58927ef567d52b"]]},{"id":"0a58927ef567d52b","type":"debug","z":"4a9845b7074be3de","name":"debug 41","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1820,"y":240,"wires":[]},{"id":"7ae8d503a64786ab","type":"OpcUa-Client","z":"4a9845b7074be3de","endpoint":"7598fc58543d2bf0","action":"write","deadbandtype":"a","deadbandvalue":1,"time":10,"timeUnit":"s","certificate":"n","localfile":"","localkeyfile":"","securitymode":"None","securitypolicy":"None","folderName4PKI":"","name":"","x":1640,"y":320,"wires":[[]]},{"id":"2c255df5d3c46f1c","type":"OpcUa-Item","z":"4a9845b7074be3de","item":"ns=2;i=6013","datatype":"Float","value":"","name":"","x":1440,"y":320,"wires":[["7ae8d503a64786ab","0beda4b0b73a2e72"]]},{"id":"0beda4b0b73a2e72","type":"OpcUa-Client","z":"4a9845b7074be3de","endpoint":"7598fc58543d2bf0","action":"read","deadbandtype":"a","deadbandvalue":1,"time":10,"timeUnit":"s","certificate":"n","localfile":"","localkeyfile":"","securitymode":"None","securitypolicy":"None","folderName4PKI":"","name":"","x":1640,"y":380,"wires":[["bba0de8fea54c2c1"]]},{"id":"bba0de8fea54c2c1","type":"debug","z":"4a9845b7074be3de","name":"debug 42","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1820,"y":380,"wires":[]},{"id":"826dca4116f1f7ef","type":"OpcUa-Client","z":"4a9845b7074be3de","endpoint":"7598fc58543d2bf0","action":"write","deadbandtype":"a","deadbandvalue":1,"time":10,"timeUnit":"s","certificate":"n","localfile":"","localkeyfile":"","securitymode":"None","securitypolicy":"None","folderName4PKI":"","name":"","x":1640,"y":460,"wires":[[]]},{"id":"8f2a407ad5f0791d","type":"OpcUa-Item","z":"4a9845b7074be3de","item":"ns=2;i=6014","datatype":"Float","value":"","name":"","x":1440,"y":460,"wires":[["826dca4116f1f7ef","abcc9f2321d10b94"]]},{"id":"abcc9f2321d10b94","type":"OpcUa-Client","z":"4a9845b7074be3de","endpoint":"7598fc58543d2bf0","action":"read","deadbandtype":"a","deadbandvalue":1,"time":10,"timeUnit":"s","certificate":"n","localfile":"","localkeyfile":"","securitymode":"None","securitypolicy":"None","folderName4PKI":"","name":"","x":1640,"y":520,"wires":[["8cd7acfc6b5da57f"]]},{"id":"8cd7acfc6b5da57f","type":"debug","z":"4a9845b7074be3de","name":"debug 43","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1820,"y":520,"wires":[]},{"id":"a826b8f80f821478","type":"modbus-client","name":"","clienttype":"serial","bufferCommands":true,"stateLogEnabled":true,"queueLogEnabled":false,"failureLogEnabled":true,"tcpHost":"192.168.1.5","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB0","serialType":"RTU-BUFFERD","serialBaudrate":"19200","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"1000","serialAsciiResponseStartDelimiter":"0x3A","unit_id":"1","commandDelay":"1","clientTimeout":"1000","reconnectOnTimeout":false,"reconnectTimeout":"2000","parallelUnitIdsAllowed":false},{"id":"6b5c2916b1f5733a","type":"ui_group","name":"Default","tab":"83c5b3dec2cc5136","order":1,"disp":true,"width":22,"collapse":false,"className":""},{"id":"7598fc58543d2bf0","type":"OpcUa-Endpoint","endpoint":"opc.tcp://10.15.1.178:48010","secpol":"None","secmode":"None","none":true,"login":false,"usercert":false,"usercertificate":"","userprivatekey":""},{"id":"83c5b3dec2cc5136","type":"ui_tab","name":"Voltage/Current/Power","icon":"dashboard","disabled":false,"hidden":false}]
Step4:
OpcUa Extender Server retrieves data from IoT Studio, then uploads historical data for each individual node. By configuring NodeId to the historical editor in the OpcUa Extender Server.
In the "Historical" sheet, setup Historical configuration:
OpcUa Ext historical.png
OpcUa Ext historical.png (17.92 KiB) Viewed 561 times
Right click to add a new node:
OpcUa Ext historical editor.png
OpcUa Ext historical editor.png (11.4 KiB) Viewed 561 times
Add the NodeId given by IoT Studio:
OpcUa Ext historical editor node from IoT Studio.png
OpcUa Ext historical editor node from IoT Studio.png (33.01 KiB) Viewed 561 times

Step5:
Configure MySQL database:
Before uploading data to the database, database should be setup first.
Setup a new connection to the database in workbench.
MySQL workbench setup new connection.png
MySQL workbench setup new connection.png (45.76 KiB) Viewed 561 times
Setup a password for the user uploaded data from the OpcUa extender server.
Goto "Server" > "User and Privileges"
MySQL user privileges.png
MySQL user privileges.png (182.16 KiB) Viewed 561 times

Select or add an user for OpcUa Extender Server and setup a password.
MySQL user privileges password.png
MySQL user privileges password.png (230.15 KiB) Viewed 561 times

Step6:
Connect OpcUa Extender Server to MySQL database:
Shift to the "Database" sheet, configure database setting.
Database: MySQL 8.x or MySQL 5.x
Hostname: (your database hostname)
Username: (your username)
Password: (your user password)
Default Schema: (the schema you want to upload)
Default Table: (the table in the schema you want to upload)
Click the "Test Connection" button to connect to MySQL.
Then click the "Init. Database" button to initialize the tables in MySQL.
p.s. You may go to viewtopic.php?t=108 for more detailed information to database connection of OpcUa extender server and IoT Studio.
OpcUa Ext database setting.png
OpcUa Ext database setting.png (72.03 KiB) Viewed 561 times
Now, MySQL database is ready to receive information from the power meter.
Best Regards,
OpcUa Support Team
Post Reply