Save multiple inputs Sqlite - undefined

I am a very beginner and have been able to bring in data via MQTT and graph it, but now I want to save to SQLite.

I am using Ubuntu on an old PC

not sure what I am doing wrong to get 'undefined'

Here is a debug from the join node output
Screenshot from 2022-07-23 16-32-30

here is the flow with the debug after the function

the debug gives me undefined for all data.
Screenshot from 2022-07-23 18-03-27

var sqliteTimeStamp = Math.round(Date.now() / 1000);
var vartemper1 = msg.payload.temperature;  
var vartemper2 = msg.payload.temperature2;   
var vartemper3 = msg.payload.temperature3;   
var vartemper4 = msg.payload.temperature4;  

msg.topic = `INSERT INTO testtable (timestamp, T1, T2, T3, T4) VALUES ('${sqliteTimeStamp}', '${vartemper1}', '${vartemper2}', '${vartemper3}', '${vartemper4}')`

return msg;

Although I would greatly appreciate working code, I really want to learn what I am doing wrong.

Hello Dave,

according to your Debug msg screenshot your temperature values are actually in

var vartemper1 = msg.payload["room/temperature"];
var vartemper2 = msg.payload["room/temperature2"];  
var vartemper3 = msg.payload["room/temperature3"];  
var vartemper4 = msg.payload["room/temperature4"];

because you payload object has a slash you need to use this alternate syntax to access it.

Node-red makes it easy to pick up the correct property path to the value by hovering over the property in the Debug window and clicking on Copy path

image


also in you sql Insert query i think you dont need the surrounding single quotes ' around the number values, if you are saving them as numbers

Thank you.

When I hover over the output in debug and copy the path.
all I get is payload

This got me over the hump. Any idea where I can read up on the various ways to define things ?

=====

Now that it appears I am sending the data out, I will try changing the

if you hover over the msg itself and Copy path .. yes .. you'll get payload
if you expand the msg and hover over a msg payload property and Copy path,
then you'll get the path of that specific property

"ways to define things" ? You can start with the Node-red youtube channel.
it covers basic introduction on getting started with Node-red and handling msgs.

you can also read this article in From the Node-red documentation
Working with messages

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

BX00Cy7yHi

You missed the slash Dave - OP will need to use square bracket notation as @UnborN pointed out...

1 Like

Thanks - I need to get my brain in gear.
I've removed my reply as I think it would confuse the situation.

1 Like

Thank you all, You have been a great help.
this did work and I got the data over to SQLite.

being a noob, I figured out that you can declare your variables first, needed that help from UnborN to get the path.
Again for the bit about opening the object to get the individual lines to get the individual path.

var sqliteTimeStamp = Math.round(Date.now() / 1000);
var vartemper1 = msg.payload["room/temperature"];  
var vartemper2 = msg.payload["room/temperature2"];   
var vartemper3 = msg.payload["room/temperature3"];   
var vartemper4 = msg.payload["room/temperature4"];

msg.topic = `INSERT INTO testtable (timestamp, T1, T2, T3, T4) VALUES ('${sqliteTimeStamp}', '${vartemper1}', '${vartemper2}', '${vartemper3}', '${vartemper4}')`

return msg;

Good stuff :+1:

Now read up on SQL injection and try to make it work with parameters instead (how to is detailed in the nodes readme page)

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