i got this kind of error when insert data...
USER database
HISTORY database
var spokenAsText = msg.payload;// or where ever the speech to text is
var SQL = "INSERT INTO HISTORY (NAME) SELECT NAME FROM USER WHERE VOICE= '" + spokenAsText + "'";
msg.topic = SQL;
return msg;
i want insert NAME data from USER to HISTORY where VOICE = spokenAsText , but i have error
how to slove this problem?
spokenAsText is an object, check its contents first.
Put a debug before this function node. What is in payload?
Another thing, as you already have the SELECT query working (from your other thread) you could just generate a new INSERT SQL statement.
Mic > speech to text > lookup text in DB > check it was found > generate new INSERT query > send to database
before function node
how to generete ?
because i want check it first it is empty or no...if no empty, it just can upload data
msg.payload[0].VOICE
As it is an array.
The debug shows you payload is an array (not a string)
Also, how can this debug be before the function (the topic already has the INSERT command)
Also, how are you checking verify empty?
Post your flow and I'll add some help for you.
[{"id":"43555fcb.61e95","type":"tab","label":"VAES_project","disabled":false,"info":""},{"id":"842ac2b4.3af0a","type":"mysql","z":"43555fcb.61e95","mydb":"7c738c44.e9a364","name":"DB_MYSQL","x":510,"y":300,"wires":[["f820ef9f.254e7"]]},{"id":"41d8f9da.5c2498","type":"switch","z":"43555fcb.61e95","name":"verify empty/no empty","property":"payload","propertyType":"msg","rules":[{"t":"nempty"},{"t":"empty"}],"checkall":"true","repair":false,"outputs":2,"x":160,"y":320,"wires":[["c5a1b4ed.2825c8","69b8edb2.191f54"],["bcf023c3.faa4a"]]},{"id":"c5a1b4ed.2825c8","type":"function","z":"43555fcb.61e95","name":"upload name data","func":"var spokenAsText = msg.payload;// or where ever the speech to text is\nvar SQL = \"INSERT INTO HISTORY (NAME) SELECT NAME FROM USER WHERE VOICE= '\" + spokenAsText + \"'\";\nmsg.topic = SQL; \nreturn msg;","outputs":1,"noerr":0,"x":530,"y":400,"wires":[["842ac2b4.3af0a"]]},{"id":"f820ef9f.254e7","type":"json","z":"43555fcb.61e95","name":"","property":"payload","action":"","pretty":false,"x":670,"y":300,"wires":[["92616f60.2df43"]]},{"id":"92616f60.2df43","type":"debug","z":"43555fcb.61e95","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":830,"y":300,"wires":[]},{"id":"dd9ee22f.8fbe2","type":"debug","z":"43555fcb.61e95","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":750,"y":540,"wires":[]},{"id":"69b8edb2.191f54","type":"change","z":"43555fcb.61e95","name":"set welcome sound","rules":[{"t":"set","p":"payload","pt":"msg","to":"welcome","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":460,"wires":[["dd9ee22f.8fbe2","78077300.fa3dfc"]]},{"id":"bcf023c3.faa4a","type":"change","z":"43555fcb.61e95","name":"set access denied sound","rules":[{"t":"set","p":"payload","pt":"msg","to":"access denied","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":520,"wires":[["dd9ee22f.8fbe2","78077300.fa3dfc"]]},{"id":"3126e3bc.b9985c","type":"watson-speech-to-text","z":"43555fcb.61e95","name":"","alternatives":"3","speakerlabels":true,"smartformatting":true,"lang":"en-US","langhidden":"en-US","langcustom":"NoCustomisationSetting","langcustomhidden":"","custom-weight":"0.5","band":"NarrowbandModel","bandhidden":"NarrowbandModel","keywords":"","keywords-threshold":"0.5","word-confidence":true,"password":"","apikey":"aEfplzvlM_B8MA01nsDy3fj6b3CnG6dOQEGISr2gHxxk","payload-response":true,"streaming-mode":false,"streaming-mute":false,"auto-connect":false,"discard-listening":false,"disable-precheck":false,"service-endpoint":"https://api.us-south.speech-to-text.watson.cloud.ibm.com/instances/4ec7115d-1cbf-4ac7-b141-9c9a675fbdac","x":480,"y":980,"wires":[["627d8e3.4f0947"]]},{"id":"462b985a.b52748","type":"microphone","z":"43555fcb.61e95","name":"","x":290,"y":980,"wires":[["3126e3bc.b9985c"]]},{"id":"627d8e3.4f0947","type":"debug","z":"43555fcb.61e95","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":690,"y":980,"wires":[]},{"id":"7f524d5d.018134","type":"watson-speech-to-text","z":"43555fcb.61e95","name":"","alternatives":1,"speakerlabels":true,"smartformatting":true,"lang":"en-US","langhidden":"en-US","langcustom":"NoCustomisationSetting","langcustomhidden":"","custom-weight":"0.5","band":"NarrowbandModel","bandhidden":"NarrowbandModel","keywords":"","keywords-threshold":"0.5","word-confidence":true,"password":"","apikey":"aEfplzvlM_B8MA01nsDy3fj6b3CnG6dOQEGISr2gHxxk","payload-response":true,"streaming-mode":false,"streaming-mute":false,"auto-connect":false,"discard-listening":false,"disable-precheck":false,"service-endpoint":"https://api.us-south.speech-to-text.watson.cloud.ibm.com/instances/4ec7115d-1cbf-4ac7-b141-9c9a675fbdac","x":280,"y":160,"wires":[["2d9ab90d.74b916"]]},{"id":"150b84f1.3b278b","type":"microphone","z":"43555fcb.61e95","name":"","x":110,"y":160,"wires":[["7f524d5d.018134"]]},{"id":"ee4ddd08.39f53","type":"function","z":"43555fcb.61e95","name":"compare data","func":"//msg.payload = msg.payload.trim();\nvar spokenAsText = msg.payload// or where ever the speech to text is\nvar SQL = \"SELECT * FROM USER WHERE VOICE = '\" + spokenAsText + \"' AND VERIFY = 1 LIMIT 1\";\nmsg.topic = SQL; \nreturn msg;","outputs":1,"noerr":0,"x":500,"y":160,"wires":[["608fffb.97ae3","5f16bb2c.67b5c4"]]},{"id":"608fffb.97ae3","type":"mysql","z":"43555fcb.61e95","mydb":"7c738c44.e9a364","name":"DB_MYSQL","x":690,"y":160,"wires":[["4726c75f.7ffc48","41d8f9da.5c2498"]]},{"id":"4726c75f.7ffc48","type":"debug","z":"43555fcb.61e95","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":690,"y":120,"wires":[]},{"id":"5f16bb2c.67b5c4","type":"debug","z":"43555fcb.61e95","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":490,"y":120,"wires":[]},{"id":"78077300.fa3dfc","type":"play audio","z":"43555fcb.61e95","name":"","voice":"4","x":750,"y":480,"wires":[]},{"id":"2d9ab90d.74b916","type":"function","z":"43555fcb.61e95","name":"trim()","func":"msg.payload = msg.payload.trim();\nreturn msg;","outputs":1,"noerr":0,"x":360,"y":60,"wires":[["ee4ddd08.39f53"]]},{"id":"7c738c44.e9a364","type":"MySQLdatabase","z":"","host":"165.22.51.44","port":"3306","db":"hireelec_vaes","tz":""}]
it compare speech to text with database in USER VOICE, if have same, then it will show output...if no have same, then it will show empty...then i can verify it is empty or no...
Hi, some notes for starters...
After tweaking it...
//first check payload is a string with something in it
if(!msg.payload || typeof msg.payload != "string"){
node.warn("msg.payload is empty");
return null;//halt the flow
}
//Next, trim spoken text & store it in the msg for later use
msg.spokenAsText = msg.payload.trim();
//now generate the SQL insert
msg.topic = `SELECT * FROM USER
WHERE VOICE = '${msg.spokenAsText}'
AND VERIFY = 1
LIMIT 1`;
return msg;
//if payload is empty send it out of output 2
if(!msg.payload){
node.warn("payload is null");
return [null, msg];//send msg to output 2
}
//if payload is NOT an array send it out of output 2
if(Array.isArray(msg.payload) === false){
node.warn("payload array is NOT an array");
return [null, msg];//send msg to output 2
}
//payload IS an array BUT has < 1, send it out of output 2
if(msg.payload.length < 1){
node.warn("payload array is empty");
return [null, msg];//send msg to output 2
}
//All checks have passed (data is good!) - send message to output 1
return [msg,null];
//generate the SQL insert
msg.topic = `INSERT INTO HISTORY (NAME)
VALUES( '${msg.spokenAsText}' )`;
return msg;
[{"id":"f2e42429.5e9068","type":"mysql","z":"6ef1cfb5.30ff2","mydb":"88bd22d4.f7417","name":"DB_MYSQL","x":830,"y":300,"wires":[["7a3a50e1.2e97e"]]},{"id":"8cbfcf24.4f099","type":"function","z":"6ef1cfb5.30ff2","name":"Generate History INSERT SQL","func":"//generate the SQL insert\nmsg.topic = `INSERT INTO HISTORY (NAME) \n VALUES( '${msg.spokenAsText}' )`;\n\nreturn msg;","outputs":1,"noerr":0,"x":470,"y":300,"wires":[["f2e42429.5e9068","290fc1a6.566eee"]]},{"id":"7a3a50e1.2e97e","type":"debug","z":"6ef1cfb5.30ff2","name":"History Insert Result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":860,"y":260,"wires":[]},{"id":"a283f271.5597e","type":"change","z":"6ef1cfb5.30ff2","name":"set welcome sound","rules":[{"t":"set","p":"payload","pt":"msg","to":"welcome","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":430,"y":400,"wires":[["4551a5d3.81b7dc","ae7afc65.b3256"]]},{"id":"ecb3ed5d.ff044","type":"change","z":"6ef1cfb5.30ff2","name":"set access denied sound","rules":[{"t":"set","p":"payload","pt":"msg","to":"access denied","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":450,"y":440,"wires":[["4551a5d3.81b7dc","ae7afc65.b3256"]]},{"id":"796aa5e6.3dd5dc","type":"watson-speech-to-text","z":"6ef1cfb5.30ff2","name":"","alternatives":1,"speakerlabels":true,"smartformatting":true,"lang":"en-US","langhidden":"en-US","langcustom":"NoCustomisationSetting","langcustomhidden":"","custom-weight":"0.5","band":"NarrowbandModel","bandhidden":"NarrowbandModel","keywords":"","keywords-threshold":"0.5","word-confidence":true,"password":"","apikey":"aEfplzvlM_B8MA01nsDy3fj6b3CnG6dOQEGISr2gHxxk","payload-response":true,"streaming-mode":false,"streaming-mute":false,"auto-connect":false,"discard-listening":false,"disable-precheck":false,"service-endpoint":"https://api.us-south.speech-to-text.watson.cloud.ibm.com/instances/4ec7115d-1cbf-4ac7-b141-9c9a675fbdac","x":340,"y":160,"wires":[["91d99153.bee16"]]},{"id":"411b75e0.e0161c","type":"microphone","z":"6ef1cfb5.30ff2","name":"","x":150,"y":160,"wires":[["796aa5e6.3dd5dc"]]},{"id":"91d99153.bee16","type":"function","z":"6ef1cfb5.30ff2","name":"Generate SQL SELECT","func":"//first check payload is a string with something in it\nif(!msg.payload || typeof msg.payload != \"string\"){\n node.warn(\"msg.payload is empty\");\n return null;//halt the flow\n}\n\n//Next, trim spoken text & store it in the msg for later use\nmsg.spokenAsText = msg.payload.trim();\n\n//now generate the SQL insert\nmsg.topic = `SELECT * FROM USER \n WHERE VOICE = '${msg.spokenAsText}' \n AND VERIFY = 1 \n LIMIT 1`;\n\nreturn msg;","outputs":1,"noerr":0,"x":570,"y":160,"wires":[["ad74c6fb.bf1f48","14d00403.78a61c"]]},{"id":"ad74c6fb.bf1f48","type":"mysql","z":"6ef1cfb5.30ff2","mydb":"88bd22d4.f7417","name":"DB_MYSQL","x":830,"y":160,"wires":[["b8e27308.e5934","16645e7.046f9a2"]]},{"id":"b8e27308.e5934","type":"debug","z":"6ef1cfb5.30ff2","name":"SELECT Data","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":840,"y":120,"wires":[]},{"id":"14d00403.78a61c","type":"debug","z":"6ef1cfb5.30ff2","name":"SELECT SQL","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":600,"y":120,"wires":[]},{"id":"4551a5d3.81b7dc","type":"play audio","z":"6ef1cfb5.30ff2","name":"","voice":"4","x":820,"y":440,"wires":[]},{"id":"16645e7.046f9a2","type":"function","z":"6ef1cfb5.30ff2","name":"Verify data","func":"\n//if payload is empty send it out of output 2\nif(!msg.payload){\n node.warn(\"payload is null\");\n return [null, msg];//send msg to output 2\n}\n\n//if payload is NOT an array send it out of output 2\nif(Array.isArray(msg.payload) === false){\n node.warn(\"payload array is NOT an array\");\n return [null, msg];//send msg to output 2\n}\n\n//if payload an array BUT has < 1, send it out of output 2\nif(msg.payload.length < 1){\n node.warn(\"payload array is empty\");\n return [null, msg];//send msg to output 2\n}\n\n\n//All checks have passed (data is good!) - send message to output 1\nreturn [msg,null];","outputs":2,"noerr":0,"x":150,"y":300,"wires":[["8cbfcf24.4f099","a283f271.5597e"],["ecb3ed5d.ff044"]],"outputLabels":["Good","Bad"]},{"id":"290fc1a6.566eee","type":"debug","z":"6ef1cfb5.30ff2","name":"INSERT SQL","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":530,"y":260,"wires":[]},{"id":"ae7afc65.b3256","type":"debug","z":"6ef1cfb5.30ff2","name":"Play Sound Debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":850,"y":400,"wires":[]},{"id":"33d05629.98a49a","type":"inject","z":"6ef1cfb5.30ff2","name":"TEST hello hello hello","topic":"","payload":"hello hello hello","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":320,"y":60,"wires":[["91d99153.bee16"]]},{"id":"88bd22d4.f7417","type":"MySQLdatabase","z":"","host":"165.22.51.44","port":"3306","db":"hireelec_vaes","tz":""}]
ya, it successful in all thing....but the NAME upload is the hello hello hello...
it should be upload the people(NAME from USER table) who has VOICE hello hello hello
msg.topic = `INSERT INTO HISTORY (NAME)
VALUES( '${msg.spokenAsText}' )`;
return msg;
can add SELECT NAME FROM USER WHERE VOICE = msg.spokenAsText in VALUES?
if no, it just upload the VOICE data... no the NAME
No need. You already have it.
Just get it from the previous select
Modify item 2
//if payload is empty send it out of output 2
if(!msg.payload){
node.warn("payload is null");
return [null, msg];//send msg to output 2
}
//if payload is NOT an array send it out of output 2
if(Array.isArray(msg.payload) === false){
node.warn("payload array is NOT an array");
return [null, msg];//send msg to output 2
}
//payload IS an array BUT has < 1, send it out of output 2
if(msg.payload.length < 1){
node.warn("payload array is empty");
return [null, msg];//send msg to output 2
}
/********** ADD THE LINE BELOW ******************/
msg.result = msg.payload[0]; //SAVE the DB SELECT Result for later use
//All checks have passed (data is good!) - send message to output 1
return [msg,null];
Modify item 3
//generate the SQL insert
msg.topic = `INSERT INTO HISTORY (NAME)
VALUES( '${msg.result.NAME}' )`;
return msg;
thank you, u help me a lot
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
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