Sqlite node examples

[acestu]

Hi,

I have installed sqlite database on my pi, also I have installed the Firefox sqlite manager add on, this allows you to create a database from your browser, what I need an example of is how I could use the sqlite node in a flow to send data to the created database when a button is pressed in say the dashboard ui, any help appreciated.

Thanks

Stuart

[Garry Hayne]

What data do you want to send?

[acestu]

Hi Gary,

I want to be able to send the temperatures from my 20 or so MQTT sensors ( cold and hot water storage ) to an sqlite database at the click of a button so that I can get a printable record, I can't figure out how the sqlite node works as the only setting in it is the database name ? and it says in the instructions that the Topic must contain the query, my MQTT in nodes have the sensor name as the Topic and the payload is the temperature, do I have to put some sort of function node in between the two nodes ?

Thanks for any help

Stuart

On Monday, May 15, 2017 at 5:55:13 PM UTC+1, acestu wrote:

[steve rickus]

I'm confused by your requirements to send sensor data to a database "at the click of a button" so you can get a "printable record"... If you really just want to click a button and print a record, you just need to append your sensor readings to a file. Then when the button is pressed, read the file and send the data to the user (or printer, or whatever).

Normally, the sensor data will be sent or polled periodically, and as soon as it's received by the flow through the mqtt in node, it would be added to the database in a background flow. Then, in another flow (maybe with a UI), when someone wants to see what has been happening, a database node is used to query the database for the needed time period, and the data is sent to a chart or file for viewing or printing. Is this more like what you are needing to do?

Is so, then the question is how to convert the msg.payload output from mqtt to the appropriate msg.topic as input to the database node. I've used the change node, function nodes, and template nodes, depending on how complex the data is, and how long the topic string will be. I have not used sqlite, so i can't tell you what the topic string should look like, but it's usually something like:

insert into tablename (col1, col2, col3, ...) values ("str1", "val2", num3)

Good luck.

[acestu]

Hi Steve,

getting my sensor data to a file is what I wanted to do but you said I needed a database in my last post, basically I have 20 mqtt sensor inputs which go to a dashboard with gauges, all I want to do is when a button is clicked all of the sensor temperatures at time of pressing the button go to a file on the desktop with their name, getting all the information to the file was proving to be too difficult.

thanks

Stuart

On Monday, May 15, 2017 at 5:55:13 PM UTC+1, acestu wrote:

[steve rickus]

Ok, so no historical data here, just the current 20 readings need to be sent to the user who pushed the button? That simplifies the flow greatly...

I think I would approach it by sending all the incoming mqtt payloads to a single "aggregator" node, either a custom function or a contrib node depending on the requirements. A while back I wrote a function that saves each msg.payload in a flow object with its msg.topic as the name of the field. So every new topic "A" payload would overwrite the previous value in this "accumulator" object.

Then a separate flow triggered by a button press would grab all the fields of the accumulator object, format them, and return them to the requestor. If this is more like what you were thinking, I will see if I can find that code... Also, check the flows library for terms like "combine", "merge", "aggr"

[acestu]

Hi Steve,

yes I checked out the aggregator nodes in my last post and I couldn't use them because you could not use the incoming temperatures as is ie you had to chose min,max, mean etc, however I have found a merge node that I have installed and would probably work with your code, I would like to try it if you don't mind.

thanks

stuart

On Monday, May 15, 2017 at 5:55:13 PM UTC+1, acestu wrote:

[steve rickus]

There is a node-red-contrib-collector node that looks like it does just what you need -- stores the topic/payload pairs in an array.

[acestu]

Hi Steve,

Thanks for the info I will check it out now, I installed the merge node and it is in my modules directory but there is no sign of it in node-red even after several restarts.

thanks

Stuart

On Monday, May 15, 2017 at 5:55:13 PM UTC+1, acestu wrote: