Currently I have been working on a project where I need to transfer string data from PLC to MySQL through Node-Red. I created the database columns in MySQL(phpMyAdmin) and node flows in Node-Red .
But while debugging I am getting following message and error
Here is my function code to pass string data towards mysql node.
Here are my columns in mysql database where I need to store the messages finally.
Where I made the wrong during the process? Are the functions right to pass string data? How to resolve the error of ER_BAD_FIELD_ERROR in mysql node and Error: Unknown column 'RND0508A03A' in 'field list' of 'Fastener Insert' node?
Thanks in advance
Since the data fields are all strings, shouldn't you wrap each value in quotes?
And each of those insert statements will create a discrete record containing just fastener type or production date or barcode. Is that really what you want?
sorry for the late reply. Yes , I want what you mentioned actually. How should I do that? or what I need to change?
By either using single quotes ' around the payload
The better solution is to use prepared queries.
From the readme
Preparing Queries
msg.payload=[24, 'example-user']; msg.topic="INSERT INTO users (`userid`, `username`) VALUES (?, ?);" return msg;with named parameters:
msg.payload={} msg.payload.userToChange=42; msg.payload.newUsername="example-user"; msg.topic="INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;" return msg;
Using prepared queries avoids the need to use quotes (and avoids the problem when the string has quotes inside) and more importantly, protects your database from SQL Injection
Here I have used 5 different insert fucntions to encode five different values for MySQL database five columns fastener_type, volume, production_date, batch_number and customer_id. Should I use "join" node after five function nodes and again another insert function to encode five different string data values in MySQL database. In the last insert function node then I wrote the following statement:
msg.payload={}
msg.payload.fastenerType="RND0508A03A";
msg.payload.Volume=10000;
msg.payload.productionDate="30JUN2021";
msg.batchNumber=756917;
msg.customerID="9909854ß00"
msg.topic = "INSERT INTO barcode('fastener_type', 'volume', 'production_date', 'batch_number', 'customer_id') VALUES (:fastenerType, :Volume, :productionDate, :batchNumber, :customerID) ON DUPLICATE KEY UPDATE 'fastener_type'=:fastenerType;"
return msg;
In that case my node flow looks like following:
Sorry again, As I am new in Node-Red, still now I am quite lost
Share this flow and I will take a look - its too hard to explain without more intricate details (like which function is this? did you use topic names? what is the join node set to? what does the join node result look like) etc etc
So sharing your flow will help me help you.
What is the structure of your "barcode" data table, what is the unique key?
When a message arrives in Node-red, for example Volume data, what does it look like?
I assume this is a production quantity or similar. How do you know which product it is for?
You should probably tell us something about the timing of the different kinds of input messages too; a join node may not be the best way to go.
I used five input nodes and got five different string values from PLC. Then I used insert function for each node and connected function node to MySQL node. In Fastener type insert function node I wrote following statement:
msg.topic = "INSERT INTO barcode(fastener_type) VALUES (" + (msg.payload) + ");";
return msg;
Like this I have used other insert functions such as volume, production date, batch number and customer id.
To get the debugged message I used join-node to join all five function nodes and got following messages
But I got the error "Error: Unknown column 'RND0508A03A' in 'field list' for the following connection
But if everything goes right here my fastener type string data "RND0508A03A" should store in my MySQL database "barcode" table "fastener_type" column.
That is unsuprising.
You need to join the data parts THEN build a single insert query.
Again, share your flow and I will instruct you.
My "barcode" data table structure is as following and key is DESC:
Here is full node flows for my program:
Here red marked functions to write the string data in "barcode" data table and blue marked functions are used to read the data from "barcode" data table. I just connected one write function node Fastener Insert to check whether it can store fastener type data in "barcode" data table or not. But unfortuenately it can't store the value. I used following statements in the Fastener Insert function:
msg.topic = "INSERT INTO barcode(fastener_type) VALUES (" + (msg.payload) + ");";
//msg.topic = query;
return msg;
But for the above statement I got the following error: "Error: Unknown column 'RND0508A03A' in 'field list'".
So where I need to change to store my string data and what should I write? Please let me know if my problem is still unclear.
You cannot do single inserts for each column - that is not how SQL works.
You need to gather all values into one message THEN build a single query e.g...
INSERT INTO myTable(id, fastener_type, thisval, thatval) VALUES (?, ?, ?, ?)
No, that is a picture of your flow. In order to help you further you need to export (CTRL+E) the flow and post it in a code block
```
like this
```
In that case my node flows will be like following:
but then what should I write in the funtions to write all five string data. because I have different message payload from different nodes?
Show us a couple of examples of the data coming in to Node-red.
That table does not contain a field "DESC".
I don't use whatever that database exploration tool is (PhpMyAdmin?) so I might be wrong, but it looks like no field can be NULL and every field must be unique in the database.
Is that correct? It seems unlikely.
ok, one last time.
Here is the exported flow for Fastener_Type input node:
[
{
"id": "65f1b08076c1fc17",
"type": "tab",
"label": "2D_Barcode",
"disabled": false,
"info": "",
"env": []
},
{
"id": "7068f5728a61cb0c",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "Fastener_Type",
"diff": true,
"name": "",
"x": 320,
"y": 140,
"wires": [
[
"1c267ac2c8ca52e0",
"39dcfe77ea772eb6",
"a90a8daa731feceb"
]
]
},
{
"id": "39dcfe77ea772eb6",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Fastener Insert",
"func": "//m = msg.payload\n//fastener = m.Fastener_Type\n//msg.payload=['RND0508A03A'];\nmsg.topic = \"INSERT INTO barcode(fastener_type) VALUES (\" + (msg.payload) + \");\"; \n\n\n//msg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1080,
"y": 140,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "bc8eec020307c679",
"type": "mysql",
"z": "65f1b08076c1fc17",
"mydb": "91ed457ccb906798",
"name": "",
"x": 850,
"y": 100,
"wires": [
[]
]
},
{
"id": "1c267ac2c8ca52e0",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1070,
"y": 200,
"wires": [
[
"696a42d3469ec68b"
]
]
},
{
"id": "696a42d3469ec68b",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function Fastener",
"func": "var query = \"SELECT `fastener_type` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1350,
"y": 80,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "f47a27fa3f488eac",
"type": "mysql",
"z": "65f1b08076c1fc17",
"mydb": "91ed457ccb906798",
"name": "",
"x": 1590,
"y": 340,
"wires": [
[
"cf8c7775b4dd9199"
]
]
},
{
"id": "404c5a7c1f90fc38",
"type": "debug",
"z": "65f1b08076c1fc17",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1790,
"y": 720,
"wires": []
},
{
"id": "ea7558204b815481",
"type": "ui_template",
"z": "65f1b08076c1fc17",
"group": "d97ce9c1f9cd1075",
"name": "",
"order": 1,
"width": "6",
"height": "8",
"format": "<style>\ntable {\n text-align: center;\n margin: 50 auto;\n}\ntbody td{\n padding: 25px;\n background-color: rgb(230,254,255);\n}\nthead th{\n padding: 25px;\n background-color: rgb(69,69,69);\n color:#fff;\n border: rgb(36,149,255);\n}\n</style>\n\n<div>\n <table>\n <thead>\n <tr>\n <th>Fastener Type</th> \n <th>Volume</th> \n <th>Production Date</th> \n <th>Batch Number</th> \n <th>Customer Id</th> \n </tr>\n </thead>\n <tbody>\n <tr>\n <td>{{msg.payload[0].fastener_type}}</td> \n <td>{{msg.payload[0].batch_number}}</td>\n <td>{{msg.payload[0].customer_id}}</td>\n <td>{{msg.payload[0].production_date}}</td> \n <td>{{msg.payload[0].volume}}</td> \n </tr>\n <tr>\n <td>{{msg.payload[1].fastener_type}}</td> \n <td>{{msg.payload[1].volume}}</td> \n <td>{{msg.payload[1].production_date}}</td> \n <td>{{msg.payload[1].batch_number}}</td>\n <td>{{msg.payload[1].customer_id}}</td>\n </tr>\n <tr>\n <td>{{msg.payload[2].fastener_type}}</td> \n <td>{{msg.payload[2].volume}}</td> \n <td>{{msg.payload[2].production_date}}</td> \n <td>{{msg.payload[2].batch_number}}</td>\n <td>{{msg.payload[2].customer_id}}</td>\n </tr>\n <tr>\n <td>{{msg.payload[3].fastener_type}}</td> \n <td>{{msg.payload[3].volume}}</td> \n <td>{{msg.payload[3].production_date}}</td> \n <td>{{msg.payload[3].batch_number}}</td>\n <td>{{msg.payload[3].customer_id}}</td>\n </tr>\n <tr>\n <td>{{msg.payload[4].fastener_type}}</td> \n <td>{{msg.payload[4].volume}}</td> \n <td>{{msg.payload[4].production_date}}</td> \n <td>{{msg.payload[4].batch_number}}</td>\n <td>{{msg.payload[4].customer_id}}</td>\n </tr>\n </tbody>\n </table>\n</div>",
"storeOutMessages": true,
"fwdInMessages": true,
"resendOnRefresh": true,
"templateScope": "local",
"className": "",
"x": 1860,
"y": 640,
"wires": [
[]
]
},
{
"id": "a368340a154d3bea",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "volume",
"diff": true,
"name": "",
"x": 330,
"y": 240,
"wires": [
[
"fde9d49d065b53d2",
"4e8f33bf039130c7",
"a90a8daa731feceb"
]
]
},
{
"id": "77d23295fbd0a7cd",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "production_date",
"diff": true,
"name": "",
"x": 340,
"y": 420,
"wires": [
[
"001abd9ab1237a73",
"ef2d8c58afbd985f",
"a90a8daa731feceb"
]
]
},
{
"id": "42731e6eb062d973",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "batch_number",
"diff": true,
"name": "",
"x": 330,
"y": 580,
"wires": [
[
"c1160ebe3a0bbc9a",
"aea49973c4d684b0",
"a90a8daa731feceb"
]
]
},
{
"id": "3e1d7e66f01a90a1",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "customer_id",
"diff": true,
"name": "",
"x": 330,
"y": 760,
"wires": [
[
"a71ab506d555ec48",
"1e7afb85114b45aa",
"a90a8daa731feceb"
]
]
},
{
"id": "fde9d49d065b53d2",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1070,
"y": 320,
"wires": [
[
"a342515af8a94099"
]
]
},
{
"id": "001abd9ab1237a73",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1130,
"y": 500,
"wires": [
[
"f204d0e28ce1b4dd"
]
]
},
{
"id": "c1160ebe3a0bbc9a",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1150,
"y": 680,
"wires": [
[
"715fdcb5db67507d"
]
]
},
{
"id": "a71ab506d555ec48",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1150,
"y": 840,
"wires": [
[
"2b336c137936beb2"
]
]
},
{
"id": "4e8f33bf039130c7",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "volume insert",
"func": "var query = \"INSERT INTO barcode(volume) VALUES (\" + (msg.payload) + \")\";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1070,
"y": 260,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "ef2d8c58afbd985f",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "production_date insert",
"func": "var query = \"INSERT INTO barcode(production_date) VALUES (\" + (msg.payload) + \")\" ; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1120,
"y": 420,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "aea49973c4d684b0",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "batch number insert",
"func": "var query = \"INSERT INTO barcode(batch_number) VALUES (\" + (msg.payload) + \");\" ;2 \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1120,
"y": 580,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "1e7afb85114b45aa",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "customer id insert",
"func": "var query = \"INSERT INTO barcode(customer_id) VALUES (\" + (msg.payload) + \")\" ;2 \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1090,
"y": 760,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "a342515af8a94099",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function volume",
"func": "var query = \"SELECT `volume` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1300,
"y": 340,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "f204d0e28ce1b4dd",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Dashboard Production date",
"func": "var query = \"SELECT `production_date` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1360,
"y": 480,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "715fdcb5db67507d",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function batch number",
"func": "var query = \"SELECT `batch_number` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1420,
"y": 680,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "2b336c137936beb2",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function customer id",
"func": "var query = \"SELECT `customer_id` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1400,
"y": 860,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "26276f65f62a9122",
"type": "debug",
"z": "65f1b08076c1fc17",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1670,
"y": 260,
"wires": []
},
{
"id": "a9dfb1688f187c8f",
"type": "join",
"z": "65f1b08076c1fc17",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "1",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 1470,
"y": 260,
"wires": [
[
"26276f65f62a9122"
]
]
},
{
"id": "cf8c7775b4dd9199",
"type": "join",
"z": "65f1b08076c1fc17",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "1",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 1730,
"y": 480,
"wires": [
[
"ea7558204b815481",
"404c5a7c1f90fc38"
]
]
},
{
"id": "a90a8daa731feceb",
"type": "join",
"z": "65f1b08076c1fc17",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "1",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 530,
"y": 60,
"wires": [
[
"0bc67f2dc294819e"
]
]
},
{
"id": "0bc67f2dc294819e",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "",
"func": "\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 700,
"y": 60,
"wires": [
[
"bc8eec020307c679"
]
]
},
{
"id": "bdf21c90d7e888e0",
"type": "s7 endpoint",
"transport": "iso-on-tcp",
"address": "192.168.1.100",
"port": "102",
"rack": "0",
"slot": "1",
"localtsaphi": "01",
"localtsaplo": "00",
"remotetsaphi": "01",
"remotetsaplo": "00",
"connmode": "rack-slot",
"adapter": "",
"busaddr": "2",
"cycletime": "1000",
"timeout": "2000",
"name": "PLC1",
"vartable": [
{
"addr": "DB7,INT4",
"name": "Counter_Up"
},
{
"addr": "DB8,INT4",
"name": "Counter_Down"
},
{
"addr": "DB1,X0.4",
"name": "Reset"
},
{
"addr": "DB3,INT4",
"name": "Counter_Right"
},
{
"addr": "DB1,X0.0",
"name": "Up"
},
{
"addr": "DB1,X0.1",
"name": "Down"
},
{
"addr": "DB1,X0.2",
"name": "Right"
},
{
"addr": "DB1,X0.3",
"name": "Left"
},
{
"addr": "DB1,X0.5",
"name": "Start"
},
{
"addr": "DB1,X0.6",
"name": "Stop"
},
{
"addr": "DB1,X0.7",
"name": "Stop Service"
},
{
"addr": "DB2,S1280.256",
"name": "Fastener_Type"
},
{
"addr": "DB2,S1536.10",
"name": "volume"
},
{
"addr": "DB2,S1792.10",
"name": "production_date"
},
{
"addr": "DB2,S2048.10",
"name": "batch_number"
},
{
"addr": "DB2,S2304.10",
"name": "customer_id"
},
{
"addr": "DB6,INT4",
"name": "Counter_Left"
}
]
},
{
"id": "91ed457ccb906798",
"type": "MySQLdatabase",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "2D_Barcode",
"tz": "",
"charset": "UTF8"
},
{
"id": "d97ce9c1f9cd1075",
"type": "ui_group",
"name": "2D Barcode",
"tab": "fb5a62bd6e3e67fd",
"order": 1,
"disp": true,
"width": "6",
"collapse": false,
"className": ""
},
{
"id": "fb5a62bd6e3e67fd",
"type": "ui_tab",
"name": "Barcode",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
For volume input node:
[
{
"id": "65f1b08076c1fc17",
"type": "tab",
"label": "2D_Barcode",
"disabled": false,
"info": "",
"env": []
},
{
"id": "7068f5728a61cb0c",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "Fastener_Type",
"diff": true,
"name": "",
"x": 320,
"y": 140,
"wires": [
[
"1c267ac2c8ca52e0",
"39dcfe77ea772eb6",
"a90a8daa731feceb"
]
]
},
{
"id": "39dcfe77ea772eb6",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Fastener Insert",
"func": "//m = msg.payload\n//fastener = m.Fastener_Type\n//msg.payload=['RND0508A03A'];\nmsg.topic = \"INSERT INTO barcode(fastener_type) VALUES (\" + (msg.payload) + \");\"; \n\n\n//msg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1080,
"y": 140,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "bc8eec020307c679",
"type": "mysql",
"z": "65f1b08076c1fc17",
"mydb": "91ed457ccb906798",
"name": "",
"x": 850,
"y": 100,
"wires": [
[]
]
},
{
"id": "1c267ac2c8ca52e0",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1070,
"y": 200,
"wires": [
[
"696a42d3469ec68b"
]
]
},
{
"id": "696a42d3469ec68b",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function Fastener",
"func": "var query = \"SELECT `fastener_type` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1350,
"y": 80,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "f47a27fa3f488eac",
"type": "mysql",
"z": "65f1b08076c1fc17",
"mydb": "91ed457ccb906798",
"name": "",
"x": 1590,
"y": 340,
"wires": [
[
"cf8c7775b4dd9199"
]
]
},
{
"id": "404c5a7c1f90fc38",
"type": "debug",
"z": "65f1b08076c1fc17",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1790,
"y": 720,
"wires": []
},
{
"id": "ea7558204b815481",
"type": "ui_template",
"z": "65f1b08076c1fc17",
"group": "d97ce9c1f9cd1075",
"name": "",
"order": 1,
"width": "6",
"height": "8",
"format": "<style>\ntable {\n text-align: center;\n margin: 50 auto;\n}\ntbody td{\n padding: 25px;\n background-color: rgb(230,254,255);\n}\nthead th{\n padding: 25px;\n background-color: rgb(69,69,69);\n color:#fff;\n border: rgb(36,149,255);\n}\n</style>\n\n<div>\n <table>\n <thead>\n <tr>\n <th>Fastener Type</th> \n <th>Volume</th> \n <th>Production Date</th> \n <th>Batch Number</th> \n <th>Customer Id</th> \n </tr>\n </thead>\n <tbody>\n <tr>\n <td>{{msg.payload[0].fastener_type}}</td> \n <td>{{msg.payload[0].batch_number}}</td>\n <td>{{msg.payload[0].customer_id}}</td>\n <td>{{msg.payload[0].production_date}}</td> \n <td>{{msg.payload[0].volume}}</td> \n </tr>\n <tr>\n <td>{{msg.payload[1].fastener_type}}</td> \n <td>{{msg.payload[1].volume}}</td> \n <td>{{msg.payload[1].production_date}}</td> \n <td>{{msg.payload[1].batch_number}}</td>\n <td>{{msg.payload[1].customer_id}}</td>\n </tr>\n <tr>\n <td>{{msg.payload[2].fastener_type}}</td> \n <td>{{msg.payload[2].volume}}</td> \n <td>{{msg.payload[2].production_date}}</td> \n <td>{{msg.payload[2].batch_number}}</td>\n <td>{{msg.payload[2].customer_id}}</td>\n </tr>\n <tr>\n <td>{{msg.payload[3].fastener_type}}</td> \n <td>{{msg.payload[3].volume}}</td> \n <td>{{msg.payload[3].production_date}}</td> \n <td>{{msg.payload[3].batch_number}}</td>\n <td>{{msg.payload[3].customer_id}}</td>\n </tr>\n <tr>\n <td>{{msg.payload[4].fastener_type}}</td> \n <td>{{msg.payload[4].volume}}</td> \n <td>{{msg.payload[4].production_date}}</td> \n <td>{{msg.payload[4].batch_number}}</td>\n <td>{{msg.payload[4].customer_id}}</td>\n </tr>\n </tbody>\n </table>\n</div>",
"storeOutMessages": true,
"fwdInMessages": true,
"resendOnRefresh": true,
"templateScope": "local",
"className": "",
"x": 1860,
"y": 640,
"wires": [
[]
]
},
{
"id": "a368340a154d3bea",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "volume",
"diff": true,
"name": "",
"x": 330,
"y": 240,
"wires": [
[
"fde9d49d065b53d2",
"4e8f33bf039130c7",
"a90a8daa731feceb"
]
]
},
{
"id": "77d23295fbd0a7cd",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "production_date",
"diff": true,
"name": "",
"x": 340,
"y": 420,
"wires": [
[
"001abd9ab1237a73",
"ef2d8c58afbd985f",
"a90a8daa731feceb"
]
]
},
{
"id": "42731e6eb062d973",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "batch_number",
"diff": true,
"name": "",
"x": 330,
"y": 580,
"wires": [
[
"c1160ebe3a0bbc9a",
"aea49973c4d684b0",
"a90a8daa731feceb"
]
]
},
{
"id": "3e1d7e66f01a90a1",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "customer_id",
"diff": true,
"name": "",
"x": 330,
"y": 760,
"wires": [
[
"a71ab506d555ec48",
"1e7afb85114b45aa",
"a90a8daa731feceb"
]
]
},
{
"id": "fde9d49d065b53d2",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1070,
"y": 320,
"wires": [
[
"a342515af8a94099"
]
]
},
{
"id": "001abd9ab1237a73",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1130,
"y": 500,
"wires": [
[
"f204d0e28ce1b4dd"
]
]
},
{
"id": "c1160ebe3a0bbc9a",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1150,
"y": 680,
"wires": [
[
"715fdcb5db67507d"
]
]
},
{
"id": "a71ab506d555ec48",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1150,
"y": 840,
"wires": [
[
"2b336c137936beb2"
]
]
},
{
"id": "4e8f33bf039130c7",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "volume insert",
"func": "var query = \"INSERT INTO barcode(volume) VALUES (\" + (msg.payload) + \")\";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1070,
"y": 260,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "ef2d8c58afbd985f",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "production_date insert",
"func": "var query = \"INSERT INTO barcode(production_date) VALUES (\" + (msg.payload) + \")\" ; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1120,
"y": 420,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "aea49973c4d684b0",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "batch number insert",
"func": "var query = \"INSERT INTO barcode(batch_number) VALUES (\" + (msg.payload) + \");\" ;2 \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1120,
"y": 580,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "1e7afb85114b45aa",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "customer id insert",
"func": "var query = \"INSERT INTO barcode(customer_id) VALUES (\" + (msg.payload) + \")\" ;2 \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1090,
"y": 760,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "a342515af8a94099",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function volume",
"func": "var query = \"SELECT `volume` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1300,
"y": 340,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "f204d0e28ce1b4dd",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Dashboard Production date",
"func": "var query = \"SELECT `production_date` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1360,
"y": 480,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "715fdcb5db67507d",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function batch number",
"func": "var query = \"SELECT `batch_number` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1420,
"y": 680,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "2b336c137936beb2",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function customer id",
"func": "var query = \"SELECT `customer_id` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1400,
"y": 860,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "26276f65f62a9122",
"type": "debug",
"z": "65f1b08076c1fc17",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1670,
"y": 260,
"wires": []
},
{
"id": "a9dfb1688f187c8f",
"type": "join",
"z": "65f1b08076c1fc17",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "1",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 1470,
"y": 260,
"wires": [
[
"26276f65f62a9122"
]
]
},
{
"id": "cf8c7775b4dd9199",
"type": "join",
"z": "65f1b08076c1fc17",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "1",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 1730,
"y": 480,
"wires": [
[
"ea7558204b815481",
"404c5a7c1f90fc38"
]
]
},
{
"id": "a90a8daa731feceb",
"type": "join",
"z": "65f1b08076c1fc17",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "1",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 530,
"y": 60,
"wires": [
[
"0bc67f2dc294819e"
]
]
},
{
"id": "0bc67f2dc294819e",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "",
"func": "\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 700,
"y": 60,
"wires": [
[
"bc8eec020307c679"
]
]
},
{
"id": "bdf21c90d7e888e0",
"type": "s7 endpoint",
"transport": "iso-on-tcp",
"address": "192.168.1.100",
"port": "102",
"rack": "0",
"slot": "1",
"localtsaphi": "01",
"localtsaplo": "00",
"remotetsaphi": "01",
"remotetsaplo": "00",
"connmode": "rack-slot",
"adapter": "",
"busaddr": "2",
"cycletime": "1000",
"timeout": "2000",
"name": "PLC1",
"vartable": [
{
"addr": "DB7,INT4",
"name": "Counter_Up"
},
{
"addr": "DB8,INT4",
"name": "Counter_Down"
},
{
"addr": "DB1,X0.4",
"name": "Reset"
},
{
"addr": "DB3,INT4",
"name": "Counter_Right"
},
{
"addr": "DB1,X0.0",
"name": "Up"
},
{
"addr": "DB1,X0.1",
"name": "Down"
},
{
"addr": "DB1,X0.2",
"name": "Right"
},
{
"addr": "DB1,X0.3",
"name": "Left"
},
{
"addr": "DB1,X0.5",
"name": "Start"
},
{
"addr": "DB1,X0.6",
"name": "Stop"
},
{
"addr": "DB1,X0.7",
"name": "Stop Service"
},
{
"addr": "DB2,S1280.256",
"name": "Fastener_Type"
},
{
"addr": "DB2,S1536.10",
"name": "volume"
},
{
"addr": "DB2,S1792.10",
"name": "production_date"
},
{
"addr": "DB2,S2048.10",
"name": "batch_number"
},
{
"addr": "DB2,S2304.10",
"name": "customer_id"
},
{
"addr": "DB6,INT4",
"name": "Counter_Left"
}
]
},
{
"id": "91ed457ccb906798",
"type": "MySQLdatabase",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "2D_Barcode",
"tz": "",
"charset": "UTF8"
},
{
"id": "d97ce9c1f9cd1075",
"type": "ui_group",
"name": "2D Barcode",
"tab": "fb5a62bd6e3e67fd",
"order": 1,
"disp": true,
"width": "6",
"collapse": false,
"className": ""
},
{
"id": "fb5a62bd6e3e67fd",
"type": "ui_tab",
"name": "Barcode",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
In node-red the incoming data looks like as following:
I am also quite new in PhpMyAdmin. That's why I am not sure that it field can be NULL and every field must be unique in the database.
For production_node node:
[
{
"id": "65f1b08076c1fc17",
"type": "tab",
"label": "2D_Barcode",
"disabled": false,
"info": "",
"env": []
},
{
"id": "7068f5728a61cb0c",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "Fastener_Type",
"diff": true,
"name": "",
"x": 320,
"y": 140,
"wires": [
[
"1c267ac2c8ca52e0",
"39dcfe77ea772eb6",
"a90a8daa731feceb"
]
]
},
{
"id": "39dcfe77ea772eb6",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Fastener Insert",
"func": "//m = msg.payload\n//fastener = m.Fastener_Type\n//msg.payload=['RND0508A03A'];\nmsg.topic = \"INSERT INTO barcode(fastener_type) VALUES (\" + (msg.payload) + \");\"; \n\n\n//msg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1080,
"y": 140,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "bc8eec020307c679",
"type": "mysql",
"z": "65f1b08076c1fc17",
"mydb": "91ed457ccb906798",
"name": "",
"x": 850,
"y": 100,
"wires": [
[]
]
},
{
"id": "1c267ac2c8ca52e0",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1070,
"y": 200,
"wires": [
[
"696a42d3469ec68b"
]
]
},
{
"id": "696a42d3469ec68b",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function Fastener",
"func": "var query = \"SELECT `fastener_type` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1350,
"y": 80,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "f47a27fa3f488eac",
"type": "mysql",
"z": "65f1b08076c1fc17",
"mydb": "91ed457ccb906798",
"name": "",
"x": 1590,
"y": 340,
"wires": [
[
"cf8c7775b4dd9199"
]
]
},
{
"id": "404c5a7c1f90fc38",
"type": "debug",
"z": "65f1b08076c1fc17",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1790,
"y": 720,
"wires": []
},
{
"id": "ea7558204b815481",
"type": "ui_template",
"z": "65f1b08076c1fc17",
"group": "d97ce9c1f9cd1075",
"name": "",
"order": 1,
"width": "6",
"height": "8",
"format": "<style>\ntable {\n text-align: center;\n margin: 50 auto;\n}\ntbody td{\n padding: 25px;\n background-color: rgb(230,254,255);\n}\nthead th{\n padding: 25px;\n background-color: rgb(69,69,69);\n color:#fff;\n border: rgb(36,149,255);\n}\n</style>\n\n<div>\n <table>\n <thead>\n <tr>\n <th>Fastener Type</th> \n <th>Volume</th> \n <th>Production Date</th> \n <th>Batch Number</th> \n <th>Customer Id</th> \n </tr>\n </thead>\n <tbody>\n <tr>\n <td>{{msg.payload[0].fastener_type}}</td> \n <td>{{msg.payload[0].batch_number}}</td>\n <td>{{msg.payload[0].customer_id}}</td>\n <td>{{msg.payload[0].production_date}}</td> \n <td>{{msg.payload[0].volume}}</td> \n </tr>\n <tr>\n <td>{{msg.payload[1].fastener_type}}</td> \n <td>{{msg.payload[1].volume}}</td> \n <td>{{msg.payload[1].production_date}}</td> \n <td>{{msg.payload[1].batch_number}}</td>\n <td>{{msg.payload[1].customer_id}}</td>\n </tr>\n <tr>\n <td>{{msg.payload[2].fastener_type}}</td> \n <td>{{msg.payload[2].volume}}</td> \n <td>{{msg.payload[2].production_date}}</td> \n <td>{{msg.payload[2].batch_number}}</td>\n <td>{{msg.payload[2].customer_id}}</td>\n </tr>\n <tr>\n <td>{{msg.payload[3].fastener_type}}</td> \n <td>{{msg.payload[3].volume}}</td> \n <td>{{msg.payload[3].production_date}}</td> \n <td>{{msg.payload[3].batch_number}}</td>\n <td>{{msg.payload[3].customer_id}}</td>\n </tr>\n <tr>\n <td>{{msg.payload[4].fastener_type}}</td> \n <td>{{msg.payload[4].volume}}</td> \n <td>{{msg.payload[4].production_date}}</td> \n <td>{{msg.payload[4].batch_number}}</td>\n <td>{{msg.payload[4].customer_id}}</td>\n </tr>\n </tbody>\n </table>\n</div>",
"storeOutMessages": true,
"fwdInMessages": true,
"resendOnRefresh": true,
"templateScope": "local",
"className": "",
"x": 1860,
"y": 640,
"wires": [
[]
]
},
{
"id": "a368340a154d3bea",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "volume",
"diff": true,
"name": "",
"x": 330,
"y": 240,
"wires": [
[
"fde9d49d065b53d2",
"4e8f33bf039130c7",
"a90a8daa731feceb"
]
]
},
{
"id": "77d23295fbd0a7cd",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "production_date",
"diff": true,
"name": "",
"x": 340,
"y": 420,
"wires": [
[
"001abd9ab1237a73",
"ef2d8c58afbd985f",
"a90a8daa731feceb"
]
]
},
{
"id": "42731e6eb062d973",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "batch_number",
"diff": true,
"name": "",
"x": 330,
"y": 580,
"wires": [
[
"c1160ebe3a0bbc9a",
"aea49973c4d684b0",
"a90a8daa731feceb"
]
]
},
{
"id": "3e1d7e66f01a90a1",
"type": "s7 in",
"z": "65f1b08076c1fc17",
"endpoint": "bdf21c90d7e888e0",
"mode": "single",
"variable": "customer_id",
"diff": true,
"name": "",
"x": 330,
"y": 760,
"wires": [
[
"a71ab506d555ec48",
"1e7afb85114b45aa",
"a90a8daa731feceb"
]
]
},
{
"id": "fde9d49d065b53d2",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1070,
"y": 320,
"wires": [
[
"a342515af8a94099"
]
]
},
{
"id": "001abd9ab1237a73",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1130,
"y": 500,
"wires": [
[
"f204d0e28ce1b4dd"
]
]
},
{
"id": "c1160ebe3a0bbc9a",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1150,
"y": 680,
"wires": [
[
"715fdcb5db67507d"
]
]
},
{
"id": "a71ab506d555ec48",
"type": "trigger",
"z": "65f1b08076c1fc17",
"name": "",
"op1": "",
"op2": "",
"op1type": "pay",
"op2type": "payl",
"duration": "10",
"extend": false,
"overrideDelay": false,
"units": "s",
"reset": "",
"bytopic": "all",
"topic": "topic",
"outputs": 1,
"x": 1150,
"y": 840,
"wires": [
[
"2b336c137936beb2"
]
]
},
{
"id": "4e8f33bf039130c7",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "volume insert",
"func": "var query = \"INSERT INTO barcode(volume) VALUES (\" + (msg.payload) + \")\";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1070,
"y": 260,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "ef2d8c58afbd985f",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "production_date insert",
"func": "var query = \"INSERT INTO barcode(production_date) VALUES (\" + (msg.payload) + \")\" ; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1120,
"y": 420,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "aea49973c4d684b0",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "batch number insert",
"func": "var query = \"INSERT INTO barcode(batch_number) VALUES (\" + (msg.payload) + \");\" ;2 \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1120,
"y": 580,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "1e7afb85114b45aa",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "customer id insert",
"func": "var query = \"INSERT INTO barcode(customer_id) VALUES (\" + (msg.payload) + \")\" ;2 \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1090,
"y": 760,
"wires": [
[
"a9dfb1688f187c8f"
]
]
},
{
"id": "a342515af8a94099",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function volume",
"func": "var query = \"SELECT `volume` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1300,
"y": 340,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "f204d0e28ce1b4dd",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Dashboard Production date",
"func": "var query = \"SELECT `production_date` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1360,
"y": 480,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "715fdcb5db67507d",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function batch number",
"func": "var query = \"SELECT `batch_number` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1420,
"y": 680,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "2b336c137936beb2",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "Function customer id",
"func": "var query = \"SELECT `customer_id` FROM `barcode` ORDER BY created_at DESC\"; \n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1400,
"y": 860,
"wires": [
[
"f47a27fa3f488eac"
]
]
},
{
"id": "26276f65f62a9122",
"type": "debug",
"z": "65f1b08076c1fc17",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1670,
"y": 260,
"wires": []
},
{
"id": "a9dfb1688f187c8f",
"type": "join",
"z": "65f1b08076c1fc17",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "1",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 1470,
"y": 260,
"wires": [
[
"26276f65f62a9122"
]
]
},
{
"id": "cf8c7775b4dd9199",
"type": "join",
"z": "65f1b08076c1fc17",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "1",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 1730,
"y": 480,
"wires": [
[
"ea7558204b815481",
"404c5a7c1f90fc38"
]
]
},
{
"id": "a90a8daa731feceb",
"type": "join",
"z": "65f1b08076c1fc17",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "1",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 530,
"y": 60,
"wires": [
[
"0bc67f2dc294819e"
]
]
},
{
"id": "0bc67f2dc294819e",
"type": "function",
"z": "65f1b08076c1fc17",
"name": "",
"func": "\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 700,
"y": 60,
"wires": [
[
"bc8eec020307c679"
]
]
},
{
"id": "bdf21c90d7e888e0",
"type": "s7 endpoint",
"transport": "iso-on-tcp",
"address": "192.168.1.100",
"port": "102",
"rack": "0",
"slot": "1",
"localtsaphi": "01",
"localtsaplo": "00",
"remotetsaphi": "01",
"remotetsaplo": "00",
"connmode": "rack-slot",
"adapter": "",
"busaddr": "2",
"cycletime": "1000",
"timeout": "2000",
"name": "PLC1",
"vartable": [
{
"addr": "DB7,INT4",
"name": "Counter_Up"
},
{
"addr": "DB8,INT4",
"name": "Counter_Down"
},
{
"addr": "DB1,X0.4",
"name": "Reset"
},
{
"addr": "DB3,INT4",
"name": "Counter_Right"
},
{
"addr": "DB1,X0.0",
"name": "Up"
},
{
"addr": "DB1,X0.1",
"name": "Down"
},
{
"addr": "DB1,X0.2",
"name": "Right"
},
{
"addr": "DB1,X0.3",
"name": "Left"
},
{
"addr": "DB1,X0.5",
"name": "Start"
},
{
"addr": "DB1,X0.6",
"name": "Stop"
},
{
"addr": "DB1,X0.7",
"name": "Stop Service"
},
{
"addr": "DB2,S1280.256",
"name": "Fastener_Type"
},
{
"addr": "DB2,S1536.10",
"name": "volume"
},
{
"addr": "DB2,S1792.10",
"name": "production_date"
},
{
"addr": "DB2,S2048.10",
"name": "batch_number"
},
{
"addr": "DB2,S2304.10",
"name": "customer_id"
},
{
"addr": "DB6,INT4",
"name": "Counter_Left"
}
]
},
{
"id": "91ed457ccb906798",
"type": "MySQLdatabase",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "2D_Barcode",
"tz": "",
"charset": "UTF8"
},
{
"id": "d97ce9c1f9cd1075",
"type": "ui_group",
"name": "2D Barcode",
"tab": "fb5a62bd6e3e67fd",
"order": 1,
"disp": true,
"width": "6",
"collapse": false,
"className": ""
},
{
"id": "fb5a62bd6e3e67fd",
"type": "ui_tab",
"name": "Barcode",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy