How to insert the output of the buffer node to mysql db

General
1

Am not able to insert the value which is coming out of the buffer parser node into the local db (mariadb).
please find below flow and debug window

image
image1583×675 111 KB

function node program

image
image999×617 61.6 KB

buffer parser

image
image1227×787 67.1 KB

Appreciate , if some one help me in resolving it
thank you !

2

Why is the function node accessing values from msg.payload.Rows[i].V1 when your data (output from the buffer parser) is in msg.payload.V1?

The output from the modbus node will be 1 buffer. You correctly turn those into values V1~V3

Next you simply need to format the SQL insert. I don't understand what the function looping is supposed to be doing (looks like a copy and paste job)

3

thanks for the quick reply .

sorry , am new to node red and program

appreciate your help , if you can verify the following program

var topic =msg.topic;
var a = msg.payload.V1;
var b = msg.payload.V2;
var c = msg.payload.V3;
payload = 'INSERT INTO meter(V1,V2,V3) VALUES ("a","b","c")'
msg.payload=payload;
return msg
4

The function node with the red triangle has a syntax error in it. It should be marked if you open it and look.

5

That is certainly closer however you need to read the help info for the SQL node on the right-hand sidebar - it will tell you how to pass a SQL command.

To get you moving, this will likely be closer (but you need to understand how to correctly pass parameters to the SQL node to avoid possible SQL injection hacks)...

var a = msg.payload.V1;
var b = msg.payload.V2;
var c = msg.payload.V3;
msg.topic = `INSERT INTO meter(V1,V2,V3) VALUES (${a}, ${b}, ${c})`
return msg;
6

Doesn't the query go in the topic?

7

Doh. I forgot to edit that bit.

Edit...
Updated the sample code.

8

Thank you Steve-Mcl and Colin for your responses :grinning:

Along with Steve sample query , additional lines added in query. Initially the flow was successfully deployed and got output in DB too..

var sql =' ';
var a = msg.payload.V1;
var b = msg.payload.V2;
var c = msg.payload.V3;
var outputs = ;
sql = 'INSERT INTO meter (V1,V2,V3) VALUE ({a},{b},${c})'
outputs.push({topic:sql});
return [ outputs ];

Another issue is raised :
"Error: ER_PARSE_ERROR: 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 '{a},{b},{c})' at line 1"

Appreciate , if some one help me in resolving it
thank you !

Energycharges

9

Check the example I posted. I used ` backticks around the string. This is known as a template literal. This is what makes ${a} turn into a variable value.

You didn't. You used single quotes and you missed out the $ symbol...

11

sql = 'INSERT INTO meter (V1,V2,V3) VALUE ({a},{b},${c})'

That was a mistake caused while posting the issue. Query was

sql = 'INSERT INTO meter (V1,V2,V3) VALUE (${a},${b},${c}).

Thank you for sharing me , Template _literals reference.

Appreciate , your effort in notifying the issue
thank you !

Energycharges

12

Thank you Steve for your timely response. :grinning:

updated query:

var sql ="";
var a = msg.payload.V1;
var b = msg.payload.V2;
var c = msg.payload.V3;
var outputs = ;
sql = 'INSERT INTO meter (V1,V2,V3) VALUES ({a},{b},${c})'
outputs.push({topic:sql});
return [ outputs ];

Issue raised:

"Error: ER_PARSE_ERROR: 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 '{a},{b},{c})' at line 1"

thank you !

Energycharges

13

Issue :
"Error: ER_PARSE_ERROR: 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 '{a},{b},{c})' at line 1"

var sql=" ";

var a = msg.payload.V1;
var b = msg.payload.V2;
var c = msg.payload.V3;
var outputs = ;

sql = 'INSERT INTO meter ( V1,V2,V3) VALUES ({a}, {b}, ${c})';

outputs.push({topic:sql});

return [outputs];

Thanks

Energycharges

14

This is still wrong.

You need to use backticks and$ symbols for all of the variables..

sql =`INSERT INTO meter (V1,V2,V3) VALUES (${a},${b},${c})`
15

Thank you Steve :smiley: :smiley: for your timely help.

image
image840×555 57.1 KB

image
image1113×797 63.6 KB

Thank you
Energycharges

16

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.