ER_PARSE_ERROR in mysql node

Need help to insert query using mysql node. I'm getting error:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER ;;
CREATE TRIGGER t BEFORE INSERT ON gdc
FOR EACH ROW BEGIN
SET ...' at line 1

But when inserted using "phpmyadmin", shows no error.

What am I doing wrong?

[{"id":"aadf71f9.37505","type":"mysql","z":"50a610c6.08a6a","mydb":"a844720c.608d6","name":"","x":1230,"y":400,"wires":[["2c15ffc.d6401"]]},{"id":"2c15ffc.d6401","type":"debug","z":"50a610c6.08a6a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1390,"y":400,"wires":[]},{"id":"ae5aa79c.41a748","type":"template","z":"50a610c6.08a6a","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"DELIMITER ;; \nCREATE TRIGGER t BEFORE INSERT ON gdc \nFOR EACH ROW BEGIN \nSET NEW.top_die_temp_gdc1 = NEW.top_die_temp_gdc1 / 10; \nSET NEW.bottom_die_temp_gdc1 = NEW.bottom_die_temp_gdc1 / 10; \nSET NEW.top_die_temp_gdc2 = NEW.top_die_temp_gdc2 / 10; \nSET NEW.bottom_die_temp_gdc2 = NEW.bottom_die_temp_gdc2 / 10; \nSET NEW.top_die_temp_gdc3 = NEW.top_die_temp_gdc3 / 10; \nSET NEW.bottom_die_temp_gdc3 = NEW.bottom_die_temp_gdc3 / 10; \nSET NEW.top_die_temp_gdc4 = NEW.top_die_temp_gdc4 / 10; \nSET NEW.bottom_die_temp_gdc4 = NEW.bottom_die_temp_gdc4 / 10; \nSET NEW.top_die_temp_gdc5 = NEW.top_die_temp_gdc5 / 10; \nSET NEW.bottom_die_temp_gdc5 = NEW.bottom_die_temp_gdc5 / 10; \nEND;; \nDELIMITER ;\nINSERT INTO `gdc`(`date`, `time`, `water_temp`, `ci_temp`,`hydraulic_temp`, `top_die_temp_gdc1`, `side_die_temp_gdc1`, `bottom_die_temp_gdc1`,`top_die_temp_gdc2`, `side_die_temp_gdc2`, `bottom_die_temp_gdc2`,`top_die_temp_gdc3`, `side_die_temp_gdc3`, `bottom_die_temp_gdc3`,`top_die_temp_gdc4`, `side_die_temp_gdc4`, `bottom_die_temp_gdc4`,`top_die_temp_gdc5`, `side_die_temp_gdc5`, `bottom_die_temp_gdc5`) \nVALUES ('2021-11-22','14:34','25','29','392','334','33','339','331','37','348','344','42','375','350','34','344','346','33','344')","output":"str","x":1080,"y":400,"wires":[["aadf71f9.37505"]]},{"id":"ffcbb284.e67e6","type":"inject","z":"50a610c6.08a6a","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":920,"y":400,"wires":[["ae5aa79c.41a748"]]},{"id":"a844720c.608d6","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"mts","tz":"","charset":""}]

I don't think the sql node copes with mulitple statements like that.

:confused:. trying a different route using a function node by following below post.

Using mysql node but no joy with insert query - #3 by Nodi.Rubrum

solved. now don't need to generate trigger in mysql querry.

[{"id":"86954fb0.83bda","type":"function","z":"50a610c6.08a6a","g":"a4a9feca.8bad3","name":"GDC - 1, 2, 3, 4, 5","func":"msg.topic = \"INSERT INTO `gdc`(`date`,`time`,`water_temp`,`ci_temp`,`hydraulic_temp`,`top_die_temp_gdc1`,`side_die_temp_gdc1`,`bottom_die_temp_gdc1`,`top_die_temp_gdc2`,`side_die_temp_gdc2`,`bottom_die_temp_gdc2`,`top_die_temp_gdc3`,`side_die_temp_gdc3`,`bottom_die_temp_gdc3`,`top_die_temp_gdc4`,`side_die_temp_gdc4`,`bottom_die_temp_gdc4`,`top_die_temp_gdc5`,`side_die_temp_gdc5`,`bottom_die_temp_gdc5`)\"+\n\"VALUES(\"\n    + \"'\" + msg.myymd     + \"',\"\n    + \"'\" + msg.mytime    + \"',\"\n    + \"'\" + msg.payload.D7030 + \"',\"    //water_temp\n    + \"'\" + msg.payload.D7034 + \"',\"    //ci_temp\n    + \"'\" + msg.payload.D7032 + \"',\"    //hydraulic_temp\n    + \"'\" + msg.payload.D7000/10 + \"',\" //top_die_temp_gdc1\n    + \"'\" + msg.payload.D7002 + \"',\"    //side_die_temp_gdc1\n    + \"'\" + msg.payload.D7004/10 + \"',\" //bottom_die_temp_gdc1\n    + \"'\" + msg.payload.D7006/10 + \"',\" //top_die_temp_gdc2\n    + \"'\" + msg.payload.D7008 + \"',\"    //side_die_temp_gdc2\n    + \"'\" + msg.payload.D7010/10 + \"',\" //bottom_die_temp_gdc2\n    + \"'\" + msg.payload.D7012/10 + \"',\" //top_die_temp_gdc3\n    + \"'\" + msg.payload.D7014 + \"',\"    //side_die_temp_gdc3\n    + \"'\" + msg.payload.D7016/10 + \"',\" //bottom_die_temp_gdc3\n    + \"'\" + msg.payload.D7018/10 + \"',\" //top_die_temp_gdc4\n    + \"'\" + msg.payload.D7020 + \"',\"    //side_die_temp_gdc4\n    + \"'\" + msg.payload.D7022/10 + \"',\" //bottom_die_temp_gdc4\n    + \"'\" + msg.payload.D7024/10 + \"',\" //top_die_temp_gdc5\n    + \"'\" + msg.payload.D7026 + \"',\"    //side_die_temp_gdc5\n    + \"'\" + msg.payload.D7028/10 + \"')\"; //bottom_die_temp_gdc5\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1210,"y":100,"wires":[["12d51ed2.7ac181"]]}]
1 Like