# 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:** 9\
**Page:** 3

<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 06:28 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/41 "2022-08-20T06:28:50Z")

</div>

```auto
MariaDB 10/mymeteo_varenne/DAVIS/ http://192.168.86.78/phpmyadmin/tbl_sql.php?db=mymeteo_varenne&table=DAVIS
La requête SQL a été exécutée avec succès.

DESCRIBE DAVIS

ID	int(11)	NO	PRI	
    NULL
	auto_increment	
Date	datetime	NO 0000-00-00 00:00:00		
TmpExt	float(4,1)	NO 0.0		
HumExt	float(4,1)	NO 0.0		
Vents	float(4,1)	NO 0.0		
Rafale	float(4,1)	NO 0.0		
PluJour	float(4,1)	NO 0.0		
PluMois	float(5,1)	NO 0.0		
PluAn	float(5,1)	NO 0.0		
PluInt	float(5,1)	NO 0.0		
PluLive	float(5,1)	NO 0.0		
Pression	float(5,1)	NO 0.0		

```

---

<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:52 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/42 "2022-08-20T06:52:26Z")

</div>

`TmpExt	float(4,1)	NO 0.0	`

The `NO` means that the database does not allow null (empty) values. You will need to provide all fields and values before it will store it, else it will reject the insert query. You could also modify all fields and set the `null` to `yes` and try again.

To capture these errors, add a "catch" node to the flow and and connect a debug node to it (set it to `complete msg object`). It will give the output of the query if there is anything wrong with the query.

---

<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 07:10 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/43 "2022-08-20T07:10:54Z")

</div>

it s still the same . the debug you see is the complete message. No error and data apears in the topic.

```auto
MariaDB 10/mymeteo_varenne/DAVIS/ http://192.168.86.78/phpmyadmin/tbl_sql.php?db=mymeteo_varenne&table=DAVIS
La requête SQL a été exécutée avec succès.

describe DAVIS

ID	int(11)	NO	PRI	
    NULL
	auto_increment	
Date	datetime	YES 0000-00-00 00:00:00		
TmpExt	float(4,1)	YES 0.0		
HumExt	float(4,1)	YES 0.0		
Vents	float(4,1)	YES 0.0		
Rafale	float(4,1)	YES 0.0		
PluJour	float(4,1)	YES 0.0		
PluMois	float(5,1)	YES 0.0		
PluAn	float(5,1)	YES 0.0		
PluInt	float(5,1)	YES 0.0		
PluLive	float(5,1)	YES 0.0		
Pression	float(5,1)	YES 0.0		

```

 ![nodered](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/1/71512006ca86b180225b4df0d7ce727e4fa4ca44.jpeg)  
i verify one more time that i have all privileges  
 ![node](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/8/885cde5cd9932a2a4a9e52bc59d69be8453835f3.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 07:12 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/44 "2022-08-20T07:12:47Z")

</div>

> the debug you see is the complete message.

Not from the catch node.

You can also copy the query/topic and put it in phpmyadmin's sql input, you will get the same output as in node red, but it is quicker to debug from there.

Also note that insert queries will not return the results if successful, only the output of number of affected rows.

---

<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:** [20 August 2022 08:29 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/45 "2022-08-20T08:29:18Z")

</div>

> The `NO` means that the database does not allow null (empty) values

But isn't the column full of "0.0"s the default values?  
So the query should create a record even if no value is given for these fields.

Two simple queries you can run to check:  
SELECT COUNT(\*) from DAVIS;  
SELECT \* FROM DAVIS;

To run them from Node-red use an inject node to inject that as msg.topic.  
The mysql nice will return it's answer as an array in msg.payload.

Or you can query the database with phomyadmin or the mysql command line.

---

<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 09:15 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/46 "2022-08-20T09:15:59Z")

</div>

@jbudd @bakman2

Sorry for this long time answer. I also have to repair coffee machine

So i deleted all data from database. and in phpmy admin i set that

INSERT INTO DAVIS( TmpExt,HumExt) VALUES (25,82)  
it works data are sendin db  
So I TRIED  
SELECT COUNT (\*) FROM DAVIS  
but i have also a syntax error

 ![node](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/a/2ae1170fdc0380eb846979b42450e37419ba14a4.jpeg)

SELECT \* FROM DAVIS ;  
MariaDB 10/mymeteo\_varenne/DAVIS/ [http://192.168.86.78/phpmyadmin/tbl\_sql.php?db=mymeteo\_varenne&table=DAVIS](http://192.168.86.78/phpmyadmin/tbl_sql.php?db=mymeteo_varenne&table=DAVIS)  
Affichage des lignes 0 - 2 (total de 3, traitement en 0.0564 seconde(s).)

SELECT \* FROM DAVIS  
all data set in sql query in phpmyadmin appears

```auto
                MariaDB 10/mymeteo_varenne/DAVIS/ http://192.168.86.78/phpmyadmin/tbl_sql.php? 
  db=mymeteo_varenne&table=DAVIS
                 Affichage des lignes 0 - 2 (total de 3, traitement en 0.0564 seconde(s).)
                
                SELECT * FROM DAVIS
        
        
          ID	Date	TmpExt	HumExt	Vents	Rafale	PluJour	PluMois	PluAn	PluInt	PluLive	Pression	
        461	0000-00-00 00:00:00	23.7	78.8	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	
        462	0000-00-00 00:00:00	18.0	79.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	
        463	0000-00-00 00:00:00	25.0	82.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	

```

---

<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 09:29 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/47 "2022-08-20T09:29:09Z")

</div>

@E1cid @Steve-Mcl @jbudd @bakman2 @dynamicdave @Colin

IT WORKS !!! i don't know why because i m sure that we've tried this syntax .

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

```

If i could i do a big hug to each of you !! thanks a lot !!

---

<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 09:48 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/48 "2022-08-20T09:48:14Z")

</div>

> SELECT COUNT (\*) FROM DAVIS  
> but i have also a syntax error

`count` is a function.

See:

```auto
select count (*) ...
vs
select count(*) ...

```

---

<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:** [19 October 2022 09:48 UTC](https://discourse.nodered.org/t/how-to-insert-in-a-database/66435/49 "2022-10-19T09:48:16Z")

</div>

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

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