Json object save to mysql Error ER_PARSE_ERROR

General
1

Hello i have a problem i need to save data from json object and save to mysql my code like this for example

var newMSG = msg.payload;

timestamp = newMSG.rx.gwrx[0].time.split(' ');
date = { payload: timestamp[0]};
time = { payload: timestamp[1]};
pulse_base64 = {payload: newMSG.rx.userdata.payload};
device_no = {payload: newMSG.rx.moteeui};
port = {payload: newMSG.rx.userdata.port};
rssi = {payload: newMSG.rx.gwrx[0].rssi};
msg.topic = "INSERT INTO `electro_count` (`date`, `time`, `start_t`, `end_t`, `flow`, `pulse`, `device_no`, `rssi`, `port`) VALUES ("+date+","+time+","+"1"+","+"2"+","+"3"+","+pulse_base64+","+device_no+","+rssi+","+port+")";
return msg ;

My Database Field

wvwvw
wvwvw.PNG1003×317 38.2 KB

my json object
s

ERROR
wfwfwf

I'm newbie for this JS Thank

2

Put a debug node in and see what query you are passing to the dB node

3

image
image.png1118×554 43.9 KB

look like this

4

Change the debug you added to show the complete message object not just the msg.payload.

The copy and paste the message here rather than paste a screenshot, Screenshots are very difficult to read on a phone

5

Error Show like this & I Update My Full Code

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[object Object],[object Object],1,2,3,[object Object],[object Object],[object Ob' at line 1

6

That’s the output from your sql node, the one after your function would be more useful

7

can see my flow i can't find what i miss

[{"id":"2f67da4a.583c56","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"4532acd2.ad087c","type":"function","z":"2f67da4a.583c56","name":"time","func":"var newMSG = msg.payload;\n\ntimestamp = newMSG.rx.gwrx[0].time.split(' ');\n time = { payload: timestamp[1]};\nreturn msg,time;","outputs":1,"noerr":0,"x":305.7143249511719,"y":464.4285060337612,"wires":[[]]},{"id":"43c55811.743b9","type":"function","z":"2f67da4a.583c56","name":"pulse","func":"var newMSG = msg.payload;\n\npulse_base64 = {payload: newMSG.rx.userdata.payload};\n\nreturn msg , pulse_base64;","outputs":1,"noerr":0,"x":274.71434238978793,"y":524.5713936941963,"wires":[[]]},{"id":"1489cbcf.fb3d0c","type":"function","z":"2f67da4a.583c56","name":"device_no","func":"var newMSG = msg.payload;\n\n\n device_no = {payload: newMSG.rx.moteeui};\n\n\nreturn msg ,device_no;","outputs":1,"noerr":0,"x":261.85719953264504,"y":570.2856794084821,"wires":[[]]},{"id":"c7fa6d0c.e25c4","type":"function","z":"2f67da4a.583c56","name":"port","func":"var newMSG = msg.payload;\n\n port = {payload: newMSG.rx.userdata.port};\n\nreturn msg;","outputs":1,"noerr":0,"x":241.85719953264504,"y":647.4285365513394,"wires":[[]]},{"id":"dac0c407.e99a68","type":"function","z":"2f67da4a.583c56","name":"rssi","func":"var newMSG = msg.payload;\n\n\n rssi = {payload: newMSG.rx.gwrx[0].rssi};\nreturn msg ,rssi;","outputs":1,"noerr":0,"x":226.1429050990513,"y":704.5714460100446,"wires":[[]]},{"id":"8bf26dbc.fb14f","type":"switch","z":"2f67da4a.583c56","name":"","property":"payload","propertyType":"msg","rules":[{"t":"index","v":"","vt":"num","v2":"","v2t":"num"}],"checkall":"true","repair":false,"outputs":1,"x":561.8571036202566,"y":638.8571602957588,"wires":[[]]},{"id":"a7456227.c1649","type":"function","z":"2f67da4a.583c56","name":"date","func":"var newMSG = msg.payload;\n\ntimestamp = newMSG.rx.gwrx[0].time.split(' ');\n date = { payload: timestamp[0]};\nreturn msg , date;","outputs":1,"noerr":0,"x":316.14291381835926,"y":410.2856794084821,"wires":[[]]},{"id":"34869f7a.3201b8","type":"json","z":"2f67da4a.583c56","name":"","property":"payload","action":"","pretty":false,"x":235.57138061523438,"y":172.14285278320312,"wires":[["e9cb6f59.836118","cfd5edc7.b29008"]]},{"id":"e39876c1.68cf78","type":"udp in","z":"2f67da4a.583c56","name":"","iface":"","port":"1234","ipv":"udp4","multicast":"false","group":"","datatype":"utf8","x":159.28564889090399,"y":58.857147216796875,"wires":[["6d042ab8.2cac24","34869f7a.3201b8"]]},{"id":"6d042ab8.2cac24","type":"debug","z":"2f67da4a.583c56","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":422.285648890904,"y":52.857147216796875,"wires":[]},{"id":"e9cb6f59.836118","type":"debug","z":"2f67da4a.583c56","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":600.7143162318638,"y":96.28569466727117,"wires":[]},{"id":"cfd5edc7.b29008","type":"function","z":"2f67da4a.583c56","name":"save_db_lora","func":"var newMSG = msg.payload;\n\ntimestamp = newMSG.rx.gwrx[0].time.split(' ');\n date = { payload: timestamp[0]};\n time = { payload: timestamp[1]};\npulse_base64 = {payload: newMSG.rx.userdata.payload};\n device_no = {payload: newMSG.rx.moteeui};\n port = {payload: newMSG.rx.userdata.port};\n rssi = {payload: newMSG.rx.gwrx[0].rssi};\n\nmsg.topic = \"INSERT INTO `electro_count` (`date`, `time`, `start_t`, `end_t`, `flow`, `pulse`, `device_no`, `rssi`, `port`) VALUES (\"+date+\",\"+time+\",\"+time+\",\"+time+\",\"+time+\",\"+pulse_base64+\",\"+device_no+\",\"+rssi+\",\"+port+\")\";\nreturn msg ;","outputs":1,"noerr":0,"x":539.4287109375,"y":278.0715026855469,"wires":[["99269b1b.6b05f8","2e27cb1d.fb631c"]]},{"id":"99269b1b.6b05f8","type":"debug","z":"2f67da4a.583c56","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":764.7143162318639,"y":384.57150704520086,"wires":[]},{"id":"2e27cb1d.fb631c","type":"mysql","z":"2f67da4a.583c56","mydb":"108076b6.141e21","name":"","x":1061.428671700614,"y":336.8929792131696,"wires":[["75a2f1a.ceae81"]]},{"id":"75a2f1a.ceae81","type":"debug","z":"2f67da4a.583c56","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1110.7500828334264,"y":502.39300973074774,"wires":[]},{"id":"108076b6.141e21","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"vnsoftt_elec","tz":""}]



Json Object
    {"rx":{"moteeui":"00-00-00-00-45-53-a0-02","userdata":     {"seqno":3,"port":7,"payload":"MjIwMA==","motetx":{"freq":922000000,"modu":"LoRa","datr":"SF12BW125","codr":"4/5"}},"gwrx":[{"time":"2018-11-16 08:52:45","chan":0,"rfch":0,"rssi":-27,"lsnr":8.2}]}}
8

Look at the output from the debug node on the function node. Does that look as you expect? If not then consider what may be wrong in the function node. It looks to me as if you may be inserting a message object in the msg.topic = line rather than simple values.

9

Yep, but as I’m on my iPhone I have no way of importing your flow. But I could have looked at the debug panel output whilst I sat on the train.

10
  1. in your function - why are you using var newMSG = msg.payload; and nt just addessing the data right from msg.payload?
  2. change the debug node on the output of the JSON node to display the complete msg object

Now you can copy the path by clicking the >_ after an item
nr-path
and use that path (prefixed with 'msg'). for example you would have
timestamp = msg.payload.rx.gwrx[0].time.split(' ');

do that and check the results