Function to add MQTT msg to mysql database.

[John O Connor]

Hi,
I am fairly familiar with Node-Red over the last while, Mysql is new to me.

This is my mqtt message;

{ "s": 8, "t": "2016-04-26T15:04:49Z", "q": 192, "c": 3, "tempint": 11.6, "vbatt": 3.29 }


I need help writing a function to insert this information into a database, and If Im not pushing it too much the basic setup of the said database!!!
I understand MySql is a complex and lengthy issue, so if anyone can point me to a good tutorial either. Information on writing functions to send to MySql from Node-Red is minimal enough across the web.

Thanks in advance for any input!!

John

[Walter Kraembring]

Hello,

Here are some of many possibilities

http://flows.nodered.org/node/node-red-node-mysql

https://www.npmjs.com/package/mysql

And this flow could be of interest as example

http://flows.nodered.org/flow/ecb37022a0f9d126a8c6

[Mike Wilson]

Hi
I have been experimenting with CouchDB for 6 months and have concluded it is easier for most things

There is a node you can download
It takes JSON directly
It can feed a website with a very simple web server (no PHP required)
Adding a new field requires minimal effort
Backup is simple

The big downside is writing queries, unlearning SQL is difficult

Just a suggestion

Mike

[John O Connor]

Thanks Walter, Great suggestions and the flow has really broke it down for me, thanks.
Is there such thing as a wild card for inserting into mysql...

ie my IOT device gives three types of messages, it would be handy have it all in the one database.
BB/0013430C978D/data : msg.payload : Object
{ "s": 5, "t": "2016-04-26T17:54:20Z", "q": 192, "c": 3, "tempint": 13.6, "vbatt": 3.29 }
BB/0013430C978D/data : msg.payload : Object
{ "s": 5, "t": "2016-04-26T17:54:20Z", "q": 192, "c": 3, "do2": false, "temp1": 9.8 }
BB/0013430C978D/data : msg.payload : Object
{ "s": 5, "t": "2016-04-26T17:54:20Z", "q": 16, "c": 3, "temp2": 2047.8 }

[John O Connor]

Looks good Mike, Will download and have a look at it. Thanks.

[John O Connor]

Mike, Im liking this couchDB... lot. Very easy use.

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/eI5pdSoV9nA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
For more options, visit https://groups.google.com/d/optout.

[Julian Knight]

CouchDB can indeed be really useful in some cases. However, SQL is generally a lot easier to use for tabular data even if it starts in JSON format. You just need to map out the JSON attributes to your columns and then it becomes simple. Some databases also have JSON helpers these days, been a while since I messed with MySQL so I'm not sure if it does.

When looking at CouchDB, remember that Couchbase and IBM's Cloudant are all part of the same family as is PouchDB which lets you create/manage CouchDB data in either pure Node.js and/or the browser. Couch's main strength comes from its synch capability so using PouchDB in the browser is a great way to create an offline capable web app.

With your data, you do have a simple tabular format even though you have 3 messages. So you could easily flatten it into a SQL database. The trick would be being able to do an insert OR update on a record unless your input always arrives in the same order.

Incidentally, since the data you are looking at is a timeseries, I would also recommend looking at InfluxDB. This is specifically designed to handle timeseries data and has a SQL-like query language. It also integrates nicely with Grafana for timeseries based dashboards. There is also Telegraf which is a great tool for capturing all manner of machine based data into InfluxDB. Primarily aimed at performance monitoring but it can be pretty much anything. Indeed, you can easily configure Telegraf to capture MQTT outputs direct to the database then display directly using Grafana. I switched to this a while back on my home hub Raspberry Pi and I've not looked back.

[Dave C-J]

just fyi - the Node-RED cloudant node  http://flows.nodered.org/node/node-red-node-cf-cloudant was recently enhanced/fixed so it now also works standalone (without Bluemix) and can access both local and remote couchdb and pouchdb instances.