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 (61.86 KiB) Viewed 1470 times
- Power meter: PM-3133-100P
- RS485 to USB adapter
- Working machine: NIFE-105
- 硬體配置圖.png (85.68 KiB) Viewed 1470 times
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 (22.5 KiB) Viewed 1470 times
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 (3.23 KiB) Viewed 1469 times
Code: Select all
msg.payload = {
value: 1,
'fc': 4,
'unitid': 1,
'address': 4352,
'quantity': 2
};
return msg;
- Register.png (54.73 KiB) Viewed 1469 times
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 (24.08 KiB) Viewed 1469 times
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}]
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 (17.92 KiB) Viewed 1469 times
- OpcUa Ext historical editor.png (11.4 KiB) Viewed 1469 times
- OpcUa Ext historical editor node from IoT Studio.png (33.01 KiB) Viewed 1469 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 (45.76 KiB) Viewed 1469 times
Goto "Server" > "User and Privileges"
- MySQL user privileges.png (182.16 KiB) Viewed 1469 times
Select or add an user for OpcUa Extender Server and setup a password.
- MySQL user privileges password.png (230.15 KiB) Viewed 1469 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 (72.03 KiB) Viewed 1469 times