Combine 2 different payload into function

alikhan · 30 September 2020 15:09

We want to call the POSK function payload into function payload and insert it into SQL.
the yellow Highlighted number needs to combine with the second payload msg into the POSK column.

can you please assist?

Colin · 30 September 2020 16:20

Have a look in the node-red cookbook, notably https://cookbook.nodered.org/basic/join-streams for an example of how to do this.

Steve-Mcl · 30 September 2020 18:30

Are you still building SQL in the function node? (you stopped replying on your other thread)

Post your flow and I will show you how to do this properly.

alikhan · 1 October 2020 17:01

is there any limitation for SQL connection, when we connect more 2 insert Db function to MSSQL server it show error " msg : error
"ConnectionError: Connection is closed."

Steve-Mcl · 1 October 2020 17:47

Are you using mssql-plus?

alikhan · 1 October 2020 18:04

thanks, sorry overlook, it is working with SQL-plus.
Do we have any limitation of connection with MSSQL-PLUS?

alikhan · 1 October 2020 18:09

Do you think we have any other option to get the data from MQTT to MSSQL, as we have to connect more then 68 nodes into MSSQL simultaneously.

Steve-Mcl · 1 October 2020 18:16

What on earth are all those nodes doing?

Can you export your flow and past it in a reply?

Also capture a sample of data from the output of the JSON node

I think you are not structuring this correctly.

alikhan · 1 October 2020 18:43

flow

[{"id":"4b3f21a3.ba434","type":"tab","label":"Redlion-main-L16","disabled":false,"info":""},{"id":"ce8063fd.fd102","type":"mqtt in","z":"4b3f21a3.ba434","name":"","topic":"L16/#","qos":"0","datatype":"auto","broker":"b611955e.b00bf8","x":70,"y":80,"wires":[["f0325971.9c3e08","869fa08d.3ba1"]]},{"id":"f0325971.9c3e08","type":"json","z":"4b3f21a3.ba434","name":"","property":"payload","action":"","pretty":true,"x":210,"y":80,"wires":[["8c518462.0d7dc8","18dd9195.a7e43e","1ccf50a1.44f08f"]]},{"id":"4caf64e4.edd2ac","type":"mosca in","z":"4b3f21a3.ba434","mqtt_port":1883,"mqtt_ws_port":"8000","name":"","username":"red","password":"red","dburl":"10.104.32.105","x":110,"y":20,"wires":[[]]},{"id":"8c518462.0d7dc8","type":"function","z":"4b3f21a3.ba434","name":"L16_POS1","func":"d = new Date,\ndformat = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('/')+' '+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join(':');\n    \ndformat1 = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('')+''+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join('');\n\ndtstmp = new Date().toString();\n\n\npld =        \"INSERT INTO [RedlionMQTT].[dbo].[Pos1_L16] \"\npld = pld + \"(DateTime, POD, Pos1_op, Pos1_ETA, Pos1_Weight, Pos1_rdy,Pos1_Length,Pos1_1_2_cut,Pos1_All_cut,Pos1_DD,Detex,Number_Ply,POSK) \"\npld = pld + \"VALUES ('\"+ dformat+\"','\" +msg.payload.POD+ \"', '\" + msg.payload.Pos1_op + \"','\" + msg.payload.Pos1_ETA + \"','\" + msg.payload.Pos1_Weight + \"','\" + msg.payload.Pos1_rdy + \"','\" + msg.payload.Pos1_Length + \"','\" + msg.payload.Pos1_1_2_cut + \"','\" + msg.payload.Pos1_All_cut + \"','\" + msg.payload.Pos1_DD + \"','\" + msg.payload.Detex + \"','\" + msg.payload.Number_Ply + \"','\"+ 'L16POS1'+dformat1+\"')\"\n\nmsg.topic = ''\nmsg.payload = pld\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":310,"y":160,"wires":[["542238be.840fe8","edd22648.9fe2d8"]]},{"id":"869fa08d.3ba1","type":"debug","z":"4b3f21a3.ba434","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":370,"y":80,"wires":[]},{"id":"542238be.840fe8","type":"debug","z":"4b3f21a3.ba434","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":590,"y":120,"wires":[]},{"id":"18dd9195.a7e43e","type":"function","z":"4b3f21a3.ba434","name":"L16_POS2","func":"d = new Date,\ndformat = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('/')+' '+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join(':');\n    \ndformat1 = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('')+''+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join('');\n\ndtstmp = new Date().toString();\n\n\npld =       \"INSERT INTO [RedlionMQTT].[dbo].[POS2_L16] \"\npld = pld + \"(DateTime, POD, Pos2_op, Pos2_ETA, Pos2_Weight, Pos2_rdy,Pos2_Length,Pos2_1_2_cut,Pos2_All_cut,Pos2_DD,Detex,Number_Ply,POSK) \"\npld = pld + \"VALUES ('\"+ dformat+\"','\" +msg.payload.POD+ \"', '\" + msg.payload.Pos2_op + \"','\" + msg.payload.Pos2_ETA + \"','\" + msg.payload.Pos2_Weight + \"','\" + msg.payload.Pos2_rdy + \"','\" + msg.payload.Pos2_Length + \"','\" + msg.payload.Pos2_1_2_cut + \"','\" + msg.payload.Pos2_All_cut + \"','\" + msg.payload.Pos2_DD + \"','\" + msg.payload.Detex + \"','\" + msg.payload.Number_Ply + \"','\"+ 'L16Pos2'+dformat1+\"')\"\n\nmsg.topic = ''\nmsg.payload = pld\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":310,"y":200,"wires":[["edd22648.9fe2d8"]]},{"id":"1ccf50a1.44f08f","type":"function","z":"4b3f21a3.ba434","name":"L16_POS3","func":"d = new Date,\ndformat = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('/')+' '+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join(':');\n    \ndformat1 = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('')+''+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join('');\n\ndtstmp = new Date().toString();\n\n\npld =       \"INSERT INTO [RedlionMQTT].[dbo].[POS3_L16] \"\npld = pld + \"(DateTime, POD, Pos3_op, Pos3_ETA, Pos3_Weight, Pos3_rdy,Pos3_Length,Pos3_1_2_cut,Pos3_All_cut,Pos3_DD,Detex,Number_Ply,POSK) \"\npld = pld + \"VALUES ('\"+ dformat+\"','\" +msg.payload.POD+ \"', '\" + msg.payload.Pos3_op + \"','\" + msg.payload.Pos3_ETA + \"','\" + msg.payload.Pos3_Weight + \"','\" + msg.payload.Pos3_rdy + \"','\" + msg.payload.Pos3_Length + \"','\" + msg.payload.Pos3_1_2_cut + \"','\" + msg.payload.Pos3_All_cut + \"','\" + msg.payload.Pos3_DD + \"','\" + msg.payload.Detex + \"','\" + msg.payload.Number_Ply + \"','\"+ 'L16Pos3'+dformat1+\"')\"\n\nmsg.topic = ''\nmsg.payload = pld\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":310,"y":240,"wires":[["edd22648.9fe2d8"]]},{"id":"65dd2a5d.84d244","type":"function","z":"4b3f21a3.ba434","name":"L16_POS16","func":"d = new Date,\ndformat = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('/')+' '+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join(':');\n    \ndformat1 = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('')+''+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join('');\n\ndtstmp = new Date().toString();\n\n\npld =        \"INSERT INTO [RedlionMQTT].[dbo].[POS16_L16] \"\npld = pld + \"(DateTime, POD, Pos16_op, Pos16_ETA, Pos16_Weight, Pos16_rdy,Pos16_Length,Pos16_1_2_cut,Pos16_All_cut,Pos16_DD,Detex,Number_Ply,POSK) \"\npld = pld + \"VALUES ('\"+ dformat+\"','\" +msg.payload.POD+ \"', '\" + msg.payload.Pos16_op + \"','\" + msg.payload.Pos16_ETA + \"','\" + msg.payload.Pos16_Weight + \"','\" + msg.payload.Pos16_rdy + \"','\" + msg.payload.Pos16_Length + \"','\" + msg.payload.Pos16_1_2_cut + \"','\" + msg.payload.Pos16_All_cut + \"','\" + msg.payload.Pos16_DD + \"','\" + msg.payload.Detex + \"','\" + msg.payload.Number_Ply + \"','\"+ 'L16Pos16'+dformat1+\"')\"\n\nmsg.topic = ''\nmsg.payload = pld\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":310,"y":1120,"wires":[[]]},{"id":"75bc5b1d.964a34","type":"function","z":"4b3f21a3.ba434","name":"L16_POS16","func":"d = new Date,\ndformat = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('/')+' '+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join(':');\n    \ndformat1 = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('')+''+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join('');\n\ndtstmp = new Date().toString();\n\n\npld =        \"INSERT INTO [RedlionMQTT].[dbo].[POS16_L16] \"\npld = pld + \"(DateTime, POD, Pos16_op, Pos16_ETA, Pos16_Weight, Pos16_rdy,Pos16_Length,Pos16_1_2_cut,Pos16_All_cut,Pos16_DD,Detex,Number_Ply,POSK) \"\npld = pld + \"VALUES ('\"+ dformat+\"','\" +msg.payload.POD+ \"', '\" + msg.payload.Pos16_op + \"','\" + msg.payload.Pos16_ETA + \"','\" + msg.payload.Pos16_Weight + \"','\" + msg.payload.Pos16_rdy + \"','\" + msg.payload.Pos16_Length + \"','\" + msg.payload.Pos16_1_2_cut + \"','\" + msg.payload.Pos16_All_cut + \"','\" + msg.payload.Pos16_DD + \"','\" + msg.payload.Detex + \"','\" + msg.payload.Number_Ply + \"','\"+ 'L16Pos16'+dformat1+\"')\"\n\nmsg.topic = ''\nmsg.payload = pld\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":310,"y":1180,"wires":[[]]},{"id":"e8567df0.85b2b","type":"function","z":"4b3f21a3.ba434","name":"L16_POS16","func":"d = new Date,\ndformat = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('/')+' '+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join(':');\n    \ndformat1 = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('')+''+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join('');\n\ndtstmp = new Date().toString();\n\n\npld =        \"INSERT INTO [RedlionMQTT].[dbo].[POS16_L16] \"\npld = pld + \"(DateTime, POD, Pos16_op, Pos16_ETA, Pos16_Weight, Pos16_rdy,Pos16_Length,Pos16_1_2_cut,Pos16_All_cut,Pos16_DD,Detex,Number_Ply,POSK) \"\npld = pld + \"VALUES ('\"+ dformat+\"','\" +msg.payload.POD+ \"', '\" + msg.payload.Pos16_op + \"','\" + msg.payload.Pos16_ETA + \"','\" + msg.payload.Pos16_Weight + \"','\" + msg.payload.Pos16_rdy + \"','\" + msg.payload.Pos16_Length + \"','\" + msg.payload.Pos16_1_2_cut + \"','\" + msg.payload.Pos16_All_cut + \"','\" + msg.payload.Pos16_DD + \"','\" + msg.payload.Detex + \"','\" + msg.payload.Number_Ply + \"','\"+ 'L16Pos16'+dformat1+\"')\"\n\nmsg.topic = ''\nmsg.payload = pld\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":310,"y":1240,"wires":[[]]},{"id":"f00b28db.d276c8","type":"function","z":"4b3f21a3.ba434","name":"L16_POS16","func":"d = new Date,\ndformat = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('/')+' '+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join(':');\n    \ndformat1 = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('')+''+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join('');\n\ndtstmp = new Date().toString();\n\n\npld =        \"INSERT INTO [RedlionMQTT].[dbo].[POS16_L16] \"\npld = pld + \"(DateTime, POD, Pos16_op, Pos16_ETA, Pos16_Weight, Pos16_rdy,Pos16_Length,Pos16_1_2_cut,Pos16_All_cut,Pos16_DD,Detex,Number_Ply,POSK) \"\npld = pld + \"VALUES ('\"+ dformat+\"','\" +msg.payload.POD+ \"', '\" + msg.payload.Pos16_op + \"','\" + msg.payload.Pos16_ETA + \"','\" + msg.payload.Pos16_Weight + \"','\" + msg.payload.Pos16_rdy + \"','\" + msg.payload.Pos16_Length + \"','\" + msg.payload.Pos16_1_2_cut + \"','\" + msg.payload.Pos16_All_cut + \"','\" + msg.payload.Pos16_DD + \"','\" + msg.payload.Detex + \"','\" + msg.payload.Number_Ply + \"','\"+ 'L16Pos16'+dformat1+\"')\"\n\nmsg.topic = ''\nmsg.payload = pld\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":310,"y":1300,"wires":[[]]},{"id":"1bd6f607.fb877a","type":"function","z":"4b3f21a3.ba434","name":"L16_POS16","func":"d = new Date,\ndformat = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('/')+' '+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join(':');\n    \ndformat1 = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('')+''+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join('');\n\ndtstmp = new Date().toString();\n\n\npld =        \"INSERT INTO [RedlionMQTT].[dbo].[POS16_L16] \"\npld = pld + \"(DateTime, POD, Pos16_op, Pos16_ETA, Pos16_Weight, Pos16_rdy,Pos16_Length,Pos16_1_2_cut,Pos16_All_cut,Pos16_DD,Detex,Number_Ply,POSK) \"\npld = pld + \"VALUES ('\"+ dformat+\"','\" +msg.payload.POD+ \"', '\" + msg.payload.Pos16_op + \"','\" + msg.payload.Pos16_ETA + \"','\" + msg.payload.Pos16_Weight + \"','\" + msg.payload.Pos16_rdy + \"','\" + msg.payload.Pos16_Length + \"','\" + msg.payload.Pos16_1_2_cut + \"','\" + msg.payload.Pos16_All_cut + \"','\" + msg.payload.Pos16_DD + \"','\" + msg.payload.Detex + \"','\" + msg.payload.Number_Ply + \"','\"+ 'L16Pos16'+dformat1+\"')\"\n\nmsg.topic = ''\nmsg.payload = pld\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":310,"y":1360,"wires":[[]]},{"id":"edd22648.9fe2d8","type":"MSSQL","z":"4b3f21a3.ba434","mssqlCN":"867426f7.2f6538","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"none","params":[],"x":970,"y":200,"wires":[[]]},{"id":"b611955e.b00bf8","type":"mqtt-broker","z":"","name":"mosca","broker":"10.104.32.105","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"867426f7.2f6538","type":"MSSQL-CN","z":"","name":"TCTME-TST","server":"10.104.32.107","encyption":true,"database":"RedlionMQTT"}]

JSON format output

INSERT INTO [RedlionMQTT].[dbo].[Pos1_L16] (DateTime, POD, Pos1_op, Pos1_ETA, Pos1_Weight, Pos1_rdy,Pos1_Length,Pos1_1_2_cut,Pos1_All_cut,Pos1_DD,Detex,Number_Ply,POSK) VALUES ('10/1/2020 22:41:44','EXT037897', '72.609','41','12.63262','OFF','11.868','OFF','OFF','OFF','0','0','L16POS11012020224144')

Nodi.Rubrum · 1 October 2020 18:59

Ok, lets step back a second. Basic SQL insert design is that 1 BIG insert is better than many small ones. This means you stage data, and then push to SQL database in one large commit. There are many reasons to do this, for example connection creation is expensive in terms of SQL server resources.

Why do you need to do so many things in explicit parallel? Maybe explain the logic of the design, before we qualify the flows for said design? I think you may want to define the problem better before we jump to a solution?

If you ask why I am asking this? Well, in a 30+ year career in IT, I have supported may DB environments, even designed DB infrastructure, etc. So I am suggesting the above based on my considered experience with data queueing and forwarding as well as DB instructure.

Steve-Mcl · 1 October 2020 19:02

Ok, so now i see what your issue is - it is a poor SQL table desgn that is forcing you to generate separate SQL insert statements.

table 1 - [Pos1_L16] "
table 2 - [POS2_L16] "
table 3 - [POS3_L16] "

field names for table Pos1_L16 - DateTime, POD, Pos1_op, Pos1_ETA, Pos1_Weight, etc etc etc
field names for table Pos2_L16 - DateTime, POD, Pos2_op, Pos2_ETA, Pos2_Weight, etc etc etc
field names for table Pos2_L16 - DateTime, POD, Pos2_op, Pos2_ETA, Pos2_Weight, etc etc etc

If you had named the fields the same in all tables, you could have a single SQL statement
- e.g. DateTime, POD, op, ETA, Weight, etc etc etc
If you had 1 table you could greatly simplify this
- if you have 1 table to insert into, you would need only need a POS field to identify the POS
- e.g. DateTime, POS, POD, op, ETA, Weight, etc etc etc

Some questions...

Are you able to change the SQL in order to greatly streamline the solution?

Could you please provide data as captured from the JSON node...

alikhan · 1 October 2020 19:08

Yes , i can change the SQL structure.

JSON format:

{"topic":"L16/","payload":"{\"connected\":1,\"Detex\":0,\"Number_Ply\":0,\"POD\":\"EXT037897\",\"Pos10_1_2_cut\":\"OFF\",\"Pos10_All_Cut\":\"OFF\",\"Pos10_DD\":\"OFF\",\"Pos10_ETA\":14,\"Pos10_Length\":14.74913,\"Pos10_op\":90.426,\"Pos10_Rdy\":\"OFF\",\"Pos10_Weight\":14.53442,\"Pos11_1_2_cut\":\"OFF\",\"Pos11_All_Cut\":\"OFF\",\"Pos11_DD\":\"OFF\",\"Pos11_ETA\":17,\"Pos11_Length\":14.51609,\"Pos11_op\":88.979,\"Pos11_Rdy\":\"OFF\",\"Pos11_Weight\":14.38062,\"Pos12_1_2_cut\":\"OFF\",\"Pos12_All_Cut\":\"OFF\",\"Pos12_DD\":\"OFF\",\"Pos12_ETA\":17,\"Pos12_Length\":14.46599,\"Pos12_op\":88.672,\"Pos12_Rdy\":\"OFF\",\"Pos12_Weight\":14.34755,\"Pos13_1_2_cut\":\"OFF\",\"Pos13_All_Cut\":\"OFF\",\"Pos13_DD\":\"OFF\",\"Pos13_ETA\":15,\"Pos13_Length\":14.69537,\"Pos13_op\":90.251,\"Pos13_Rdy\":\"OFF\",\"Pos13_Weight\":14.49894,\"Pos14_1_2_cut\":\"OFF\",\"Pos14_All_Cut\":\"OFF\",\"Pos14_DD\":\"OFF\",\"Pos14_ETA\":15,\"Pos14_Length\":14.66638,\"Pos14_op\":89.917,\"Pos14_Rdy\":\"OFF\",\"Pos14_Weight\":14.47981,\"Pos15_1_2_cut\":\"OFF\",\"Pos15_All_Cut\":\"OFF\",\"Pos15_DD\":\"OFF\",\"Pos15_ETA\":14,\"Pos15_Length\":14.73197,\"Pos15_op\":90.302,\"Pos15_Rdy\":\"OFF\",\"Pos15...","qos":0,"retain":false,"_msgid":"d07f3000.d48ab"}

Steve-Mcl · 1 October 2020 19:15

2 things.

That data is no good - please use the copy button on the debug
Paste the data between backticks
```
like this
```

alikhan · 1 October 2020 19:18

Same way copied:

'''{"topic":"L16/","payload":"{"connected":1,"Detex":0,"Number_Ply":0,"POD":"EXT037897","Pos10_1_2_cut":"OFF","Pos10_All_Cut":"OFF","Pos10_DD":"OFF","Pos10_ETA":14,"Pos10_Length":14.74913,"Pos10_op":90.426,"Pos10_Rdy":"OFF","Pos10_Weight":14.53442,"Pos11_1_2_cut":"OFF","Pos11_All_Cut":"OFF","Pos11_DD":"OFF","Pos11_ETA":17,"Pos11_Length":14.51609,"Pos11_op":88.979,"Pos11_Rdy":"OFF","Pos11_Weight":14.38062,"Pos12_1_2_cut":"OFF","Pos12_All_Cut":"OFF","Pos12_DD":"OFF","Pos12_ETA":17,"Pos12_Length":14.46599,"Pos12_op":88.672,"Pos12_Rdy":"OFF","Pos12_Weight":14.34755,"Pos13_1_2_cut":"OFF","Pos13_All_Cut":"OFF","Pos13_DD":"OFF","Pos13_ETA":15,"Pos13_Length":14.69537,"Pos13_op":90.251,"Pos13_Rdy":"OFF","Pos13_Weight":14.49894,"Pos14_1_2_cut":"OFF","Pos14_All_Cut":"OFF","Pos14_DD":"OFF","Pos14_ETA":15,"Pos14_Length":14.66638,"Pos14_op":89.917,"Pos14_Rdy":"OFF","Pos14_Weight":14.47981,"Pos15_1_2_cut":"OFF","Pos15_All_Cut":"OFF","Pos15_DD":"OFF","Pos15_ETA":14,"Pos15_Length":14.73197,"Pos15_op":90.302,"Pos15_Rdy":"OFF","Pos15...","qos":0,"retain":false,"_msgid":"d07f3000.d48ab"}'''

alikhan · 1 October 2020 19:22

again:

Only payload msg:
'''
{"connected":1,"Detex":0,"Number_Ply":0,"POD":"EXT037897","Pos10_1_2_cut":"OFF","Pos10_All_Cut":"OFF","Pos10_DD":"OFF","Pos10_ETA":2,"Pos10_Length":16.45928,"Pos10_op":101.112,"Pos10_Rdy":"ON","Pos10_Weight":15.66313,"Pos11_1_2_cut":"OFF","Pos11_All_Cut":"OFF","Pos11_DD":"OFF","Pos11_ETA":1,"Pos11_Length":16.23375,"Pos11_op":99.522,"Pos11_Rdy":"ON","Pos11_Weight":15.51428,"Pos12_1_2_cut":"OFF","Pos12_All_Cut":"OFF","Pos12_DD":"OFF","Pos12_ETA":1,"Pos12_Length":16.18431,"Pos12_op":99.202,"Pos12_Rdy":"ON","Pos12_Weight":15.49117,"Pos13_1_2_cut":"OFF","Pos13_All_Cut":"OFF","Pos13_DD":"OFF","Pos13_ETA":1,"Pos13_Length":16.43628,"Pos13_op":100.657,"Pos13_Rdy":"ON","Pos13_Weight":15.64794,"Pos14_1_2_cut":"OFF","Pos14_All_Cut":"OFF","Pos14_DD":"OFF","Pos14_ETA":1,"Pos14_Length":16.40729,"Pos14_op":100.479,"Pos14_Rdy":"ON","Pos14_Weight":15.62881,"Pos15_1_2_cut":"OFF","Pos15_All_Cut":"OFF","Pos15_DD":"OFF","Pos15_ETA":1,"Pos15_Length":16.48587,"Pos15_op":100.960,"Pos15_Rdy":"ON","Pos15_Weight"...'''

Steve-Mcl · 1 October 2020 19:24

So 1 MAJOR problem I see is ...

you want 68 of these inserts - so I imagine you have tables Pos1_L16 --> Pos68_L16 ?
is that correct ?

HOWEVER - I dont see all the data in your payload. I see "pos10" and Pos11... through to Pos15

Another thing, the payload you are showing is a STRING (JSON) - it needs to be a JS object.
Did you capture this BEFORE or AFTER the JSON node?

This cannot work as it is

alikhan · 1 October 2020 19:30

Yes i have tables with POS1 to POS68.
till now i did connect all the nodes that why you cannot see the data in the payload.

with the connected nodes it is working fine with the database.

Steve-Mcl · 1 October 2020 19:33

Would you be willing to make a single table with an simple field names and an extra column to identify the POS?

e.g...

datetime, pos, pod, op, eta, weight, rdy, [length], etc, etc, etc

If so, I can simplify this to a very small flow.

alikhan · 1 October 2020 19:35

yes we can try.

Steve-Mcl · 1 October 2020 19:38

ok, so make a new table called L16

and ensure the field names are...

eventtime, pos, pod, 
    op, eta, [weight], rdy,[length], cut, 
    all_cut, dd, detex, number_ply, posk

tell me when you have that table ready

Topic		Replies	Views
Joining msg.payload and then sending to SQLite. Two entries instead of one General database , function-node , mqtt	3	278	30 October 2022
How to do a simple sum of 2 msgs General	27	29358	2 January 2022
Generate SQL statement from 2 mqtt messages General	42	822	20 July 2020
mySQL INSERT INTO with multiple msg.xxx failing General	15	2882	28 November 2020
MSSQL-Plus - Appending the Query output to incoming payload.msg General	3	337	23 October 2021

Combine 2 different payload into function

Related Topics