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

**URL:** <https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557>\
**Category:** General\
**Created:** [7 September 2020 16:37 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557 "2020-09-07T16:37:09Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Energycharges](https://avatars.discourse-cdn.com/v4/letter/e/e47c2d/32.png) [@Energycharges](https://discourse.nodered.org/u/Energycharges)\
**Post date:** [7 September 2020 16:37 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/1 "2020-09-07T16:37:09Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/2/2236f8ff1b55c719243a3e0679c456220e6dc9b3.png)

function node program

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/6/c6e9d121e81545b32c599a2354e5a017b161f394.png)

buffer parser

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/a/aaf68cea3d7b13687cf503110434359a316352c8.png)

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 September 2020 16:51 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/2 "2020-09-07T16:51:15Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![Energycharges](https://avatars.discourse-cdn.com/v4/letter/e/e47c2d/32.png) [@Energycharges](https://discourse.nodered.org/u/Energycharges)\
**Post date:** [7 September 2020 17:15 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/3 "2020-09-07T17:15:42Z")

</div>

thanks for the quick reply .

sorry , am new to node red and program

appreciate your help , if you can verify the following program

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [7 September 2020 17:17 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/4 "2020-09-07T17:17:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 September 2020 17:38 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/5 "2020-09-07T17:38:35Z")

</div>

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

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [7 September 2020 20:40 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/6 "2020-09-07T20:40:14Z")

</div>

Doesn't the query go in the topic?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 September 2020 21:36 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/7 "2020-09-07T21:36:50Z")

</div>

Doh. I forgot to edit that bit.

Edit...  
Updated the sample code.

---

<div class="post-metadata">

**Author:** ![Energycharges](https://avatars.discourse-cdn.com/v4/letter/e/e47c2d/32.png) [@Energycharges](https://discourse.nodered.org/u/Energycharges)\
**Post date:** [7 September 2020 21:44 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/8 "2020-09-07T21:44:08Z")

</div>

Thank you Steve-Mcl and Colin for your responses 😀

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 September 2020 21:53 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/9 "2020-09-07T21:53:46Z")

</div>

> [@Energycharges](#):
>
> 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"

Check the example I posted. I used ` backticks around the string. This is known as a [template literal](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals). This is what makes `${a}` turn into a variable value.

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

> [@Energycharges](#):
>
> sql = 'INSERT INTO meter (V1,V2,V3) VALUE ({a},{b},${c})'

---

<div class="post-metadata">

**Author:** ![Energycharges](https://avatars.discourse-cdn.com/v4/letter/e/e47c2d/32.png) [@Energycharges](https://discourse.nodered.org/u/Energycharges)\
**Post date:** [7 September 2020 22:29 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/11 "2020-09-07T22:29:39Z")

</div>

> [@Steve-Mcl](#):
>
> ([Template literals (Template strings) - JavaScript | MDN](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals))

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

---

<div class="post-metadata">

**Author:** ![Energycharges](https://avatars.discourse-cdn.com/v4/letter/e/e47c2d/32.png) [@Energycharges](https://discourse.nodered.org/u/Energycharges)\
**Post date:** [7 September 2020 22:35 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/12 "2020-09-07T22:35:04Z")

</div>

Thank you Steve for your timely response. 😀

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

---

<div class="post-metadata">

**Author:** ![Energycharges](https://avatars.discourse-cdn.com/v4/letter/e/e47c2d/32.png) [@Energycharges](https://discourse.nodered.org/u/Energycharges)\
**Post date:** [7 September 2020 22:39 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/13 "2020-09-07T22:39:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 September 2020 22:39 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/14 "2020-09-07T22:39:50Z")

</div>

> [@Energycharges](#):
>
> sql = 'INSERT INTO meter (V1,V2,V3) VALUES ({a},{b},${c})'

This is still wrong.

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

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

```

---

<div class="post-metadata">

**Author:** ![Energycharges](https://avatars.discourse-cdn.com/v4/letter/e/e47c2d/32.png) [@Energycharges](https://discourse.nodered.org/u/Energycharges)\
**Post date:** [8 September 2020 06:57 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/15 "2020-09-08T06:57:18Z")

</div>

Thank you Steve 😃 😃 for your timely help.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/7/476196e55e8626986a46136adf695c7c519c61d4.png)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/0/c00555c6a296aece179957b87c62eb889ad93445.png)

Thank you  
Energycharges

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [22 September 2020 06:57 UTC](https://discourse.nodered.org/t/how-to-insert-the-output-of-the-buffer-node-to-mysql-db/32557/16 "2020-09-22T06:57:18Z")

</div>

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