Hello,
I've been using the mysql node for INSERT and CALL operations successfully but have reached a roadblock in understanding how to use it for the equivalent of the SQL UPDATE statement. The INSERT code snippet has been working for several years now. A sample flow is captured below:
[{"id":"574ac50e.0e43fc","type":"rpi-sensehat in","z":"202ea5e3.ae1b3a","name":"PiSH-11-1","motion":false,"env":true,"stick":false,"x":60,"y":260,"wires":[["fff6066a.aa9708"]]},{"id":"fff6066a.aa9708","type":"delay","z":"202ea5e3.ae1b3a","name":"HoldEm","pauseType":"rate","timeout":"1","timeoutUnits":"minutes","rate":"1","nbRateUnits":"1","rateUnits":"minute","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":200,"y":260,"wires":[["7d44886b.f584b8"]]},{"id":"7d44886b.f584b8","type":"function","z":"202ea5e3.ae1b3a","name":"PackEm","func":"// an experiment in using basic SQL statements rather an \"efficient\" stored procedure\n// appropriate \"stored\" procedure exists, used in other flows\nnode.status({fill:\"yellow\", shape:\"ring\", text:\"processing\"});\n// special case for current PiSH-11-1; TBD: stored procedure for general case\nvar currentStatus = 0;\nvar deviceIdT = 1453;\nvar deviceIdH = 1454;\nvar deviceIdP = 1455;\nvar dtStamp = new Date();\nvar ISOstrTZ = dtStamp.toISOString();\n// Datetime formats:\n// - MariaDB, MySQL: \"2019-07-04 03:27:22.54\";\n// - Javascript ISO: \"2019-07-04T03:27:22.54Z\";\n// very embarassing code below; shows how little I know about Javascript\nvar ISOstrZ = ISOstrTZ.replace(\"T\", \" \");\nvar waqt = ISOstrZ.replace(\"Z\", \"\");\n\n// INSERT Telemetry\nvar msgTelemetry = {};\nmsgTelemetry.topic = \"INSERT INTO Telemetry \" +\n \"(Waqt, DeviceId, Status, Reading, Dimension, UOM) \" +\n \"VALUES ?\";\nvar newTelemetry = [\n [waqt, deviceIdT, currentStatus, msg.payload.temperature, \"Temperature\", \"\\xB0C\"],\n [waqt, deviceIdH, currentStatus, msg.payload.humidity, \"RHumidity\", \"%rH\"],\n [waqt, deviceIdP, currentStatus, msg.payload.pressure, \"Pressure\", \"mb\"]\n ];\nmsgTelemetry.payload = [newTelemetry];\n\nvar txTime = dtStamp.toLocaleTimeString('en-US');\nnode.status({fill:\"green\", shape:\"ring\", text:\"TX: \" + txTime});\n\n// UPDATE Device\nvar msgDevice = {};\nmsgDevice.topic = \"SELECT Device SET LastHeard = NOW(), CurrentStatus = ? WHERE DeviceId = ?\";\nvar updateDevice = [\n [currentStatus, deviceIdT],\n [currentStatus, deviceIdH],\n [currentStatus, deviceIdP]\n ];\nmsgDevice.payload = [updateDevice];\n\nnode.status({fill:\"green\", shape:\"dot\", text:\"LastHeard: \" + txTime});\nreturn [msgDevice, msgTelemetry];","outputs":2,"noerr":0,"x":340,"y":260,"wires":[["3fb0ae.481f7752","66f6be8c.5d08a8"],["b07c532d.85a23"]]},{"id":"3fb0ae.481f7752","type":"mysql","z":"202ea5e3.ae1b3a","mydb":"bb9bff56.2345e","name":"UPDATE Device","x":520,"y":220,"wires":[[]]},{"id":"b07c532d.85a23","type":"mysql","z":"202ea5e3.ae1b3a","mydb":"bb9bff56.2345e","name":"INSERT Telemetry","x":530,"y":280,"wires":[["e094352c.5e4d28"]]},{"id":"bb9bff56.2345e","type":"MySQLdatabase","z":"","host":"raspbari5.parkcircus.org","port":"3306","db":"ha","tz":""}]
The UPDATE statement has been verified to work from the (MariaDB) console command prompt with the same database as follows:
UPDATE Device SET LastHeard = NOW(), CurrentStatus = 0 WHERE DeviceId = 1;
After reading the "docs" I changed the command from UPDATE to SELECT only in the flow. The INSERT works fine and I (in my ignorance) simply adapted it for the subsequent update operation. The msg structure is similar (topic & payload) for both sets of operations (as confirmed by Debug node). I have a stored procedure to bypass all this but simply for my education (& learning 
), I would like to understand the erroneous code in my example.
The code snippet for the update operation is as follows:
var msgDevice = {};
msgDevice.topic = "SELECT Device SET LastHeard = NOW(), CurrentStatus = ? WHERE DeviceId = ?";
var updateDevice = [
[currentStatus, deviceIdT],
[currentStatus, deviceIdH],
[currentStatus, deviceIdP]
];
msgDevice.payload = [updateDevice];
Please note that I have used the command UPDATE and SELECT successively but the error persisted. The node status is Error for the update operation and it is OK for the insert operation. Also, the code variables have correct values being assembled in the transmission to the mysql node as confirmed by viewing a temporary Debug node.
I know that there are other nodes that could be used but since I have confined my limited use (in many, many flows) to the mysql code, I would prefer to correct my mistake here rather than embark on yet another substitution exercise. Thanks for any advice.
Kind regards.
