Multivalue mysql insert to mariadb

General
1

Hello, first time poster here! I’ve been working to replace some older python scripts that export data from MSSQL and import it into MariaDB. I can successfully do this with nodered function by creating separate insert statements for the data using the node-red-node-mysql process, using a parameterized insert statement. What’s tripping me up is that I'm doing ~150,000 inserts, and I want to validate that the process is completed successfully, and do it as fast as possible. It would seem that the most straightforward way is to use the VALUES ? parameter in the query in the .topic, and feed an array of arrays via the .payload, but I cannot, for the life of me, get it to work.

For diagnostic purposes, I have broken it down to just a function node that states:

msg.payload = [["aa","bb","cc","dd","ee","ff","gg","hh","ii"],
               ["ab","bc","cd","de","ef","fg","gh","hi","ij"]];
msg.topic = "insert into Company_ShipTo (Name,Address1,Address2,Address3,City,State,Zip,CoNum,CustNum) VALUES ? ;";  
return msg;

and pass that to the node-red-node-mysql node, which will only produce:

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 ''aa', 'bb', 'cc', 'dd', 'ee', 'ff', 'gg', 'hh', 'ii'' at line 1

This same node will take hundreds of thousands of individual inserts. Can anyone point me in the direction of what I’m doing wrong here?

Nodered 4.1, node-red-node-mysql 2.0.0

2

You can try to compose your values into the SQL statement after the VALUES keyword.
For example:

const values = [
    ["a1","b1","c1"],
    ["a2","b2","c2"]
];
let valueStr = "";
for (let i = 0 ; i < values.length ; i++)
    valueStr += ( "(" + values[i] + "),");
valueStr = valueStr.replace(/,$/,';');

msg.topic = "insert into myTable (fieldA,fieldB,fieldC) VALUES " + valueStr;
return msg;
3

That is definitely workable. I was hoping to use a parameterized approach to protect me from SQL injection problems by keeping the data in the payload object. All the mysql nodes seem to have large bold warnings about using values from variables on the SQL line directly.

Is it a known issue the that node-red-node-mysql node doesn’t handle the “VALUES ?” parameter?

4

Maybe someone else here knows, or you can check this in the node's documentation or code. Of course sending the values as msg parameters is preferable, but the question is if the node supports it.

5

The docs suggest that you should use something like
msg.topic = "insert into Company_ShipTo ('Name','Address1', ...) VALUES (?,?,?,?,?,?,?,?,?) ;"

6

That doesn’t seem to work when I pass an array of arrays.

msg.topic = "insert into Company_ShipTo (Name,Address1,Address2,Address3,City,State,Zip,CoNum,CustNum) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?);";
msg.payload = [["aa","bb","cc","dd","ee","ff","gg","hh","ii"],
               ["ab","bc","cd","de","ef","fg","gh","hi","ij"]];
return msg;

node-red-node-mysql returns:

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 '?, ?, ?, ?, ?, ?, ?)' at line 1
7

This flow accepts multiple records as an array of objects and inserts them into a mariadb table

[{"id":"c91a72c08f2ba23d","type":"mysql","z":"5398fd8367dbf130","mydb":"2381cb15.b563bc","name":"","x":240,"y":160,"wires":[["b9789d624273ac6c"]]},{"id":"b91b24827d05e067","type":"inject","z":"5398fd8367dbf130","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":110,"y":100,"wires":[["0456ccacd8e9f01e"]]},{"id":"0456ccacd8e9f01e","type":"template","z":"5398fd8367dbf130","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"[\n{ \"flavour\": \"Pea\", \"colour\": \"green\" },\n{ \"flavour\": \"Oxtail\", \"colour\": \"Brown\" },\n{ \"flavour\": \"Tomato\", \"colour\": \"Red\" },\n{ \"flavour\": \"Chicken\", \"colour\": \"beige\" },\n{ \"flavour\": \"Mushroom\", \"colour\": \"beige\" },\n{ \"flavour\": \"Pea\", \"colour\": \"green\" },\n{ \"flavour\": \"Oxtail\", \"colour\": \"Brown\" },\n{ \"flavour\": \"Tomato\", \"colour\": \"Red\" },\n{ \"flavour\": \"Chicken\", \"colour\": \"beige\" },\n{ \"flavour\": \"Mushroom\", \"colour\": \"beige\" }\n]","output":"json","x":240,"y":100,"wires":[["67450b9b5fa2cea6"]]},{"id":"67450b9b5fa2cea6","type":"function","z":"5398fd8367dbf130","name":"function 5","func":"let soups = msg.payload;\nlet sql = \"INSERT INTO soup (flavour, colour) VALUES ?\";\nlet values = soups.map(soup => [soup.flavour, soup.colour]);\n\nmsg.topic = sql;\nmsg.payload = [values]; // MySQL node expects an array of arrays for multiple inserts\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":100,"wires":[["c91a72c08f2ba23d"]]},{"id":"b9789d624273ac6c","type":"debug","z":"5398fd8367dbf130","name":"debug 20","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":380,"y":160,"wires":[]},{"id":"2381cb15.b563bc","type":"MySQLdatabase","name":"espresso","host":"127.0.0.1","port":"3306","db":"espresso","tz":"","charset":"UTF8"},{"id":"1bd9f1dc25e8bfad","type":"global-config","env":[],"modules":{"node-red-node-mysql":"2.0.0"}}]
8

So, it’s working will all your help. Turns out, you have to define the payload as an array, of arrays, of arrays.

jbudd, the example you provided took the array of arrays, and put that in an array when defining the payload. That’s what I was missing.

The value for msg.payload ended up being:

[[["Pea","green"],["Oxtail","Brown"],["Tomato","Red"],["Chicken","beige"],["Mushroom","beige"],["Pea","green"],["Oxtail","Brown"],["Tomato","Red"],["Chicken","beige"],["Mushroom","beige"]]]