SQlite, NodeRed, Http and Telegram Bot

i'm a nodered beginner and i don't speak english very well but i will try to make my self understood. The purpose of my flow should be to enter Id, Name and Surname of a worker on a telegram bot and record it in db in case the id does not already exist in the db itself. To understand if it exists I used a select and if this select is nothing I proceed with an insert. The problem is that from the table I see that through the insert I enter only null values and also despite entering the same id several times does not tell me that it is already present. Can anyone help me? below I entered the flow.

[{"id":"b9714c6481595ac3","type":"tab","label":"Flow 2","disabled":false,"info":"","env":[]},{"id":"2908f8ba29fe8611","type":"chatbot-message","z":"b9714c6481595ac3","name":"","message":[{"message":"Sign in"}],"language":"none","x":510,"y":380,"wires":[["f36cc6a2c69534a0"]]},{"id":"f36cc6a2c69534a0","type":"chatbot-message","z":"b9714c6481595ac3","name":"","message":[{"message":"Insert your name"}],"language":"none","x":650,"y":380,"wires":[["8ecc9d2cb6781465"]]},{"id":"8ecc9d2cb6781465","type":"chatbot-telegram-send","z":"b9714c6481595ac3","bot":"9672c375.5e41","botProduction":"","track":true,"passThrough":false,"errorOutput":false,"outputs":1,"x":850,"y":380,"wires":[["fa766618e224c4fa"]]},{"id":"fa766618e224c4fa","type":"chatbot-parse","z":"b9714c6481595ac3","name":"","parseType":"string","parseVariable":"nome","x":1100,"y":380,"wires":[["5647bc26e07a3ee8"],[]]},{"id":"5647bc26e07a3ee8","type":"chatbot-message","z":"b9714c6481595ac3","name":"","message":[{"message":"Insert your surname"}],"language":"none","x":650,"y":460,"wires":[["d76185d79b8bef02"]]},{"id":"d76185d79b8bef02","type":"chatbot-telegram-send","z":"b9714c6481595ac3","bot":"9672c375.5e41","botProduction":"","track":true,"passThrough":false,"errorOutput":false,"outputs":1,"x":850,"y":460,"wires":[["c6b46ed6da4e35df"]]},{"id":"c6b46ed6da4e35df","type":"chatbot-parse","z":"b9714c6481595ac3","name":"","parseType":"string","parseVariable":"cognome","x":1100,"y":460,"wires":[["92a87b558847f5de"],[]]},{"id":"92a87b558847f5de","type":"chatbot-message","z":"b9714c6481595ac3","name":"","message":[{"message":"Insert ID"}],"language":"none","x":650,"y":540,"wires":[["fbc28402837d7eac"]]},{"id":"fbc28402837d7eac","type":"chatbot-telegram-send","z":"b9714c6481595ac3","bot":"9672c375.5e41","botProduction":"","track":true,"passThrough":false,"errorOutput":false,"outputs":1,"x":850,"y":540,"wires":[["a4396041418b2032"]]},{"id":"a4396041418b2032","type":"chatbot-parse","z":"b9714c6481595ac3","name":"","parseType":"string","parseVariable":"ID","x":1100,"y":540,"wires":[["c38592c881cbcb4d"],[]]},{"id":"c38592c881cbcb4d","type":"function","z":"b9714c6481595ac3","name":"","func":"var chat = msg.chat();\nvar nome = chat.get('nome');\nvar cognome = chat.get('cognome');\nvar ID = chat.get('ID');\nmsg.topic = \"INSERT INTO operatore VALUES (?,?,?)\";\nmsg.payload = {\"IDoperatore\":ID,\"Nome\":nome, \"Cognome\":cognome};\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1280,"y":540,"wires":[["6ead44f661d8116a","ef35fbb6bd8e0e1d","2829f2f12633470f","d2f73b749f74a46d"]]},{"id":"6ead44f661d8116a","type":"debug","z":"b9714c6481595ac3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1530,"y":520,"wires":[]},{"id":"d50f04cec29391bb","type":"ui_table","z":"b9714c6481595ac3","group":"","name":"Tabella Operatori","order":1,"width":"6","height":"5","columns":[{"field":"IDoperatore","title":"IDoperatore","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"Cognome","title":"Cognome","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"Nome","title":"Nome","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":1,"cts":true,"x":1410,"y":260,"wires":[[]]},{"id":"34a43c2c31e9e28c","type":"ui_button","z":"b9714c6481595ac3","name":"","group":"","order":0,"width":0,"height":0,"passthru":false,"label":"","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"str","x":750,"y":260,"wires":[["4e60edae1c8cf808"]]},{"id":"4e60edae1c8cf808","type":"change","z":"b9714c6481595ac3","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"SELECT * FROM operatore;","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":960,"y":260,"wires":[["e0bbedda447d0dab"]]},{"id":"df027ebe6df7a7f1","type":"http in","z":"b9714c6481595ac3","name":"","url":"/reg/wo","method":"get","upload":false,"swaggerDoc":"","x":270,"y":800,"wires":[["8bcd5d3115cb8410","847e38b99d653106"]]},{"id":"f907b452ff9998e5","type":"http response","z":"b9714c6481595ac3","name":"","statusCode":"","headers":{},"x":1910,"y":920,"wires":[]},{"id":"67695cb26baeabe9","type":"change","z":"b9714c6481595ac3","name":"INSERT","rules":[{"t":"set","p":"params","pt":"msg","to":"[payload.nome, payload.cognome, payload.ID\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1160,"y":820,"wires":[["faf1db7123e0b317","0050dd78e4ebe77c"]]},{"id":"7ca077be463b610e","type":"switch","z":"b9714c6481595ac3","name":"","property":"payload[0]","propertyType":"msg","rules":[{"t":"null"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1530,"y":760,"wires":[["35efb9facbee9d72"],["c878381a9a79f3c1"]]},{"id":"35efb9facbee9d72","type":"change","z":"b9714c6481595ac3","name":"","rules":[{"t":"set","p":"statusCode","pt":"msg","to":"200","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"Operatore creato","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1700,"y":700,"wires":[["f907b452ff9998e5"]]},{"id":"8bcd5d3115cb8410","type":"change","z":"b9714c6481595ac3","name":"SELECT","rules":[{"t":"set","p":"datiOperatore","pt":"msg","to":"{\t\"IDoperatore\":payload.ID,\t\"Nome\":payload.nome,\t\"Cognome\":payload.cognome\t}","tot":"jsonata"},{"t":"set","p":"topic","pt":"msg","to":"SELECT * FROM operatore WHERE IDoperatore = ?","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"[\t$string(datiOperatore.IDoperatore)\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":860,"wires":[["cdc8c1e47449b158","e8f5a204312ea8e0","0d47e16a6b7e1db8"]]},{"id":"c542d16d2a232a72","type":"switch","z":"b9714c6481595ac3","name":"","property":"payload[0]","propertyType":"msg","rules":[{"t":"null"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":950,"y":860,"wires":[["67695cb26baeabe9","e7d81d5f3ef34886"],["bc6fe9053f3a87ab","fc05092495de84fb"]]},{"id":"bc6fe9053f3a87ab","type":"change","z":"b9714c6481595ac3","name":"","rules":[{"t":"set","p":"statusCode","pt":"msg","to":"409","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"Operatore esistente","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1180,"y":920,"wires":[["f907b452ff9998e5"]]},{"id":"fc05092495de84fb","type":"debug","z":"b9714c6481595ac3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1150,"y":980,"wires":[]},{"id":"d2f73b749f74a46d","type":"http request","z":"b9714c6481595ac3","name":"","method":"GET","ret":"txt","paytoqs":"query","url":"https://\"knbjbk\".azurewebsites.net/reg/wo","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":1530,"y":480,"wires":[["16b15b28548d3d65"]]},{"id":"16b15b28548d3d65","type":"chatbot-telegram-send","z":"b9714c6481595ac3","bot":"9672c375.5e41","botProduction":"","track":false,"passThrough":false,"errorOutput":false,"outputs":0,"x":1810,"y":520,"wires":[]},{"id":"ef35fbb6bd8e0e1d","type":"chatbot-waiting","z":"b9714c6481595ac3","waitingType":"typing","x":1520,"y":560,"wires":[["16b15b28548d3d65"]]},{"id":"b39e4686fe172778","type":"sqlite","z":"b9714c6481595ac3","mydb":"","sqlquery":"msg.topic","sql":"","name":"DB worker","x":1010,"y":160,"wires":[[]]},{"id":"27bbb55d8240f1fd","type":"inject","z":"b9714c6481595ac3","name":"CREATE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE operatore (Nome Varchar, Cognome Varchar, IDoperatore Varchar)","payload":"","payloadType":"date","x":840,"y":140,"wires":[["b39e4686fe172778"]]},{"id":"2382febe5a9f1778","type":"inject","z":"b9714c6481595ac3","name":"DROP","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DROP TABLE operatore","payload":"","payloadType":"date","x":850,"y":180,"wires":[["b39e4686fe172778"]]},{"id":"2829f2f12633470f","type":"chatbot-message","z":"b9714c6481595ac3","name":"","message":[{"message":"Welcome Worker: {{nome}} {{cognome}} \n\n"}],"language":"none","x":1510,"y":600,"wires":[["16b15b28548d3d65"]]},{"id":"e0bbedda447d0dab","type":"sqlite","z":"b9714c6481595ac3","mydb":"","sqlquery":"msg.topic","sql":"","name":"DB worker","x":1170,"y":260,"wires":[["d50f04cec29391bb"]]},{"id":"cdc8c1e47449b158","type":"sqlite","z":"b9714c6481595ac3","mydb":"","sqlquery":"msg.topic","sql":"SELECT * FROM operatore WHERE IDoperatore = ?","name":"DB worker","x":750,"y":860,"wires":[["c542d16d2a232a72","e33d39e6363b396c","d1478f0c8acec50b"]]},{"id":"faf1db7123e0b317","type":"sqlite","z":"b9714c6481595ac3","mydb":"","sqlquery":"prepared","sql":"INSERT INTO operatore (IDoperatore, Nome, Cognome) VALUES (?,?,?);","name":"DB worker","x":1350,"y":760,"wires":[["7ca077be463b610e"]]},{"id":"1bb4a5f994cc6438","type":"chatbot-rules","z":"b9714c6481595ac3","name":"/reg","rules":[{"type":"commandStartsWith","command":"/reg"}],"outputs":1,"x":330,"y":380,"wires":[["2908f8ba29fe8611"]]},{"id":"e8f5a204312ea8e0","type":"debug","z":"b9714c6481595ac3","name":"debug 52","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":720,"y":740,"wires":[]},{"id":"0050dd78e4ebe77c","type":"debug","z":"b9714c6481595ac3","name":"debug 53","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1340,"y":680,"wires":[]},{"id":"e33d39e6363b396c","type":"debug","z":"b9714c6481595ac3","name":"debug 54","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":920,"y":740,"wires":[]},{"id":"e7d81d5f3ef34886","type":"debug","z":"b9714c6481595ac3","name":"debug 56","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1160,"y":780,"wires":[]},{"id":"847e38b99d653106","type":"debug","z":"b9714c6481595ac3","name":"debug 57","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":540,"y":740,"wires":[]},{"id":"d1478f0c8acec50b","type":"debug","z":"b9714c6481595ac3","name":"debug 58","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":940,"y":780,"wires":[]},{"id":"0d47e16a6b7e1db8","type":"debug","z":"b9714c6481595ac3","name":"debug 59","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":720,"y":780,"wires":[]},{"id":"c878381a9a79f3c1","type":"change","z":"b9714c6481595ac3","name":"","rules":[{"t":"set","p":"statusCode","pt":"msg","to":"500","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":1700,"y":800,"wires":[["f907b452ff9998e5"]]},{"id":"b61e446fe07af892","type":"chatbot-telegram-receive","z":"b9714c6481595ac3","bot":"","botProduction":"","x":90,"y":580,"wires":[["1bb4a5f994cc6438"]]},{"id":"9672c375.5e41","type":"chatbot-telegram-node","botname":"","usernames":"","polling":"1000","store":"","log":"","debug":false,"skipMediaFiles":false,"webHook":"","connectMode":"polling","storeMessages":false,"enableMissionControl":false,"inspectMessages":false,"chatbotId":""}]

There are a bunch of chatbot node, what is the full name of the one you using? node-red-contrib-?????

1 Like

I'm using node-red-contrib-chatbot

[EDIT] It looks like I am wrong about this. I haven't used the chatbot node so ignore this for now.

Let's work thru this a bit by bit. First off in the 'function` node you have this:

var chat = msg.chat();
var nome = chat.get('nome');
var cognome = chat.get('cognome');
var ID = chat.get('ID');
msg.topic = "INSERT INTO operatore VALUES (?,?,?)";
msg.payload = {"IDoperatore":ID,"Nome":nome, "Cognome":cognome};
return msg;


The first four lines are wrong since they are trying to call a function when you want to access an object (you might want to take a JavaScript tutorial)Those lines should be

var chat = msg.chat;
var nome = chat.nome;
var cognome = chat.cognome;
var ID = chat.ID;
msg.topic = "INSERT INTO operatore VALUES (?,?,?)";
msg.payload = {"IDoperatore":ID,"Nome":nome, "Cognome":cognome};
return msg;

make those changes and see what happens.

1 Like

Okay, in fact I changed my function as you wrote me and now I can't read the data that I enter via telegram and the debug displays empty strings. I think I made mistakes on the part about the select, the switch or the insert.

Why are you using the HTTP nodes? why not just use link nodes?

[{"id":"8e6836bb4127eae6","type":"tab","label":"Flow 2","disabled":false,"info":"","env":[],"_mcu":{"mcu":false}},{"id":"de1d3ec170398cfc","type":"chatbot-message","z":"8e6836bb4127eae6","name":"","message":[{"message":"Sign in"}],"language":"none","x":510,"y":380,"wires":[["933e98a9e05861c1"]]},{"id":"933e98a9e05861c1","type":"chatbot-message","z":"8e6836bb4127eae6","name":"","message":[{"message":"Insert your name"}],"language":"none","x":650,"y":380,"wires":[["b5b09ff14bc6638a"]]},{"id":"b5b09ff14bc6638a","type":"chatbot-telegram-send","z":"8e6836bb4127eae6","bot":"4e01d2fe62a2d8d9","botProduction":"","track":true,"passThrough":false,"errorOutput":false,"outputs":1,"x":850,"y":380,"wires":[["0bbb0b11583badf7"]]},{"id":"0bbb0b11583badf7","type":"chatbot-parse","z":"8e6836bb4127eae6","name":"","parseType":"string","parseVariable":"nome","x":1100,"y":380,"wires":[["dbee090b1fea76fc"],[]]},{"id":"dbee090b1fea76fc","type":"chatbot-message","z":"8e6836bb4127eae6","name":"","message":[{"message":"Insert your surname"}],"language":"none","x":650,"y":460,"wires":[["06a433094b72021f"]]},{"id":"06a433094b72021f","type":"chatbot-telegram-send","z":"8e6836bb4127eae6","bot":"4e01d2fe62a2d8d9","botProduction":"","track":true,"passThrough":false,"errorOutput":false,"outputs":1,"x":850,"y":460,"wires":[["2cbda96ac2269e38"]]},{"id":"2cbda96ac2269e38","type":"chatbot-parse","z":"8e6836bb4127eae6","name":"","parseType":"string","parseVariable":"cognome","x":1100,"y":460,"wires":[["9df235380b6eb5c9"],[]]},{"id":"9df235380b6eb5c9","type":"chatbot-message","z":"8e6836bb4127eae6","name":"","message":[{"message":"Insert ID"}],"language":"none","x":650,"y":540,"wires":[["ae20d8f5f7b081c2"]]},{"id":"ae20d8f5f7b081c2","type":"chatbot-telegram-send","z":"8e6836bb4127eae6","bot":"4e01d2fe62a2d8d9","botProduction":"","track":true,"passThrough":false,"errorOutput":false,"outputs":1,"x":850,"y":540,"wires":[["7bb9fad54bbbb171"]]},{"id":"7bb9fad54bbbb171","type":"chatbot-parse","z":"8e6836bb4127eae6","name":"","parseType":"string","parseVariable":"ID","x":1100,"y":540,"wires":[["db6c040ac069ec69"],[]]},{"id":"db6c040ac069ec69","type":"function","z":"8e6836bb4127eae6","name":"","func":"var chat = msg.chat();\nvar nome = chat.get('nome');\nvar cognome = chat.get('cognome');\nvar ID = chat.get('ID');\nmsg.topic = \"INSERT INTO operatore VALUES (?,?,?)\";\nmsg.payload = {\"IDoperatore\":ID,\"Nome\":nome, \"Cognome\":cognome};\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1280,"y":540,"wires":[["9c78c769c0bbdec9","fca7c0385ca11323","809bf36544560b98","56b0d453935e09db","090659f0a7a617cc"]]},{"id":"9c78c769c0bbdec9","type":"debug","z":"8e6836bb4127eae6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1530,"y":520,"wires":[]},{"id":"6aadfe6bb6b8273a","type":"ui_table","z":"8e6836bb4127eae6","group":"","name":"Tabella Operatori","order":1,"width":"6","height":"5","columns":[{"field":"IDoperatore","title":"IDoperatore","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"Cognome","title":"Cognome","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"Nome","title":"Nome","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":1,"cts":true,"x":1410,"y":260,"wires":[[]]},{"id":"eb4665279a4a1b6a","type":"ui_button","z":"8e6836bb4127eae6","name":"","group":"","order":0,"width":0,"height":0,"passthru":false,"label":"","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"str","x":750,"y":260,"wires":[["a8d2170c5ddf45c3"]]},{"id":"a8d2170c5ddf45c3","type":"change","z":"8e6836bb4127eae6","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"SELECT * FROM operatore;","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":960,"y":260,"wires":[["5257f29fb1d88753"]]},{"id":"9a06c0ebf43bfc79","type":"http in","z":"8e6836bb4127eae6","d":true,"name":"","url":"/reg/wo","method":"get","upload":false,"swaggerDoc":"","x":270,"y":800,"wires":[["d82a188d6f5eeee2","293468edc9c021e1"]]},{"id":"9106bdc8cd5ad2bc","type":"http response","z":"8e6836bb4127eae6","d":true,"name":"","statusCode":"","headers":{},"x":1930,"y":880,"wires":[]},{"id":"7d6b2ad1489ee6d8","type":"change","z":"8e6836bb4127eae6","name":"INSERT","rules":[{"t":"set","p":"params","pt":"msg","to":"[payload.nome, payload.cognome, payload.ID\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1160,"y":820,"wires":[["07d94b864c8f9815","7e4d6625c561edc0"]]},{"id":"866dd40d1a865c1b","type":"switch","z":"8e6836bb4127eae6","name":"","property":"payload[0]","propertyType":"msg","rules":[{"t":"null"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1530,"y":760,"wires":[["aa92bd8c32a7cd44"],["2d85db53c6214b35"]]},{"id":"aa92bd8c32a7cd44","type":"change","z":"8e6836bb4127eae6","name":"","rules":[{"t":"set","p":"statusCode","pt":"msg","to":"200","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"Operatore creato","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1700,"y":700,"wires":[["9106bdc8cd5ad2bc","19d522ce51d91372"]]},{"id":"d82a188d6f5eeee2","type":"change","z":"8e6836bb4127eae6","name":"SELECT","rules":[{"t":"set","p":"datiOperatore","pt":"msg","to":"{\t\"IDoperatore\":payload.ID,\t\"Nome\":payload.nome,\t\"Cognome\":payload.cognome\t}","tot":"jsonata"},{"t":"set","p":"topic","pt":"msg","to":"SELECT * FROM operatore WHERE IDoperatore = ?","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"[\t$string(datiOperatore.IDoperatore)\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":860,"wires":[["25537e28bc233cf8","a4567e1f0bc0e99f","1058d81c18065cb3"]]},{"id":"01e51e16c5839bd4","type":"switch","z":"8e6836bb4127eae6","name":"","property":"payload[0]","propertyType":"msg","rules":[{"t":"null"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":950,"y":860,"wires":[["7d6b2ad1489ee6d8","5003051e49dae643"],["4e84612a344a7a15","831510e842398b67"]]},{"id":"4e84612a344a7a15","type":"change","z":"8e6836bb4127eae6","name":"","rules":[{"t":"set","p":"statusCode","pt":"msg","to":"409","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"Operatore esistente","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1180,"y":920,"wires":[["9106bdc8cd5ad2bc","19d522ce51d91372"]]},{"id":"831510e842398b67","type":"debug","z":"8e6836bb4127eae6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1150,"y":980,"wires":[]},{"id":"56b0d453935e09db","type":"http request","z":"8e6836bb4127eae6","d":true,"name":"","method":"GET","ret":"txt","paytoqs":"query","url":"https://\"knbjbk\".azurewebsites.net/reg/wo","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":1530,"y":480,"wires":[["d3b92f2606e77711"]]},{"id":"d3b92f2606e77711","type":"chatbot-telegram-send","z":"8e6836bb4127eae6","bot":"4e01d2fe62a2d8d9","botProduction":"","track":false,"passThrough":false,"errorOutput":false,"outputs":0,"x":1810,"y":520,"wires":[]},{"id":"fca7c0385ca11323","type":"chatbot-waiting","z":"8e6836bb4127eae6","waitingType":"typing","x":1520,"y":560,"wires":[["d3b92f2606e77711"]]},{"id":"ddca2f2ff964eb91","type":"sqlite","z":"8e6836bb4127eae6","mydb":"","sqlquery":"msg.topic","sql":"","name":"DB worker","x":1010,"y":160,"wires":[[]]},{"id":"ea6c8c8a1e774fc6","type":"inject","z":"8e6836bb4127eae6","name":"CREATE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE operatore (Nome Varchar, Cognome Varchar, IDoperatore Varchar)","payload":"","payloadType":"date","x":840,"y":140,"wires":[["ddca2f2ff964eb91"]]},{"id":"a43a94553a6dbd10","type":"inject","z":"8e6836bb4127eae6","name":"DROP","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DROP TABLE operatore","payload":"","payloadType":"date","x":850,"y":180,"wires":[["ddca2f2ff964eb91"]]},{"id":"809bf36544560b98","type":"chatbot-message","z":"8e6836bb4127eae6","name":"","message":[{"message":"Welcome Worker: {{nome}} {{cognome}} \n\n"}],"language":"none","x":1510,"y":600,"wires":[["d3b92f2606e77711"]]},{"id":"5257f29fb1d88753","type":"sqlite","z":"8e6836bb4127eae6","mydb":"","sqlquery":"msg.topic","sql":"","name":"DB worker","x":1170,"y":260,"wires":[["6aadfe6bb6b8273a"]]},{"id":"25537e28bc233cf8","type":"sqlite","z":"8e6836bb4127eae6","mydb":"","sqlquery":"msg.topic","sql":"SELECT * FROM operatore WHERE IDoperatore = ?","name":"DB worker","x":750,"y":860,"wires":[["01e51e16c5839bd4","b0df1a1f160791ad","cfb9a63cebcdf556"]]},{"id":"07d94b864c8f9815","type":"sqlite","z":"8e6836bb4127eae6","mydb":"","sqlquery":"prepared","sql":"INSERT INTO operatore (IDoperatore, Nome, Cognome) VALUES (?,?,?);","name":"DB worker","x":1350,"y":760,"wires":[["866dd40d1a865c1b"]]},{"id":"b22bbe71543b09f1","type":"chatbot-rules","z":"8e6836bb4127eae6","name":"/reg","rules":[{"type":"commandStartsWith","command":"/reg"}],"outputs":1,"x":330,"y":380,"wires":[["de1d3ec170398cfc"]]},{"id":"a4567e1f0bc0e99f","type":"debug","z":"8e6836bb4127eae6","name":"debug 52","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":720,"y":740,"wires":[]},{"id":"7e4d6625c561edc0","type":"debug","z":"8e6836bb4127eae6","name":"debug 53","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1340,"y":680,"wires":[]},{"id":"b0df1a1f160791ad","type":"debug","z":"8e6836bb4127eae6","name":"debug 54","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":920,"y":740,"wires":[]},{"id":"5003051e49dae643","type":"debug","z":"8e6836bb4127eae6","name":"debug 56","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1160,"y":780,"wires":[]},{"id":"293468edc9c021e1","type":"debug","z":"8e6836bb4127eae6","name":"debug 57","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":540,"y":740,"wires":[]},{"id":"cfb9a63cebcdf556","type":"debug","z":"8e6836bb4127eae6","name":"debug 58","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":940,"y":780,"wires":[]},{"id":"1058d81c18065cb3","type":"debug","z":"8e6836bb4127eae6","name":"debug 59","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":720,"y":780,"wires":[]},{"id":"2d85db53c6214b35","type":"change","z":"8e6836bb4127eae6","name":"","rules":[{"t":"set","p":"statusCode","pt":"msg","to":"500","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":1700,"y":800,"wires":[["9106bdc8cd5ad2bc","19d522ce51d91372"]]},{"id":"cfaa7b74efff01f1","type":"chatbot-telegram-receive","z":"8e6836bb4127eae6","bot":"","botProduction":"","x":90,"y":580,"wires":[["b22bbe71543b09f1"]]},{"id":"56d8265d6d90249e","type":"link in","z":"8e6836bb4127eae6","name":"Process the ID","links":[],"_mcu":{"mcu":false},"x":300,"y":740,"wires":[["293468edc9c021e1","d82a188d6f5eeee2"]],"l":true},{"id":"090659f0a7a617cc","type":"link call","z":"8e6836bb4127eae6","name":"","links":["56d8265d6d90249e"],"linkType":"static","timeout":"30","_mcu":{"mcu":false},"x":1560,"y":420,"wires":[["d3b92f2606e77711"]]},{"id":"19d522ce51d91372","type":"link out","z":"8e6836bb4127eae6","name":"link out 1","mode":"return","links":[],"_mcu":{"mcu":false},"x":1940,"y":920,"wires":[],"l":true},{"id":"4e01d2fe62a2d8d9","type":"chatbot-telegram-node","botname":"","usernames":"","polling":"1000","store":"","log":"","debug":false,"skipMediaFiles":false,"webHook":"","connectMode":"polling","storeMessages":false,"enableMissionControl":false,"inspectMessages":false,"chatbotId":""}]```
1 Like

because I have to create an educational project in which http nodes must necessarily be present

Oh that makes it ugly (grin) it would be so much easier if you could use MQTT to pass things.

That said you need to understand how the HTTP endpoints work. I suggest you look at the HTTP Endpoint and HTTP Request sections of the cookbook

I'd also suggest taking a look at node-red-contrib-telegrambot which only has six nodes and should take up less resources.

And remember you are not passing the entire msg so if you want to send the sql query and values they will have to be sent in the same variable.

Can you give me more details about the project?

  1. Is it a school assignment?
  2. what are the actual requirements?

Depending on your needs, I may be able to supply you with a flow I setup that monitors environmental sensors (temperature, Humidity), saves the data in a DB, sends alerts.

You might be able to use it as a design for your project.

The project must implement at least one sensor and an actuator on raspberry, and must consist of a Software Infrastructure built with node red on Microsoft Azure (local / remote MQTT and Http and related synchronization logic), at least a db that manages the persistent storage of data and a telegram bot that allows you to manage everything.
I have chosen to do this:
The project concerns the management of a small greenhouse. From the raspberry via sensor dht11 and db1820 sending temperature and humidity and exceeding a certain temperature activates a dual color led together with a fan. The humidity and temperature data are communicated with MQTT and are put on special graphs (I already did this part). I also wanted to insert within the project the adjustment of the RGB led via ir receiver and sunfounder remote control by assigning to specific buttons on the remote control the colors "Blue", "Red", "Purple" and "White" (I have not done it yet).
Finally, the project consists of a database called "Greenhouse" which contains a table concerning the plants contained in the greenhouse, on which activities must be carried out and contains a table concerning the operators who must carry out the activity. Through the chatbot (which allows me to enter name, surname and id of the operator) I register the operator within the appropriate table (I enter it only if via http I see that the operator is not present in db). The chatbot also communicates the temperature and humidity through MQTT so that the operator keeps these parameters under control before performing a specific activity on a specific selected plant. At the time of registration, the name, surname and id of the operator are sent via chatbot and an http request must then be used to check if the entered id is present in db through the use of a select. The select takes in input the variables entered in the chatbot with an http get and if it is nothing should take the same variables and record them in db. Once this is done it also sends a 200 status message and says that the operator has been entered. If the select is not null we proceed with sending a message saying that the operator is existing.

Woooooooooooooo - that's a lot of things to do.

How long have you been given to create this project?

Has this project ever been attempted by other year-groups (ahead of you)?

The project can be broken-down into a number of (functional) tasks that may make it more manageable.
Once you have done that, you may find some solutions of the Forum.
As most poeple on the Forum are volunteers and give their time for free, I doubt if you will find anyone with enough free-time to "do" the project for you.

But I don't want this, my problem concerns only the part of the operator registration, in particular the select and insert present in the code attached above are definitely problematic.

Oh in that case I might be able to help as I made a 'Buzz-In bot' (that works out who was first to press a Telegram keybaord button on a mobile phone). I'll see if I can modify the flow to suit your application.
Probably won't be able to look at it in detail until Monday - but I will have a go for you.

Just had a few minutes to spare and thought I'd have a 'first-stab' at creating the Operator Registration.
You will need to enter your Bot details and Token into the Telegram nodes.

Note: I'm using the 'node-red-contrib-telegrambot' node as I find it easier to use.

I've created a Javascript object called 'operators' to hold the key pieces of information.
The main item is 'stage' as this is used to send the appropriate keyboard layout to the user and process the result from Telegram.
e.g. stage==1 is inputting the operator's id
stage==2 is inputting the operator's first_name
stage==3 is inputting the operator's last_name

The Javascript object is dynamic, so it can deal with more than one operator on a mobile phone.

Once all the form-inputs have been captured, they are output on the lower pin of the 'process input' function node. You'll need to connect this to your next stage - writing to the database.

Hope it works for you.

let operators = {
    1:{stage:0, id:0, first_name:'unknown', last_name:'unknown'}
}

Once an operator has entered their details, the Javascipt object might look like this...

let operators = {
    1:{stage:0, id:0, first_name:'unknown', last_name:'unknown'},
    1234567:{stage:0, id:4567, first_name:'Fred', last_name:'Bloggs'}
}

[{"id":"4c1c22d1750b13fa","type":"tab","label":"Telegram form ","disabled":false,"info":"","env":[]},{"id":"4f96e235566061f3","type":"inject","z":"4c1c22d1750b13fa","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":true,"onceDelay":"0.5","topic":"","payload":"1","payloadType":"num","x":130,"y":80,"wires":[["1ab91987066a8b16"]]},{"id":"1ab91987066a8b16","type":"function","z":"4c1c22d1750b13fa","name":"Operators array","func":"// Array for operators\n// New operators will be dynamically added to the Javascript object \n\nlet operators = {\n    1:{stage:0, id:0, first_name:'unknown', last_name:'unknown'}\n}\n\nreturn null;\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":80,"wires":[[]]},{"id":"737d730ac6c5a7ce","type":"debug","z":"4c1c22d1750b13fa","name":"debug 170","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":290,"y":140,"wires":[]},{"id":"470690d8c93b893d","type":"catch","z":"4c1c22d1750b13fa","name":"","scope":null,"uncaught":false,"x":120,"y":140,"wires":[["737d730ac6c5a7ce"]]},{"id":"a0f9ba1ef57fed59","type":"change","z":"4c1c22d1750b13fa","name":"Save chatID","rules":[{"t":"set","p":"chatID","pt":"flow","to":"payload.chatId","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":350,"y":260,"wires":[["546a965f0521ed9d"]]},{"id":"5f82472c7b303d52","type":"comment","z":"4c1c22d1750b13fa","name":"Telegram >>> Input to Bot","info":"","x":170,"y":220,"wires":[]},{"id":"66c6f8f3fce6eb3d","type":"comment","z":"4c1c22d1750b13fa","name":"Output from Bot >>> Telegram","info":"","x":1040,"y":240,"wires":[]},{"id":"546a965f0521ed9d","type":"switch","z":"4c1c22d1750b13fa","name":"","property":"payload.content","propertyType":"msg","rules":[{"t":"cont","v":"/Start","vt":"str"},{"t":"cont","v":"/start","vt":"str"},{"t":"else"}],"checkall":"false","repair":false,"outputs":3,"x":330,"y":320,"wires":[["ef3da6b036a025b8"],["ef3da6b036a025b8"],["c1ab75d37c520995"]]},{"id":"ef3da6b036a025b8","type":"function","z":"4c1c22d1750b13fa","name":"Welcome screen","func":"let chatID = flow.get(\"chatID\");\nlet operators = flow.get(\"operators\") ||  {1: {\"stage\": 1, \"id\": 0, \"first_name\": \"unknown\", \"last_name\": \"unknown\"}};\n\n\n// if chatId not in players object then add an entry for it\nif (operators[chatID] === undefined) {\n    operators[chatID] = {}\n}\n\noperators[chatID] = {\"stage\": 1,\n               \"id\":0,\n               \"first_name\": \"\", \n               \"last_name\": \"\"\n};\n\nflow.set(\"operators\", operators);\n\n// Assemble the content for the Welcome screen\nlet my_message = \"\\u{1F60A} \\u{1F60A} Operator Registration \\u{1F60A} \\u{1F60A}\";\n\nmy_message = my_message + \"\\nCommands are...\";\nmy_message = my_message + \"\\n/start - to start or re-start your session\";\n\n// Format the message to send to the user via Telegram \nmsg.payload = {\n        chatId: chatID, \n        type:\"message\",\n        content:my_message\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":610,"y":300,"wires":[["e6c00e8803aec245","176d69e996cff108"]]},{"id":"e6c00e8803aec245","type":"delay","z":"4c1c22d1750b13fa","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":800,"y":320,"wires":[["c8866469eb24fab8"]]},{"id":"c8866469eb24fab8","type":"function","z":"4c1c22d1750b13fa","name":"Send appropriate keyboard","func":"let chatID = flow.get(\"chatID\");\nlet operators = flow.get(\"operators\");\nlet stage = operators[chatID].stage;\n\nif (stage == 1) {\n    let message = 'Enter your ID';\n    msg.payload = {chatId : chatID, type : 'message', content : message};\n    // activate markdown\n    msg.payload.options = {disable_web_page_preview : true, parse_mode : \"Markdown\"};\n    return msg;\n}\nelse if (stage == 2) {\n    let message = 'Enter your First Name';\n    msg.payload = {chatId : chatID, type : 'message', content : message};\n    // activate markdown\n    msg.payload.options = {disable_web_page_preview : true, parse_mode : \"Markdown\"};\n    return msg;\n}\nelse if (stage == 3) {\n    let message = 'Enter your Last Name';\n    msg.payload = {chatId : chatID, type : 'message', content : message};\n    // activate markdown\n    msg.payload.options = {disable_web_page_preview : true, parse_mode : \"Markdown\"};\n    return msg;\n}\nelse if (stage == 0) {\n    let message = 'Thank you - input is complete';\n    msg.payload = {chatId : chatID, type : 'message', content : message};\n    // activate markdown\n    msg.payload.options = {disable_web_page_preview : true, parse_mode : \"Markdown\"};\n    return msg;\n}\n\nreturn null;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1040,"y":360,"wires":[["176d69e996cff108"]]},{"id":"c1ab75d37c520995","type":"function","z":"4c1c22d1750b13fa","name":"process input","func":"let chatID = flow.get(\"chatID\");\nlet operators = flow.get(\"operators\");\nlet stage = operators[chatID].stage;\n\nlet my_content = msg.payload.content;\n\nif (stage == 1) {\n    operators[chatID].id = my_content;\n    operators[chatID].stage = 2;\n    flow.set(\"operators\", operators);\n    node.send( [msg, null] );\n}\n\nelse if (stage == 2) {\n    operators[chatID].first_name = my_content;\n    operators[chatID].stage = 3;\n    flow.set(\"operators\", operators);\n    node.send( [msg, null] );\n}\n\nelse if (stage == 3) {\n    operators[chatID].last_name = my_content;\n    operators[chatID].stage = 0;\n    flow.set(\"operators\", operators);\n    \n    msg.payload = {}; // Clear the object\n    msg.payload.chatID     = chatID;\n    msg.payload.id         = operators[chatID].id\n    msg.payload.first_name = operators[chatID].first_name;\n    msg.payload.last_name  = operators[chatID].last_name;\n    node.send( [msg, msg] );\n}\nreturn null;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":600,"y":340,"wires":[["c8866469eb24fab8"],["2999f3a667878437","c9434e1ed49387f0"]]},{"id":"2999f3a667878437","type":"delay","z":"4c1c22d1750b13fa","name":"","pauseType":"delay","timeout":"6","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":580,"y":220,"wires":[["ef3da6b036a025b8"]]},{"id":"c9434e1ed49387f0","type":"debug","z":"4c1c22d1750b13fa","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":790,"y":480,"wires":[]},{"id":"930df49cfc7838f0","type":"comment","z":"4c1c22d1750b13fa","name":"Output to next stage.... dBase","info":"","x":860,"y":440,"wires":[]},{"id":"6fa62da3ac4dde87","type":"comment","z":"4c1c22d1750b13fa","name":"Operator registration wil start again after 6s delay ","info":"","x":710,"y":140,"wires":[]},{"id":"743575fba88c69ef","type":"comment","z":"4c1c22d1750b13fa","name":"Delete this node to remove that functionality","info":"","x":690,"y":180,"wires":[]},{"id":"5570cb361d4f36c3","type":"telegram receiver","z":"4c1c22d1750b13fa","name":"","bot":"","saveDataDir":"","filterCommands":false,"x":150,"y":260,"wires":[["a0f9ba1ef57fed59"],[]]},{"id":"176d69e996cff108","type":"telegram sender","z":"4c1c22d1750b13fa","name":"","bot":"","haserroroutput":false,"outputs":1,"x":1040,"y":300,"wires":[[]]}]
1 Like

You need to read the documentation on HTTP endpoints I pointed you at and understand how they work and how to send data. Then just build a test to send data, remove Telegram from that experiment till you get it to work. Once it is working, then add telegram back in.

I solved the problem and now my flow allows you to do what I wanted, thank you all for the help

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.