mySQL Connector / Passing data to another server

Hardware emonPi
1

I just started with openenergymonitor - especially because of the RFM69pi Modul… I use it for receiving data from my own RFM12B temperature sensors. At the moment I use the pre-built emonSD-03May16 Image - and it works great. I can see the Nodes and the temperature.

But I don’t want to use the local emoncms or emoncms.org services. I would like to forward the values to a local mysql database on a different server…

So I think about writing a connector which sends the data to the database.

Unfortunately I have no idea which (PHP) script is processing new sensor values… I think somewhere must be a function which is processing the new values. Then I could modify this code and do whatever I want…

Does anyone have an idea in which script / function I can add my own code?
e.g Passing the values to another URL (http://my-server/import.php?node=$node_id&value1=$value1… ) or something like that…

Or is there an easier way? Thanks for any ideas…

2

The easiest way IMHO is to use nodeRED which is built into the emonSD-03May16 SD card image to read the data from MQTT on the emonPi / emonBase to post to another service. You can perform a HTTP request to post data to another service.

NodeRED is running on port 1880, see emonPi nodeRED documentation page:

1 Like
3

Hi Glyn,

thank you for this information - it partially works but I think it’s not exactly what I’m looking for.

I created the following nodes (see image) and I can access this one value in my test.php script (and write it in a database or whatever).

Is it possible to work with multiple values (emon/41/1 [temperature] & emon/41/2 [humidity] & emon/41/3 [battery] & emon/41/rssi) in ONE request?

I cant belive that writing the values in a mySQL database is so difficult :wink: I already think about parsing emonhub.log - that’s easier…

4

In my mind emonhub is the more logical place to do what you want but I’m not fan of parsing the log file. It has been done before though and a direct SQL connector has been also been discussed. The better way would be to write a custom SQL reporter for emonhub, a reporter would buffer undelivered data during server/network interruptions etc.

Take a look at emonhub_reporters.py and the emoncms reporter in particular to see how it could be quite easily duplicated with changes to suit your own api or if you are more familiar with using remote SQL connections than I am (which isn’t at all difficult) we could look at connecting directly to the remote mySQL server.

The reporters are not found in the “emonPi variant” of emonHub on the emonSD image, so the buffering isn’t available, I would firmly recommend using the original version when posting remotely and you will also find it easier to develop for.

5

Hi Paul,

thank you so much - that was a great idea.

After solving a few problems with a clean Raspian Jessie (SysRQ / Crash because of cmdline settings) and struggling with the documentation I found a solution.

“Talking” with the RFM69Pi is much more easier than I tought. And emonhub.py is doing the rest.

At the moment I can access the values in emonhub.py / emonhub_reporter.py and post them to a PHP-Script / URL. I can access the values in the PHP Script - so insertig them in a mySQL Database is no problem.

Perhaps I will try to connect to mySQL directly from emonhub_reporter.py later - but at the moment I’m happy with this “quick-and-dirty” solution :wink:

1 Like
6

Subscribing to emon/# mqtt topic will give you all the data. However it sounds like you have figured a solution.

7

I tried it with # later too. But then I had 3 requests to test.php per node… (One request for temperature, one request for humidity and so on…)

The solution with emonhub.py is much easier to maintain. And fewer sources of error - I need only one script…

8

hi
I’m trying do the same , I ready install emoncms
http://cyrus.delphsoft.com/emoncms/

but I should use that conector emonhub_reporters.py ?

or made changes on
[[emoncmsorg]]
Type = EmonHubEmoncmsHTTPInterfacer
[[[init_settings]]]
[[[runtimesettings]]]
pubchannels = ToRFM12,
subchannels = ToEmonCMS,
url = https://emoncms.org
apikey =
senddata = 1 # Enable sending data to Emoncms.org
sendstatus = 1 # Enable sending WAN IP to Emoncms.org MyIP > Emoncms - myip list
sendinterval= 30 # Bulk send interval to Emoncms.org in seconds