How to insert in a database?

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.


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



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

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

expands to ...

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

expands to ...

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

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.

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

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


When you look at your image and the query, then compare it to the examples here does your query have anything extra near ' values (74)' section of your query? Maybe just before.


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

const FtoC = farenheit =>(farenheit-32)*5/9;
msg.payload.tmp= FtoC ([0].temp);
msg.payload.hum= ([0].hum);
msg.topic= "INSERT INTO DAVIS  VALUES (tmp, "TmpExt"), (hum,"HumExt")";
return msg;
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

And you cannot have double quotes inside double quotes.

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

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

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

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

mine is

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

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

` // backtick
'  // single

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

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:

const theDateValue = ...

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

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.

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

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

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?

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

Hello to everybody and thanks for your help..

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

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

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.