Parse error - insert into MySQL

#1

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

Regards
jaguar

0 Likes

#2

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.

Nick

0 Likes

#3

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.

0 Likes

#4

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

0 Likes