Node Red writing into SqLite Database

[Ken Kaktus]

Hello All,

i'm experimentalise with Node Red for a few days.

Now i try to write Data from a DHT21 Sensor into a Database.

I stuck with the right syntax.

With a inject node i created a new Table like this:

CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)

Now i want do insert readings into this Table with a function node.

I Tried this with fixed values without success:

msg.topic = "INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) VALUES (22.4, 48, date('now'), time('now'), manual)"; msg.payload = [temperature, humidity, currentdate, currenttime, device];

 return msg;

This seems to have the wrong syntax.

Any idea?

Wow can i archieve to write real readings of the DHT21 Sensor into the Database?

Is it possible to write both values in one step or do i have to handle them seperatly?

Thanks a lot

Thomas

[Zenofmud]

I believe your data for the device needs to be surrounded by double quotes since it is a text field i.e.

On Jul 14, 2017, at 4:29 AM, Ken Kaktus <perg...@gmail.com> wrote:

INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) VALUES (22.4, 48, date('now'), time('now'), manual)

should be:

INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) VALUES (22.4, 48, date('now'), time('now'), “manual")

[Colin Law]

On 14 July 2017 at 09:29, Ken Kaktus <perg...@gmail.com> wrote:
> ...

> msg.topic = "INSERT INTO dhtreadings(temperature, humidity, currentdate,
> currenttime, device) VALUES (22.4, 48, date('now'), time('now'), manual)";
> msg.payload = [temperature, humidity, currentdate, currenttime, device];

In addition to Zenofmud's comment are the values you are putting into
the payload array variables that you have already setup? So is
temperature a js variable that you have set to the current
temperature? Since you are setting the values in the query itself in
the topic I would not have thought you need the array in the payload
anyway, though I am not very familiar with the sqlite node.

Also when you get an error it is always a good idea to paste the
message here (if there is one).

Colin

[David Caparrós]

Jus in case if helps you:

INSERT INTO temperature (date,temperature,humidity,look,detail)

VALUES ({{date}},{{payload.tempc}},{{payload.humidity}},"{{payload.weather}}","{{payload.detail}}"); 

where:  

table name is temperature

variables names on table: date,temperature,humidity,look,detail

msg.topics are tempc.humidity,weather,detail

try to set yours on same sintax and will work.

Regards