Two MQTT IN to SQLITE

[jjcfrank]

Hello,

I have never used Node-Red before so I am still trying to figure it out.

I have two MQTT IN, one is temperature and the other one is humidity. I want to separately have them in the sqlite database. The idea is to have a table that has the next columns: date, temperature, humidity.

I have read that the best option is to use "join" in Node Red, but I do not know how can I now separate them, they seem to live together.

Thanks

[zenofmud]

Welcome to the forum!

Where is the data coming from? Do you have the ability to return both temperature and humidity in one mqtt message? If you do, that will make it much simpler to deal with.

[jjcfrank]

Hey thanks for the help. The readings come from two different MQTT channels but they could technically be joined in the ESP32.

[E1cid]

If you want the values separate then I don't see a need for a join node.
use the mqtt topic to decide if the value is a temp or humidity.
then construct the sql query accordingly.
here is a simple example. the injects simulate the mqtt in nodes.

[{"id":"5bacd77c.686808","type":"inject","z":"c74669a0.6a34f8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"sensor/temperature","payload":"23","payloadType":"num","x":140,"y":4540,"wires":[["23a6325e.79c7a6"]]},{"id":"23a6325e.79c7a6","type":"function","z":"c74669a0.6a34f8","name":"","func":"let column = msg.topic.split(\"/\")[1];\nmsg.topic = `INSERT INTO table_name (date, ${column}) VALUES (${new Date().valueOf()}, ${msg.payload});`; \nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":380,"y":4560,"wires":[["7372c9de.20ba08"]]},{"id":"47833c.f39edcc4","type":"inject","z":"c74669a0.6a34f8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"sensor/humidity","payload":"65","payloadType":"num","x":150,"y":4580,"wires":[["23a6325e.79c7a6"]]},{"id":"7372c9de.20ba08","type":"debug","z":"c74669a0.6a34f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":590,"y":4560,"wires":[]}]

[jjcfrank]

Ok. This is great. So I have been following a tutorial but the tutorial I was following only has one reading (temperature). In my case I need two, as I mentioned before. This tutorial goes from "MQTT in" to "FUNCTION" to "SQLITE".

The code below is in the FUNCTION:

var sqliteTimeStamp = Math.round(Date.now() / 1000); //converting to seconds instead of milliseconds epoch

var theSQL = "INSERT INTO lounge_sensor (timestamp, temp, humi) VALUES "
theSQL = theSQL + "('" + sqliteTimeStamp + "', '" + msg.payload +"', '" + HERE PAYLOAD TWO? + ");";

msg.topic = theSQL;

How can I insert payload two? Apologies as I am not very knowledgeable.

[Steve-Mcl]

To insert temp & humi into the same row you need both values in the same msg.

A key point to remember is - it is IMPOSSIBLE for 2 msg to reach a node at the same time.

Therefore you would use a join node (just before the function node) - set it to 2 parts and key/value mode.

See this article in the cookbook for an example of how to join messages into one object.

[jjcfrank]

Yes. That totally makes sense. Thanks Steve!

I do have a question, whether is in the function or the join... how could I get temp and humi individually? When I use join and then a debug (for testing) they seem to come in a JSON?

Apologies, I am a python guy and not well versed in JS.

[zenofmud]

There’s a great page in the docs 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.

https://nodered.org/docs/user-guide/messages

[Steve-Mcl]

@jjcfrank

When I use join and then a debug (for testing) they seem to come in a JSON

They should arrive as an object (JSON is a string representation of a js object)

Use the copy path feature @zenofmud mentioned above.

Note: the function node requires you to prefix the copied path with msg. e.g. msg.payload.humi

[jjcfrank]

I had a good look at that article and managed to put something together... but I am getting an error now.

First, I got the two MQTTs to a JOIN and then into a DEBUG to see the path (as mentioned by zenofmud). I then modified the flow like in the picture with MQTTs to a JOIN to a FUNCTION to a DEBUG... but I get an error (see the first picture).

BTW, this is what my JOIN looks like:

[Steve-Mcl]

So you get the value from the payload then assign it to msg (making msg a string or number or whatever it is) - you cannot simply return a value - you must return an object (usually the msg)

e.g...

var sqliteTimeStamp = Math.round(Date.now() / 1000);
var humi = msg.payload.humi;   // << or whatever the copy path gives you
var temp = msg.payload.temp;   // << or whatever the copy path gives you

msg.topic = `INSERT INTO lounge_sensor (timestamp, temp, humi) VALUES ('${sqliteTimeStamp}', '${humi}', '${temp}')`
return msg;

[jjcfrank]

That worked perfectly well Steve. Thanks!

Just one last thing (sorry!), I get this message: "Error: SQLITE_READONLY: attempt to write a readonly database".

I set up a /databases in the root of the Rpi and then created the table called "lounge_sensors". On SQLITE can see on the side of the table ("rw"). I am not sure what it says there is a readonly issue. I also CHMOD +777 the /databases.

The only thing I find strange is that after creating a database named "iot.db" and table "lounge_sensors", I do not see any files inside /databases (currently the table is empty).

Does this make sense?

[zenofmud]

What does the configuration of the sqlite node look like?
What device is NR running on and is sqlite installed on the same device?

[jjcfrank]

Here it is:

[jjcfrank]

Also, here's the SQLITE:

[zenofmud]

Can you add data in via phpLiteAdmin?

What device is NR installed on and is sqlite on the same device.

also note that the database path needs to be the full path to the database. For example on my mac I have /Users/Paul/databases/inventory

[jjcfrank]

Can you add data in via phpLiteAdmin?

I tried inserting a row directly from phpLiteAdmin and works fine.

What device is NR installed on and is sqlite on the same device.

NR is installed in the Rpi so is SQLITE. NR is exactly installed in "/var/www/html/" and SQLITE is in "var/www/html/database".

also note that the database path needs to be the full path to the database. For example on my mac I have /Users/Paul/databases/inventory

It's installed in root of Rpi "/databases". I have edited the phpliteadmin.config.php and set it to "/databases"

I am just trying to think - what am I missing?

[zenofmud]

@jjcfrank

var/www/html/database

You have databases/iot.db (note the plural) in the sqlite configuration.
try using var/www/html/database/iot.db in the database option of Properties.

[jjcfrank]

It's solved now. Before your message I ended up doing quite a bit of things and now it's working. The problem is that I am not sure what I did to fix it.

I did do a chmod 777 to the actual iot.db... I am wondering if that the issue?

[zenofmud]

On the RPI, the sqlite DB is opened in the .node-red folder if you just give the db name. If you want it in another location you need to specify the path.

For example, if I want the database to reside in a folder called 'databases' that sits in the pi's home folder, in the database option I would specify /home/pi/databases/iot.db that way the user 'pi' can access it.

Since 'root' owns the 'var' folder the 'pi' user woldn't have had access to it. When you changed the ownership to 777 you said anyone can access this file and so user 'pi' could so node-red can.