How to insert in a database?

General
41 
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
42

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.

43

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

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
nodered1721×408 98.1 KB

i verify one more time that i have all privileges
node
node775×31 9.33 KB

44

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.

45

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.

46

@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
node463×593 40.5 KB

SELECT * FROM DAVIS ;
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
all data set in sql query in phpmyadmin appears

                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
47

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

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

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 !!

48

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

count is a function.

See:

select count (*) ...
vs
select count(*) ...
49

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