Hi All, I read somewhere that the MySQL connector may not be able to process a multiple column insert into MySQL and would appreciate confirmation if that is the case.
I have an incoming string comprised of around 35 varchars and am using substr to split that in a function just before the MySQL connector.
var m = {
topic : "INSERT INTO `node-red`.`tag_reads` ('node_id', `personal_id`) VALUES ('"; + msg.payload.substr(3, 4) + "', '" + msg.payload.substr(20, 33) + "');"
};
return m
If I use just one column and one substr then all is ok, but two or more fail with a parse_error (though the data in the error message is ok and of the correct format).
Is there a workaround, a for loop or something?
I also notice that the first substr doesn't seem to work properly (it should return the single character at position 3 in the string whereas it returns 4 characters starting at position 3).
I've edited your post to properly format the code block by placing three backticks (`) on new lines before and after the code. That ensures the formatting is correct.
I don't know where you read the mysql node can't do multi-column inserts, because it isn't correct.
If you're getting a parse error, you'd normally get some clue as to what is wrong. I note you're using a mix of quotes in your column list - that could be something or it could be nothing.
As for substr ... it is doing exactly what it is meant to - returning 4 characters starting at position 3. If you want the characters between positions 3 and 4, you should use substring.
Thanks Nick for editing (the issues appeared when I copied in the function and I did attempt to correct them). substring solved the issue with the single character but I still get the parse error, error in syntax (either column names or data to be entered) thought he column names and data to be entered are showing correctly.
