Unable to Insert Data to the Database

#1

Hi,

I'm unable to insert data to the database. The following error occurred once I click the Save button:

image

The following is the flow:

[{"id":"a181134e.edb82","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"machineCode","label":"Machine Code","group":"8a70bacc.615718","order":1,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"machineCode","x":965,"y":60,"wires":[["f402e4c4.c30a18"]]},{"id":"6af9ae98.a40c8","type":"change","z":"9f52e83b.5b44f8","name":"clear-text-inputs","rules":[{"t":"set","p":"clear","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":605,"y":480,"wires":[["a181134e.edb82","415c3e3d.03a44","4af2c428.57646c","63dff417.6b7acc","e194e211.f2034","9bfe9fb9.48b82","8da75106.5596d","437a5349.f6673c","b4bd6e0d.d04eb","ab81ae8d.e9876","ed291fe8.d0053","e50970b9.13ec3","2c63516b.b8ccbe","484de85e.7f3b28","8cfa1f9b.b4f82"]]},{"id":"4ef40af8.594684","type":"change","z":"9f52e83b.5b44f8","name":"clear-dropdown","rules":[{"t":"set","p":"payload","pt":"msg","to":"false","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":605,"y":600,"wires":[["71842900.e5f4d8","e734029b.1a5d8","84e9ac7a.e3e39","b196da07.832eb8","8e404bf4.551de8"]]},{"id":"415c3e3d.03a44","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"description","label":"Description","group":"8a70bacc.615718","order":2,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"description","x":955,"y":105,"wires":[["f402e4c4.c30a18"]]},{"id":"71842900.e5f4d8","type":"mui_dropdown","z":"9f52e83b.5b44f8","name":"location","label":"","place":"Select Location","group":"8a70bacc.615718","order":3,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"location","x":945,"y":150,"wires":[["f402e4c4.c30a18"]]},{"id":"e734029b.1a5d8","type":"mui_dropdown","z":"9f52e83b.5b44f8","name":"type","label":"","place":"Select Type","group":"8a70bacc.615718","order":5,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"type","x":935,"y":240,"wires":[["f402e4c4.c30a18"]]},{"id":"4af2c428.57646c","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"oee","label":"OEE Threshold","group":"c0ad49ec.dff698","order":1,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"oee","x":935,"y":285,"wires":[["f402e4c4.c30a18"]]},{"id":"63dff417.6b7acc","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"ip","label":"IP Address","group":"c0ad49ec.dff698","order":2,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"ip","x":935,"y":330,"wires":[["f402e4c4.c30a18"]]},{"id":"e194e211.f2034","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"port","label":"IP Port","group":"c0ad49ec.dff698","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"port","x":935,"y":375,"wires":[["f402e4c4.c30a18"]]},{"id":"9bfe9fb9.48b82","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"url","label":"Client URL","group":"c0ad49ec.dff698","order":4,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"url","x":935,"y":420,"wires":[["f402e4c4.c30a18"]]},{"id":"b196da07.832eb8","type":"mui_dropdown","z":"9f52e83b.5b44f8","name":"poll","label":"","place":"Select Poll Rate","group":"18a2002a.8e676","order":1,"width":0,"height":0,"passthru":false,"options":[{"label":"5 seconds","value":5,"type":"num"},{"label":"10 seconds","value":10,"type":"num"},{"label":"15 seconds","value":15,"type":"num"},{"label":"20 seconds","value":20,"type":"num"},{"label":"25 seconds","value":25,"type":"num"},{"label":"30 seconds","value":30,"type":"num"},{"label":"35 seconds","value":35,"type":"num"},{"label":"40 seconds","value":40,"type":"num"},{"label":"45 seconds","value":45,"type":"num"},{"label":"50 seconds","value":50,"type":"num"},{"label":"55 seconds","value":55,"type":"num"},{"label":"60 seconds","value":60,"type":"num"}],"payload":"","topic":"poll","x":935,"y":465,"wires":[["f402e4c4.c30a18"]]},{"id":"8da75106.5596d","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"processCode","label":"Process Code","group":"18a2002a.8e676","order":2,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"processCode","x":955,"y":510,"wires":[["f402e4c4.c30a18"]]},{"id":"437a5349.f6673c","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"srcLocPath","label":"Source Local Path","group":"18a2002a.8e676","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"srcLocPath","x":955,"y":555,"wires":[["f402e4c4.c30a18"]]},{"id":"b4bd6e0d.d04eb","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"dstSrvrPath","label":"Destination Server Path","group":"18a2002a.8e676","order":4,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"dstSrvrPath","x":955,"y":600,"wires":[["f402e4c4.c30a18"]]},{"id":"ab81ae8d.e9876","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"fileExecSrvr","label":"File Execute Server","group":"18a2002a.8e676","order":5,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"fileExecSrvr","x":955,"y":645,"wires":[["f402e4c4.c30a18"]]},{"id":"ed291fe8.d0053","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"fileExecLoc","label":"File Execute Local","group":"7feabf50.affdc","order":1,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"fileExecLoc","x":955,"y":690,"wires":[["f402e4c4.c30a18"]]},{"id":"e50970b9.13ec3","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"dtSrc1","label":"Data Type Source 1","group":"7feabf50.affdc","order":2,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"dtSrc1","x":935,"y":735,"wires":[["f402e4c4.c30a18"]]},{"id":"2c63516b.b8ccbe","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"dtSrc2","label":"Data Type Source 2","group":"7feabf50.affdc","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"dtSrc2","x":935,"y":780,"wires":[["f402e4c4.c30a18"]]},{"id":"84e9ac7a.e3e39","type":"mui_dropdown","z":"9f52e83b.5b44f8","name":"owner","label":"","place":"Select Owner","group":"c0ad49ec.dff698","order":5,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"owner","x":935,"y":825,"wires":[["f402e4c4.c30a18"]]},{"id":"484de85e.7f3b28","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"to","label":"Alert to","group":"7feabf50.affdc","order":4,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"to","x":935,"y":870,"wires":[["f402e4c4.c30a18"]]},{"id":"8cfa1f9b.b4f82","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"cc","label":"Alert cc","group":"7feabf50.affdc","order":5,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"cc","x":935,"y":915,"wires":[["f402e4c4.c30a18"]]},{"id":"6aae142b.83450c","type":"mui_button","z":"9f52e83b.5b44f8","name":"saveButton","group":"8dbe39c3.7d0808","order":1,"width":0,"height":0,"passthru":false,"label":"Save","color":"","bgcolor":"","icon":"","payload":"{}","payloadType":"global","topic":"saveButton","x":955,"y":960,"wires":[["f402e4c4.c30a18"]]},{"id":"f402e4c4.c30a18","type":"join","z":"9f52e83b.5b44f8","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"20","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1235,"y":480,"wires":[["f3855113.80ed"]]},{"id":"f3855113.80ed","type":"switch","z":"9f52e83b.5b44f8","name":"","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"saveButton","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":1370,"y":480,"wires":[["bad8dc6a.c75","a9fef1cd.75b25"]]},{"id":"bad8dc6a.c75","type":"function","z":"9f52e83b.5b44f8","name":"select-query","func":"select = {topic: \"SELECT COUNT(*) AS count FROM machine2 WHERE name='\" + msg.payload.machineCode + \"'\"};\nreturn select;","outputs":1,"noerr":0,"x":1525,"y":480,"wires":[["f3b93590.9c5618","e6e77139.efc47"]]},{"id":"59eafeac.7ebf5","type":"switch","z":"9f52e83b.5b44f8","name":"","property":"payload[0].count","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1370,"y":630,"wires":[["a9fef1cd.75b25"],["6537be1e.b9685"]]},{"id":"a9fef1cd.75b25","type":"join","z":"9f52e83b.5b44f8","name":"","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":1505,"y":555,"wires":[["c3cd55f7.ab90b8"]]},{"id":"6537be1e.b9685","type":"function","z":"9f52e83b.5b44f8","name":"failure-msg","func":"actionString = \"Data insertion failed!\";\nmsg.payload = actionString;\nreturn msg;","outputs":1,"noerr":0,"x":1690,"y":660,"wires":[["8065a3c7.7f1de"]]},{"id":"c3cd55f7.ab90b8","type":"function","z":"9f52e83b.5b44f8","name":"insert-query","func":"var machineCode = {payload: (msg.payload.saveButton.machineCode)};\nvar description = {payload: (msg.payload.saveButton.description)};\nvar location = {payload: (msg.payload.saveButton.location)};\nvar bu = {payload: (msg.payload.saveButton.bu)};\nvar oee = {payload: (msg.payload.saveButton.oee)};\nvar type = {payload: (msg.payload.saveButton.type)};\nvar ip = {payload: (msg.payload.saveButton.ip)};\nvar port = {payload: (msg.payload.saveButton.port)};\nvar url = {payload: (msg.payload.saveButton.url)};\nvar poll = {payload: (msg.payload.saveButton.poll)};\nvar processCode = {payload: (msg.payload.saveButton.processCode)};\n\nvar srcLocPath = {payload: (msg.payload.saveButton.srcLocPath)};\nvar dstSrvrPath = {payload: (msg.payload.saveButton.dstSrvrPath)};\nvar fileExecSrvr = {payload: (msg.payload.saveButton.fileExecSrvr)};\nvar fileExecLoc = {payload: (msg.payload.saveButton.fileExecLoc)};\nvar dtSrc1 = {payload: (msg.payload.saveButton.dtSrc1)};\nvar dtSrc2 = {payload: (msg.payload.saveButton.dtSrc2)};\n\nvar owner = {payload: (msg.payload.saveButton.owner)};\nvar to = {payload: (msg.payload.saveButton.to)};\nvar cc = {payload: (msg.payload.saveButton.cc)};\n\ninsert = {topic: \"INSERT INTO machine2 (name,description,location,bu,oee,type,ip_address,clienturl,process_code,source_local_path,dest_server_path,file_execute_server,file_execute_local,data_type_source1,data_type_source2,owner_id,alert_to,created_at) VALUES ('\" + machineCode.payload + \"','\" + description.payload + \"','\" + location.payload + \"','\" + bu.payload + \"','\" + oee.payload + \"','\" + type.payload + \"','\" + ip.payload + \"','\" + port.payload + \"','\" + poll.payload + \"','\" + srcLocPath.payload + \"','\" + dstSrvrPath.payload + \"','\" + fileExecSrvr.payload + \"','\" + dtSrc1.payload + \"','\" + owner.payload + \"','\" + cc.payload + \"',CURRENT_TIMESTAMP)\"};\n\nactionString = {payload: (\"Data inserted successfully!\")};\nreturn [insert,actionString];","outputs":2,"noerr":0,"x":1690,"y":555,"wires":[["17bc6a9d.0160f5"],["8065a3c7.7f1de"]]},{"id":"8065a3c7.7f1de","type":"debug","z":"9f52e83b.5b44f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1870,"y":615,"wires":[]},{"id":"f3b93590.9c5618","type":"link out","z":"9f52e83b.5b44f8","name":"select-output","links":["c73fd307.ff896"],"x":1635,"y":480,"wires":[]},{"id":"5faf6451.ff8dbc","type":"link in","z":"9f52e83b.5b44f8","name":"switch-input","links":["35f20487.65c18c"],"x":1275,"y":630,"wires":[["59eafeac.7ebf5"]]},{"id":"17bc6a9d.0160f5","type":"link out","z":"9f52e83b.5b44f8","name":"insert-output","links":["7ae1b537.310c1c"],"x":1815,"y":510,"wires":[]},{"id":"8e404bf4.551de8","type":"mui_dropdown","z":"9f52e83b.5b44f8","name":"bu","label":"","place":"Select Business Unit","group":"8a70bacc.615718","order":4,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"bu","x":935,"y":195,"wires":[["f402e4c4.c30a18"]]},{"id":"487b740c.f6ff1c","type":"mui_button","z":"9f52e83b.5b44f8","name":"clearButton","group":"8dbe39c3.7d0808","order":2,"width":0,"height":0,"passthru":false,"label":"Clear","color":"","bgcolor":"","icon":"","payload":"{}","payloadType":"global","topic":"","x":295,"y":540,"wires":[["6af9ae98.a40c8","4ef40af8.594684"]]},{"id":"79b49b22.1ed3e4","type":"function","z":"9f52e83b.5b44f8","name":"select-loc","func":"msg.topic=\"SELECT id,location FROM location\";\nvar loc = msg.payload.location;\nvar id = msg.payload.id;\nmsg.options = [{loc:id}];\nreturn msg;","outputs":1,"noerr":0,"x":330,"y":150,"wires":[["73f28d25.053ec4"]]},{"id":"5c1a76ee.bf6bb8","type":"inject","z":"9f52e83b.5b44f8","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":145,"y":225,"wires":[["79b49b22.1ed3e4","3bf95fed.e0e85","13a07979.8b45d7","3984a3ca.b8367c"]]},{"id":"73f28d25.053ec4","type":"mysql","z":"9f52e83b.5b44f8","mydb":"24d38c58.3fc104","name":"database","x":495,"y":225,"wires":[["91327e7.78e0c8","9b34957c.708a08","373bee74.9cd492","d8f8d333.cb6ff"]]},{"id":"91327e7.78e0c8","type":"change","z":"9f52e83b.5b44f8","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.location","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":150,"wires":[["71842900.e5f4d8"]]},{"id":"13a07979.8b45d7","type":"function","z":"9f52e83b.5b44f8","name":"select-bu","func":"msg.topic=\"SELECT unit,description FROM business_unit\";\nvar desc = msg.payload.description;\nvar unit = msg.payload.unit;\nmsg.options = [{desc:unit}];\nreturn msg;","outputs":1,"noerr":0,"x":330,"y":195,"wires":[["73f28d25.053ec4"]]},{"id":"9b34957c.708a08","type":"change","z":"9f52e83b.5b44f8","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.description","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":195,"wires":[["8e404bf4.551de8"]]},{"id":"3984a3ca.b8367c","type":"function","z":"9f52e83b.5b44f8","name":"select-type","func":"msg.topic=\"SELECT id, CONCAT(name,' | ',description) AS typeDesc FROM machine_type\";\nvar desc = msg.payload.typeDesc;\nvar id = msg.payload.id;\nmsg.options = [{desc:id}];\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":240,"wires":[["73f28d25.053ec4"]]},{"id":"373bee74.9cd492","type":"change","z":"9f52e83b.5b44f8","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.typeDesc","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":240,"wires":[["e734029b.1a5d8"]]},{"id":"3bf95fed.e0e85","type":"function","z":"9f52e83b.5b44f8","name":"select-owner","func":"msg.topic=\"SELECT id,name as username FROM users\";\nvar name = msg.payload.username;\nvar id = msg.payload.id;\nmsg.options = [{name:id}];\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":285,"wires":[["73f28d25.053ec4"]]},{"id":"d8f8d333.cb6ff","type":"change","z":"9f52e83b.5b44f8","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.username","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":285,"wires":[["84e9ac7a.e3e39"]]},{"id":"e6e77139.efc47","type":"debug","z":"9f52e83b.5b44f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1695,"y":405,"wires":[]},{"id":"8a70bacc.615718","type":"mui_group","z":"","name":"mGroup 1","tab":"d4e8d996.700768","order":1,"disp":false,"width":"6","collapse":false},{"id":"c0ad49ec.dff698","type":"mui_group","z":"","name":"mGroup 2","tab":"d4e8d996.700768","order":2,"disp":false,"width":"6","collapse":false},{"id":"18a2002a.8e676","type":"mui_group","z":"","name":"mGroup 3","tab":"d4e8d996.700768","order":3,"disp":false,"width":"6","collapse":false},{"id":"7feabf50.affdc","type":"mui_group","z":"","name":"mGroup 4","tab":"d4e8d996.700768","order":4,"disp":false,"width":"6","collapse":false},{"id":"8dbe39c3.7d0808","type":"mui_group","z":"","name":"mGroup 5","tab":"d4e8d996.700768","order":5,"disp":false,"width":"6","collapse":false},{"id":"24d38c58.3fc104","type":"MySQLdatabase","z":"","host":"10.21.11.15","port":"3306","db":"i4inari","tz":""},{"id":"d4e8d996.700768","type":"mui_tab","z":"","name":"I4Inari : Machine Management ","icon":"dashboard","order":3}]

Thanks for helping.

#2

Does the error not tell you everything you need to know?

Whatever SQL statement you are building has a different number of columns listed to the number of values.

I suggest you add a debug node to see the exact SQL query you are passing to the database. You can then fix your code that is generating it.