Generate SQL statement from 2 mqtt messages

General
21

Steve, for what its worth, there is the complete flow

[{"id":"c7ac4dd1.1d648","type":"tab","label":"New Acc Request","disabled":false,"info":""},{"id":"3fba7a44.43c286","type":"debug","z":"c7ac4dd1.1d648","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":650,"y":160,"wires":[]},{"id":"168fe769.a484b9","type":"mqtt in","z":"c7ac4dd1.1d648","name":"PSA001 - QR Value","topic":"accreq","qos":"0","datatype":"auto","broker":"57c985f0.a39e8c","x":170,"y":160,"wires":[["3fba7a44.43c286","64b4ac9b.9d95a4","38b16044.77229","a9dd3d9f.c5e95","2509d6f9.9757ba","14db38cc.16e097"]]},{"id":"64b4ac9b.9d95a4","type":"ui_text","z":"c7ac4dd1.1d648","group":"6771f7fc.19f008","order":2,"width":26,"height":1,"name":"PSA001 - QR Value Dissplay","label":"PSA001 - DoorID:001 - QR HASH Value : ","format":"{{msg.payload}}","layout":"row-left","x":720,"y":200,"wires":[]},{"id":"9bfc35d3.f29208","type":"rpi-gpio out","z":"c7ac4dd1.1d648","name":"PSA001 INStatus  LED","pin":"33","set":true,"level":"0","freq":"","out":"out","x":1100,"y":120,"wires":[]},{"id":"829ab0fc.2211b","type":"ui_led","z":"c7ac4dd1.1d648","group":"6771f7fc.19f008","order":1,"width":4,"height":1,"label":"Recived ","labelPlacement":"left","labelAlignment":"left","colorForValue":[{"color":"red","value":"0","valueType":"num"},{"color":"green","value":"1","valueType":"num"}],"allowColorForValueInMessage":false,"name":"","x":1050,"y":80,"wires":[]},{"id":"38b16044.77229","type":"trigger","z":"c7ac4dd1.1d648","op1":"1","op2":"0","op1type":"num","op2type":"str","duration":"5","extend":false,"units":"s","reset":"5","bytopic":"all","name":"","x":660,"y":120,"wires":[["829ab0fc.2211b","9bfc35d3.f29208"]]},{"id":"f73eb69a.3337b8","type":"sqlite","z":"c7ac4dd1.1d648","mydb":"9db34194.66aca","sqlquery":"msg.topic","sql":"","name":"PSA-DB ","x":1540,"y":320,"wires":[["25617dd6.b83862"]]},{"id":"25617dd6.b83862","type":"debug","z":"c7ac4dd1.1d648","name":"Inseted in to DB ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1760,"y":320,"wires":[]},{"id":"10aa3ead.9de511","type":"mqtt in","z":"c7ac4dd1.1d648","name":"PSA001 - OTP","topic":"psa001/d1/user_input ","qos":"0","datatype":"json","broker":"57c985f0.a39e8c","x":120,"y":580,"wires":[["7ed0ff44.ba906","8cae8503.6cf648"]]},{"id":"7ed0ff44.ba906","type":"simpletime","z":"c7ac4dd1.1d648","name":"Time Stamp ","mydate":false,"myymd":true,"myyear":false,"mymonth":false,"mymonthn":false,"mydom":false,"mydoy":false,"myday":false,"myhourpm":false,"myhour":false,"mytime":false,"mytimes":true,"myminute":false,"myminutes":false,"mysecond":false,"mymillis":false,"myepoch":false,"myrawdate":false,"mypm":false,"x":570,"y":580,"wires":[["14db38cc.16e097"]]},{"id":"a9dd3d9f.c5e95","type":"delay","z":"c7ac4dd1.1d648","name":"Delay 2sec","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":130,"y":420,"wires":[["c63e22af.c892c"]]},{"id":"cb55e176.c7aa7","type":"change","z":"c7ac4dd1.1d648","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"complete","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":420,"wires":[["14db38cc.16e097"]]},{"id":"40ebe0ca.2bbef","type":"debug","z":"c7ac4dd1.1d648","name":"Output from PSA DB ACCREQ Funtion ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1670,"y":420,"wires":[]},{"id":"c63e22af.c892c","type":"trigger","z":"c7ac4dd1.1d648","op1":"true","op2":"0","op1type":"bool","op2type":"str","duration":"0","extend":false,"units":"ms","reset":"5","bytopic":"all","name":"Trigger TRUE Value ","x":380,"y":420,"wires":[["cb55e176.c7aa7","1f8956cf.724ea9"]]},{"id":"1f8956cf.724ea9","type":"trigger","z":"c7ac4dd1.1d648","op1":"5","op2":"","op1type":"str","op2type":"str","duration":"0","extend":false,"units":"s","reset":"true ","bytopic":"all","name":"","x":380,"y":500,"wires":[["c63e22af.c892c"]]},{"id":"2509d6f9.9757ba","type":"debug","z":"c7ac4dd1.1d648","name":"Output from MQTT-PSA001/ QR Value in SHA256 key ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":500,"y":60,"wires":[]},{"id":"8cae8503.6cf648","type":"debug","z":"c7ac4dd1.1d648","name":"Output from MQTT-PSA001/ User Data before Hash  ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":440,"y":640,"wires":[]},{"id":"14db38cc.16e097","type":"join","z":"c7ac4dd1.1d648","name":"Wait and Join ","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":980,"y":420,"wires":[["6b2f2e8f.074b3"]]},{"id":"6b2f2e8f.074b3","type":"function","z":"c7ac4dd1.1d648","name":"PSA DB - Insert - ACCREQ","func":"var now = (new Date()).toISOString().slice(0,-5);\nvar dateParts = now.split(\"T\");\n\nvar door_id= msg.payload[\"psa001/d1/user_input \"][\"Door ID\"];\nvar door_otp= msg.payload[\"psa001/d1/user_input \"].OTP;\nvar hashkey= msg.payload.accreq;\nvar _msgid= msg._msgid;\nvar date = dateParts[0];\nvar time = dateParts[1];\n\n\n//create SQL command\nmsg.topic = `INSERT INTO ACCREQ (\n  DATE, TIME, MSGID_ID, DOOR_ID, DOOR_OTP, HASHKEY\n) values (\n   '${date}', '${time}', '${_msgid}', ${door_id}, ${door_otp} ,${hashkey}\n);`\nreturn msg;","outputs":1,"noerr":0,"x":1280,"y":420,"wires":[["40ebe0ca.2bbef","f73eb69a.3337b8"]]},{"id":"7d55d1f5.c17ca","type":"function","z":"c7ac4dd1.1d648","name":"Read DB for user 1 ","func":"SELECT * FROM USERS \n \nreturn msg;\n\nthen\n\n// use the result from the db quiry to encrypt one by one with the \"Door_id\"+\"Door_otp\" \n\nvar user_name= msg.payload.user_name;\nvar user_surname= msg.payload.User_surname;\nvar user_pin= msg.payload.user_pin;\nvar user_acc_level= msg.payload.user_acc_level;\nvar user_acc_code= msg.payload.user_acc_code;\n\n\n","outputs":1,"noerr":7,"x":830,"y":700,"wires":[["3c6b0de4.0b9972","e62cf257.713a2"]]},{"id":"3c6b0de4.0b9972","type":"digest","z":"c7ac4dd1.1d648","name":"Encrypt User Data to SHA256","algorithm":"SHA256","x":1150,"y":700,"wires":[["879b5636.0469c8"]]},{"id":"a13617c0.70a068","type":"function","z":"c7ac4dd1.1d648","name":"Read DB for ACCREQ Data ","func":"SELECT * FROM USERS \n \nreturn msg;\n\nthen\n\n// use the result from the db quiry to encrypt one by one with the \"Door_id\"+\"Door_otp\" \n\nvar user_name= msg.payload.user_name;\nvar user_surname= msg.payload.User_surname;\nvar user_pin= msg.payload.user_pin;\nvar user_acc_level= msg.payload.user_acc_level;\nvar user_acc_code= msg.payload.user_acc_code;\n\n\n","outputs":1,"noerr":7,"x":800,"y":780,"wires":[["e62cf257.713a2","3c6b0de4.0b9972"]]},{"id":"879b5636.0469c8","type":"match","z":"c7ac4dd1.1d648","name":"","rules":[{"property":"payload","propertyType":"msg","type":"cont","value":"payload.hashkey ","valueType":"msg"}],"x":1390,"y":740,"wires":[["8b74c76.b8ad738","f181a09a.02854","b5ccb127.0dfa9","3c06878b.2428f8"],["8b74c76.b8ad738","f181a09a.02854"]]},{"id":"b5ccb127.0dfa9","type":"mqtt out","z":"c7ac4dd1.1d648","name":"Notify Base Station node ","topic":"server/accreq/result ","qos":"","retain":"","broker":"d8ebb20f.0a884","x":1770,"y":580,"wires":[]},{"id":"8b74c76.b8ad738","type":"ui_led","z":"c7ac4dd1.1d648","group":"162eae12.1020b2","order":1,"width":0,"height":0,"label":"Access Status ","labelPlacement":"left","labelAlignment":"left","colorForValue":[{"color":"red","value":"false","valueType":"bool"},{"color":"green","value":"true","valueType":"bool"}],"allowColorForValueInMessage":false,"name":"Access Status ","x":1720,"y":740,"wires":[]},{"id":"f181a09a.02854","type":"ui_text","z":"c7ac4dd1.1d648","group":"162eae12.1020b2","order":0,"width":0,"height":0,"name":"Granted / Denied ","label":"Access ","format":"{{msg.payload}}","layout":"row-spread","x":1730,"y":820,"wires":[]},{"id":"3c06878b.2428f8","type":"mqtt out","z":"c7ac4dd1.1d648","name":"Notify Door node ","topic":"server/accreq/result ","qos":"","retain":"","broker":"d8ebb20f.0a884","x":1730,"y":660,"wires":[]},{"id":"e62cf257.713a2","type":"digest","z":"c7ac4dd1.1d648","name":"Encrypt User Data to SHA256","algorithm":"SHA256","x":1150,"y":780,"wires":[["879b5636.0469c8"]]},{"id":"57c985f0.a39e8c","type":"mqtt-broker","z":"","name":"PSA Server 1","broker":"localhost ","port":"1883","clientid":"psa003","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"6771f7fc.19f008","type":"ui_group","z":"","name":"Traffic From Psa001","tab":"8d4ff28.725211","order":1,"disp":true,"width":30,"collapse":false},{"id":"9db34194.66aca","type":"sqlitedb","z":"","db":"psa.db","mode":"RWC"},{"id":"d8ebb20f.0a884","type":"mqtt-broker","z":"","name":"PSA Server 1","broker":"10.11.12.101","port":"1880","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"162eae12.1020b2","type":"ui_group","z":"","name":"PSA-VPN / Pass: Psa@123! - Please Scan QR to get access to PSA-QR GEN-SYST ","tab":"24e16ee9.7041c2","order":2,"disp":true,"width":15,"collapse":false},{"id":"8d4ff28.725211","type":"ui_tab","z":"","name":"Traffic","icon":"dashboard","order":3,"disabled":false,"hidden":false},{"id":"24e16ee9.7041c2","type":"ui_tab","z":"","name":"IP ACCESS ","icon":"dashboard","disabled":false,"hidden":false}]
22

So compare the output of the mqtt in nodes to the sample data you sent me. Do they have the same keys? In particular, does the msg.payload contain a property "Door ID" like the sample data you sent does?

23

Oh - I see what you have done.

You need to pretty much lose all that crap in your flow and use what I developed for you.

two minutes...

My flow...

image
image800×177 14.9 KB

Your flow...

image
image1516×682 73.7 KB

So where I wrote Fake MQTT - you were supposed to feed YOUR MQTT nodes to where the FAKE messages went - like this...

image
image1549×701 88.6 KB

To clarify...

  • I faked your MQTT using the DATA you sent in previous post.
  • So when you take my flow - replace the FAKE data with the REAL data & where I had a debug node "To Database" to connect to your data base node

Not sure how I could be clearer?

Question - did you import my flow & deploy it and actually press the buttons - to see how it works & witness the generation of a VALID SQL statement (like in my screen shot) and inspect the data / code to see how it worked?

1 Like
24

Steve, thanks for the explanation , let me see if i can digest his ..

25

Hey Steve , Just to be clear , if you review the "complete flow" i have posted earlier, you would see that i did indeed implemented your newly developed nodes and push the buttons , and posted the results to you.

i will try to re implement the nodes as instructed this time around .

please for give me for the stupidity , i am really trying to learn this as i really enjoy node red .

give me 2 min..

26

accreq flow
accreq flow947×359 52.4 KB

27

OK , Steve , I still get the same Errors as before and implemented the nodes as instructed ,

Response from Join Node ,

{"topic":"accreq","payload":{"psa001/d1/user_input ":{"Name ":"Pieter","Surname":"Hattingh","PIN":5054,"OTP":1234,"Door ID":1},"accreq":"74657ce6651d26d4650b2da3c16127de6017ed409492fcf62a0a48eee27c2667"},"qos":0,"retain":false,"_msgid":"5d9caa65.05e1d4"}

Response from Function Node:

{"topic":"INSERT INTO ACCREQ (\n  DATE, TIME, MSGID_ID, DOOR_ID, DOOR_OTP, HASHKEY\n) values (\n   '2020-07-06', '20:48:01', '5d9caa65.05e1d4', 1, 1234 ,74657ce6651d26d4650b2da3c16127de6017ed409492fcf62a0a48eee27c2667\n);","payload":{"psa001/d1/user_input ":{"Name ":"Pieter","Surname":"Hattingh","PIN":5054,"OTP":1234,"Door ID":1},"accreq":"74657ce6651d26d4650b2da3c16127de6017ed409492fcf62a0a48eee27c2667"},"qos":0,"retain":false,"_msgid":"5d9caa65.05e1d4"}

Response From DB node

Error: SQLITE_ERROR: unrecognized token: "74657ce6651d26d4650b2da3c16127de6017ed409492fcf62a0a48eee27c2667"

what now?

28

Post your latest flow.

And post the debug data from the 2 mqtt nodes like before.

Ps, glad you're enjoying it. It's both addictive and frustrating all at the same time. And you will get there.

29

Cool, i will post in a min. thanks for everything Steve , i really need you to be patient with me ...hahah

1 Like
30

Here with my complete flow with for nodes implemented '

[{"id":"c7ac4dd1.1d648","type":"tab","label":"New Acc Request","disabled":false,"info":""},{"id":"3fba7a44.43c286","type":"debug","z":"c7ac4dd1.1d648","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":650,"y":160,"wires":[]},{"id":"168fe769.a484b9","type":"mqtt in","z":"c7ac4dd1.1d648","name":"PSA001 - QR Value","topic":"accreq","qos":"0","datatype":"auto","broker":"57c985f0.a39e8c","x":210,"y":160,"wires":[["3fba7a44.43c286","64b4ac9b.9d95a4","38b16044.77229","8bf18653.85df88","f390248e.456428"]]},{"id":"64b4ac9b.9d95a4","type":"ui_text","z":"c7ac4dd1.1d648","group":"6771f7fc.19f008","order":2,"width":26,"height":1,"name":"PSA001 - QR Value Dissplay","label":"PSA001 - DoorID:001 - QR HASH Value : ","format":"{{msg.payload}}","layout":"row-left","x":720,"y":200,"wires":[]},{"id":"9bfc35d3.f29208","type":"rpi-gpio out","z":"c7ac4dd1.1d648","name":"PSA001 INStatus  LED","pin":"33","set":true,"level":"0","freq":"","out":"out","x":1100,"y":120,"wires":[]},{"id":"829ab0fc.2211b","type":"ui_led","z":"c7ac4dd1.1d648","group":"6771f7fc.19f008","order":1,"width":4,"height":1,"label":"Recived ","labelPlacement":"left","labelAlignment":"left","colorForValue":[{"color":"red","value":"0","valueType":"num"},{"color":"green","value":"1","valueType":"num"}],"allowColorForValueInMessage":false,"name":"","x":1050,"y":80,"wires":[]},{"id":"38b16044.77229","type":"trigger","z":"c7ac4dd1.1d648","op1":"1","op2":"0","op1type":"num","op2type":"str","duration":"5","extend":false,"units":"s","reset":"5","bytopic":"all","name":"","x":660,"y":120,"wires":[["829ab0fc.2211b","9bfc35d3.f29208"]]},{"id":"f73eb69a.3337b8","type":"sqlite","z":"c7ac4dd1.1d648","mydb":"9db34194.66aca","sqlquery":"msg.topic","sql":"","name":"PSA-DB ","x":1420,"y":380,"wires":[["25617dd6.b83862"]]},{"id":"25617dd6.b83862","type":"debug","z":"c7ac4dd1.1d648","name":"Inseted in to DB ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1680,"y":380,"wires":[]},{"id":"10aa3ead.9de511","type":"mqtt in","z":"c7ac4dd1.1d648","name":"PSA001 - OTP","topic":"psa001/d1/user_input ","qos":"0","datatype":"json","broker":"57c985f0.a39e8c","x":200,"y":520,"wires":[["8bf18653.85df88","76843908.cf6198"]]},{"id":"40ebe0ca.2bbef","type":"debug","z":"c7ac4dd1.1d648","name":"Output from PSA DB ACCREQ Funtion ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1390,"y":560,"wires":[]},{"id":"6b2f2e8f.074b3","type":"function","z":"c7ac4dd1.1d648","name":"PSA DB - Insert - ACCREQ","func":"var now = (new Date()).toISOString().slice(0,-5);\nvar dateParts = now.split(\"T\");\n\nvar door_id= msg.payload[\"psa001/d1/user_input \"][\"Door ID\"];\nvar door_otp= msg.payload[\"psa001/d1/user_input \"].OTP;\nvar hashkey= msg.payload.accreq;\nvar _msgid= msg._msgid;\nvar date = dateParts[0];\nvar time = dateParts[1];\n\n\n//create SQL command\nmsg.topic = `INSERT INTO ACCREQ (\n  DATE, TIME, MSGID_ID, DOOR_ID, DOOR_OTP, HASHKEY\n) values (\n   '${date}', '${time}', '${_msgid}', ${door_id}, ${door_otp} ,${hashkey}\n);`\nreturn msg;","outputs":1,"noerr":0,"x":1080,"y":380,"wires":[["40ebe0ca.2bbef","f73eb69a.3337b8"]]},{"id":"8bf18653.85df88","type":"join","z":"c7ac4dd1.1d648","name":"Wait and Join ","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":600,"y":380,"wires":[["7b6d9443.e2387c","6b2f2e8f.074b3"]]},{"id":"7b6d9443.e2387c","type":"debug","z":"c7ac4dd1.1d648","name":"check it","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":780,"y":560,"wires":[]},{"id":"42e1b365.b8d08c","type":"comment","z":"c7ac4dd1.1d648","name":"Join 2 messages by topic","info":"","x":610,"y":320,"wires":[]},{"id":"c397bbf4.736848","type":"comment","z":"c7ac4dd1.1d648","name":"Build SQL","info":"","x":1080,"y":320,"wires":[]},{"id":"f390248e.456428","type":"debug","z":"c7ac4dd1.1d648","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":420,"y":60,"wires":[]},{"id":"76843908.cf6198","type":"debug","z":"c7ac4dd1.1d648","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":390,"y":600,"wires":[]},{"id":"57c985f0.a39e8c","type":"mqtt-broker","z":"","name":"PSA Server 1","broker":"localhost ","port":"1883","clientid":"psa003","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"6771f7fc.19f008","type":"ui_group","z":"","name":"Traffic From Psa001","tab":"8d4ff28.725211","order":1,"disp":true,"width":30,"collapse":false},{"id":"9db34194.66aca","type":"sqlitedb","z":"","db":"psa.db","mode":"RWC"},{"id":"8d4ff28.725211","type":"ui_tab","z":"","name":"Traffic","icon":"dashboard","order":3,"disabled":false,"hidden":false}]

debug on MQTT1 ,

7/6/2020, 11:01:32 PMnode: f390248e.456428
accreq : msg.payload : string[64]
"74657ce6651d26d4650b2da3c16127de6017ed409492fcf62a0a48eee27c2667"

Debug on MQTT2

7/6/2020, 11:00:56 PMnode: 76843908.cf6198
psa001/d1/user_input : msg.payload : Object
object
Name : "Pieter"
Surname: "Hattingh"
PIN: 5054
OTP: 1234
Door ID: 1

Debug on Join Node ,

{"topic":"accreq","payload":{"psa001/d1/user_input ":{"Name ":"Pieter","Surname":"Hattingh","PIN":5054,"OTP":1234,"Door ID":1},"accreq":"74657ce6651d26d4650b2da3c16127de6017ed409492fcf62a0a48eee27c2667"},"qos":0,"retain":false,"_msgid":"45afbfd6.2a343"}

Debug on Funtion node ,

7/6/2020, 11:01:34 PMnode: Output from PSA DB ACCREQ Funtion
INSERT INTO ACCREQ ( DATE, TIME, MSGID_ID, DOOR_ID, DOOR_OTP, HASHKEY ) values ( '2020-07-06', '21:01:33', '45afbfd6.2a343', 1, 1234 ,74657ce6651d26d4650b2da3c16127de6017ed409492fcf62a0a48eee27c2667 ); : msg : Object

.....................................................................


{"topic":"INSERT INTO ACCREQ (\n  DATE, TIME, MSGID_ID, DOOR_ID, DOOR_OTP, HASHKEY\n) values (\n   '2020-07-06', '21:01:33', '45afbfd6.2a343', 1, 1234 ,74657ce6651d26d4650b2da3c16127de6017ed409492fcf62a0a48eee27c2667\n);","payload":{"psa001/d1/user_input ":{"Name ":"Pieter","Surname":"Hattingh","PIN":5054,"OTP":1234,"Door ID":1},"accreq":"74657ce6651d26d4650b2da3c16127de6017ed409492fcf62a0a48eee27c2667"},"qos":0,"retain":false,"_msgid":"45afbfd6.2a343"}

debug on SQL Node ,

7/6/2020, 11:01:35 PMnode: PSA-DB
msg : error
"Error: SQLITE_ERROR: unrecognized token: "74657ce6651d26d4650b2da3c16127de6017ed409492fcf62a0a48eee27c2667""

that's the lot .

i have noticed the """ behind the Hashkey value error from the SQL node , do you think this is the issues ?

31

could this be the only issue its moaning about ?

32

Steve , Could this be due to an SQLite integer quantity limitation ?

33

There is no quotes around the hash value in the SQL

In the function where the SQL is generated, put a single quote before and after ${hashkey}); like this '${hashkey}' then try again

Also, delete the \n just after
'${hashkey}'\n); --> '${hashkey}');

1 Like
34

let me test this out

35

LIke This Steve ? ,

'${hashkey}');`
36

Congratulations Steve, we ( You ) have done it !! sussesfull entry to the DB

[{"DATE":"2020-07-06","TIME":"21:43:51","MSGID_ID":"a56c1d43.3c03","DOOR_ID":1,"DOOR_OTP":1234,"HASHKEY":"74657ce6651d26d4650b2da3c16127de6017ed409692fcf62a0a48eee27c2667"}]
1 Like
37

I wait your next challenge (not :wink: )

Glad it's working.

Hopefully you have learned a lot?

1 Like
38

I sure did , thanks alot my friend .. amazing that '' could make that difference, i will need to continue my JS Script cources .

NOW

issue number 2 hahaha

thats is if you can be so kind ..

39

Another time I'm afraid.

When you post your next thread (new thread), can I suggest the following..

Try to create a demo flow with real dummy data so users can simulate what you are trying to achieve!

Also, don't post dashboard nodes in your export as they're a pain to remove & not required (unless it's a dashboard question)

And please select nodes to export not the full flow (as it keeps adding new tabs to my node-red when I import)
I.e. select only the nodes of interest, press ctrl+e & ensure the "selected nodes" button is lit up before copying.

Also, be super clear on your subject title. Like "generate SQL statement from 2 mqtt messages" would have been a far better title & would attract far more support than just me.

Phew. That should do it. :+1:

1 Like