# How to insert in a database?

**URL:** <https://discourse.nodered.org/t/how-to-insert-in-a-database/66435>\
**Category:** General\
**Tags:** database\
**Created:** [15 August 2022 09:53 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435 "2022-08-15T09:53:54Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [19 August 2022 12:02 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/21 "2022-08-19T12:02:28Z")

</div>

i need help one more time. yesterday i have to move my database and i made a mistake with nodered i deleted it and i tried to reinstall but i have an error and i don't understand what is it.

 ![nodered](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/e/0e8d269e2ef31029de61f55e2e5e519cbe307b03.jpeg)

---

<div class="post-metadata">

**Author:** ![paulkeates](https://avatars.discourse-cdn.com/v4/letter/p/9de053/32.png) [@paulkeates](https://discourse.nodered.org/u/paulkeates)\
**Post date:** [19 August 2022 12:11 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/22 "2022-08-19T12:11:18Z")

</div>

Hi,

"(:tmp)" does not look to be a valid variable. Try without the ":".

Cheers,

Paul

---

<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:** [19 August 2022 12:11 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/23 "2022-08-19T12:11:46Z")

</div>

> [@jbudd](#):
>
> It's not so much the maths I don't grasp, it's the =\> operator

I used to struggle with the fat arrow syntax too. I just kept using it and now its second nature.

```javascript
// name func params function body
const FtoC = fahrenheit => (fahrenheit - 32) * 5 / 9;

```

expands to ...

```javascript
// name func params function body
const FtoC = (fahrenheit) => { return (fahrenheit - 32) * 5 / 9; }

```

expands to ...

```javascript
// name func params function body
const FtoC = function(fahrenheit) { return (fahrenheit - 32) * 5 / 9; }

```

---

<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:** [19 August 2022 13:07 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/24 "2022-08-19T13:07:07Z")

</div>

When you see an error that says a node does not like something, then the first thing to do is to look at what you are feeding it. Feed the output of the function into a debug node set to Output Complete Message and see what it shows in the topic.

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [19 August 2022 14:09 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/25 "2022-08-19T14:09:26Z")

</div>

Hello .i tried to find where is the msitake since a long time . in my other post yiou can find the message error on the jpg .  
maybe it s important to say: i m using a maria DB database with phpmyadmin.

Here s the error message

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 ' values (74)' at line 1

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [19 August 2022 16:26 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/26 "2022-08-19T16:26:07Z")

</div>

I really don't understand what s wrong . Values appears in the debug but i still have a trouble sql syntax.  
Some one to hel me restore this

Thanks a lot

```auto
[{"id":"02f91f35e8561da1","type":"mysql","z":"2d7932fb075f9e64","mydb":"8495c5d63ffed0ab","name":"dbdavis","x":940,"y":160,"wires":[["db6d36d99a4ee0eb"]]},{"id":"8495c5d63ffed0ab","type":"MySQLdatabase","name":"DAVIS","host":"192.168.86.78","port":"3306","db":"mymeteo_varenne","tz":"","charset":"","credentials":{}}]

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [19 August 2022 16:51 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/27 "2022-08-19T16:51:42Z")

</div>

> [@mykerinos1](#):
>
> ![nodered](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/e/0e8d269e2ef31029de61f55e2e5e519cbe307b03.jpeg)

When you look at your image and the query, then compare it to the [examples here](https://mariadb.com/kb/en/insert/#examples) does your query have anything extra near `' values (74)'` section of your query? Maybe just before.

[hint](https://m.youtube.com/watch?v=hF589_X_JJg)

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [19 August 2022 17:04 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/28 "2022-08-19T17:04:40Z")

</div>

I tried with example like that but it doesn't work . TmpExt and HumExt are name of colonn in dB

```auto
const FtoC = farenheit =>(farenheit-32)*5/9;
msg.payload.tmp= FtoC (msg.payload.data.conditions[0].temp);
msg.payload.hum= (msg.payload.data.conditions[0].hum);
msg.topic= "INSERT INTO DAVIS VALUES (tmp, "TmpExt"), (hum,"HumExt")";
return msg;

```

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [19 August 2022 17:24 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/29 "2022-08-19T17:24:15Z")

</div>

```auto
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

```

And you cannot have double quotes inside double quotes.

```auto
const FtoC = farenheit =>(farenheit-32)*5/9;
const tmp = FtoC(msg.payload.data.conditions[0].temp)
const hum = msg.payload.data.conditions[0].hum
msg.topic= `INSERT INTO DAVIS (TmpExt,HumExt) VALUES (${tmp}, ${hum});`
return msg;

```

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [19 August 2022 17:36 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/30 "2022-08-19T17:36:08Z")

</div>

Thanks for your help .i still have the same trouble.

 ![nodered](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/8/68768d88badd8f11195ebba6dc8a8ec5bf850831.jpeg)

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [19 August 2022 17:47 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/31 "2022-08-19T17:47:45Z")

</div>

Did you copy and paste the complete code block? Of just replaced some parts?

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [19 August 2022 17:50 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/32 "2022-08-19T17:50:06Z")

</div>

i tried copy all the block. i don t know how to paste on node red

mine is

```auto
const FtoC = farenheit =>(farenheit-32)*5/9;
const tmp = FtoC(msg.payload.data.conditions[0].temp)
const hum = msg.payload.data.conditions[0].hum
msg.topic= 'INSERT INTO DAVIS VALUES (TmpExt,HumExt), (${tmp}, ${hum});'
return msg;

```

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [19 August 2022 17:54 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/33 "2022-08-19T17:54:20Z")

</div>

Yes it not the same, note that i used ‘back-tick’ symbols, not single quotes:

```auto
` // backtick
' // single

```

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [19 August 2022 18:21 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/34 "2022-08-19T18:21:04Z")

</div>

it work but not completely. i have no data in database and if i want to add a const Date it doesn't work.

 ![nodered](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/b/cb9b6f6a5e1d521f4b0b97985574ab074dcf281a.jpeg)

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [19 August 2022 18:34 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/35 "2022-08-19T18:34:53Z")

</div>

> if i want to add a const Date it doesn't work.

a date is a string, you will need to enclose it in quotes:

```auto
const theDateValue = ...

msg.topic= `INSERT INTO DAVIS (TmpExt,HumExt,somedatefield) VALUES (${tmp}, ${hum},'${theDateValue}');`

```

---

<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:** [19 August 2022 21:05 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/36 "2022-08-19T21:05:13Z")

</div>

> [@mykerinos1](#):
>
> in my other post yiou can find the message error on the jpg .

It is too late now, but you misread my post. I said to add a debug node showing what is coming out of the Function node. Had you done that you would have seen exactly what you were feeding into the SQL node and would have seen that the quotes were not doing what you thought they would do.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [19 August 2022 21:33 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/37 "2022-08-19T21:33:04Z")

</div>

> [@mykerinos1](#):
>
> ```auto
> const tmp = FtoC(msg.payload.data.conditions[0].temp)
> const hum = msg.payload.data.conditions[0].hum
> msg.topic= 'INSERT INTO DAVIS VALUES (TmpExt,HumExt), (${tmp}, ${hum});'
> 
> ```

It's the end of a busy day, I hope there are not too many errors here...  
You clearly are not very comfortable with SQL syntax.

So here is an insert statement  
INSERT INTO DAVIS (TmpExt, HumExt) VALUES (23.7, 42.5);  
(assuming that the DAVIS table has two fields TmpExt and HumExt with datatype float)  
The syntax is **INSERT INTO** name\_of\_table (list, of, fields) **VALUES** (values, for, fields);

You could put that statement into msg.topic as it is  
`msg.topic = 'INSERT INTO DAVIS (TmpExt, HumExt) VALUES (23.7, 42.5)';`  
So long as there are no other NOT NULL fields which have no default value, I would expect it to insert the record successfully.

However, it is considered better to use a "prepared query" which can look like this (the format I used):

```auto
msg.payload.temperature = 23.7;
msg.payload.humidity = 42.5;
msg.topic = 'INSERT INTO DAVIS (TmpExt, HumExt) VALUES (:temperature, :humidity)';

```

Or it can look like this (the format that @bakman2 used):

```auto
const tmp = 23.7;
const hum = 42.5;
msg.topic= `INSERT INTO DAVIS (TmpExt,HumExt) VALUES (${tmp}, ${hum})';

```

I don't think it matters which form of prepared query you use but it makes sense to stick to one or the other.

Edit - I have no idea why the forum displays the msg.topic line in 3 different ways. Do I have an unmatched quote somewhere?

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [19 August 2022 21:38 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/38 "2022-08-19T21:38:52Z")

</div>

> [@Steve-Mcl](#):
>
> > [@jbudd](#):
> >
> > It's not so much the maths I don't grasp, it's the =\> operator
> 
> I used to struggle with the fat arrow syntax too. I just kept using it and now its second nature.
> 
> ```auto
> // name func params function body
> const FtoC = fahrenheit => (fahrenheit - 32) * 5 / 9;
> 
> ```
> 
> expands to ...
> 
> ```auto
> // name func params function body
> const FtoC = (fahrenheit) => { return (fahrenheit - 32) * 5 / 9; }
> 
> ```
> 
> expands to ...
> 
> ```auto
> // name func params function body
> const FtoC = function(fahrenheit) { return (fahrenheit - 32) * 5 / 9; }
> 
> ```

Thanks for that clear explanation Steve, it makes more sense now.

---

<div class="post-metadata">

**Author:** ![mykerinos1](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mykerinos1/32/46877_2.png) [@mykerinos1](https://discourse.nodered.org/u/mykerinos1)\
**Post date:** [20 August 2022 05:48 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/39 "2022-08-20T05:48:46Z")

</div>

Hello to everybody and thanks for your help..

@jbudd  
You're right i'm just starting learnins SQL .i tried all . but nothing is working. no data are stored in DB

@Colin  
if i put a debug and it work well. i find my complete payload appear and also test values from @jbudd request.

 ![nodered](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/c/bcbd9fa2f59caab7e0bcf0998eab13a23bc08bbe.jpeg)

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [20 August 2022 06:07 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/40 "2022-08-20T06:07:10Z")

</div>

> [@mykerinos1](#):
>
> i have no data in database

Are you sure about this ?  
Looking at the screenshot: affected rows: 1

can you perform the following query on the table:

`describe davis`

and paste the output here.

[Previous page](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435.md?page=1)

[Next page](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435.md?page=3)