Newbie - msg.payload into SQL

General
1

Hi. I'm a newbie to NODE RED, SQL, webserver ...

I try to put all the info from msg.payload into a database.
I installed everthing and it works for me.
SQL connect. Datas are going in.

my function is:

msg.topic = "INSERT INTO dbasename(data1) VALUES ('"+msg.payload+"')";
return msg;

debug output:

INSERT INTO dbasename(data1) VALUES ('[object Object]') : msg.payload : Object

[object Object] are going into database but not the json datas
Could you please give me a advise regarding my funktion. I dont want to split the json file. I want to input the entire info.

payload is eg.

 msg.payload : string[179]
"{"time" : "2019-12-11 23:23:26", "model" : "GT-WT02", "id" : 165, "channel" : 1, "battery" : "OK", "temperature_C" : 4.000, "humidity" : 100.000, "button" : 0, "mic" : "CHECKSUM"}"
2

What are the definitions of the columns in the dbasename table?

As a self described newbie, have you taken a mySQL tutorial? Something like https://www.w3schools.com/sql/default.asp

3

Hello Zenofmud
thanks for your reply. My database info are:
10.3.18-MariaDB-0+deb10u1 - Debian 10
The dbase name ="dbasename"
there are 2 columns in it. 1. name is "timestamp" typ "timestamp"
the 2nd is named "data1" typ "text"
I administrate it with phpmyadmin

And I havnt take done this tutorial. But I watched several videos on youtube.

4

BTW I changed my function to

var obj = msg.payload;
var myJSON = JSON.stringify(obj);
msg.topic = "INSERT INTO dbasename(data1) VALUES ('"+myJSON+"')";
return msg;

Now I see the json object in my column "data1"

5

Congratulations you’ve figured it out!

1 Like
6

But is it right?
Do I have to "stringify" my object to a variable?

7

Well you can't store a json object into a text column since the data types don't match. Have a read of this and see if it helps.

8

According to the first post the payload is a string, but that cannot be correct, it must be a javascript object.
[Edit] By that I mean that if it were a string it would work, to get the error posted the payload must in fact contain an object.