Parse error - insert into MySQL

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).


Hi @jaguar

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.

And thank you again, it was just the quotes surrounding the column names, removing them solved the issue.