OpenHab can write in MySQL?

[Dim (Angelos)]

@MDieppa

How do I see the data stored in MySQL in OH?

You don’t
persistence is (mainly) a 1 way street … from OH to DB
the exceptions are very few and not very useful (especially the 2nd one with the custom exec SQL queries… it’s like bending upside down to do something that is not really needed… you can try it of course and it will work)

@MDieppa

I forgot something, I had the idea that openhab will be modified from the BD since I also plan to make a separate android application just to modify a parameter (item), I do not want to use the application that brings OH2 because I do not want to have access to all the items.

You can use the REST API in your mobile app and issue http requests to OH to obtain/set info like item states etc.

Also: access to the items depends on what you expose in your sitemap. You can list only a few that you want in a sitemap and as a result, the OH2 mobile app will display only those.

@MDieppa

For that reason I had the idea that OH2 would be modified from BD and that the arduino and android app would modify it.

unfortunately, it doesn’t work like this. OH2 does not “depend” on an external DB like MySQL.
It uses other methods for storing configs etc

[Dim (Angelos)]

@MDieppa

How would the exec scripts be?

I will give you some guidelines. I haven’t tried this but in theory, it will work.

i) Read about the exec binding: Exec - Bindings | openHAB
ii) structure a shell command/script that executes a DB query and test that it works before you put it in the exec binding
iii) the exec bound item will update it’s state based on the results of the query. You can define the interval in the exec binding config
iv) optionally, use a rule that updates the state of other items based on the result of (iii) above

It’s ugly but it should work

by the way: this scenario does not require persistence

[rlkoshak (Rich Koshak)]

@MDieppa

I will communicate by mqtt arduino and OH, and also by the persistencies, I will connect OH with MySQL, however it is not clear to me the 3 levels that Rlkoshak speaks,

Dim:

T1: MapDB (for restore)
T2: InfluxDB (for graphs with Grafana)
T3: MySQL (if needed)

Persistence has three main uses.

1: Initialize your Items to what ever state they last had when OH first boots up or you make a change to your Items. By default all Items are initialized to NULL. This will replace the NULL with the most recently saved value in the database. I recommend MapDB for this because it is embedded meaning it is very fast and MapDB only saves one value for Item meaning it doesn’t grow in size.

2: Charts and graphs require historic data. OH has built in support for charting or you can use Grafana to have more control over the creation of charts. For this you need a database that saves all the values, not just the most recent one. If you want to use Grafana you also need to use a database that Grafana supports. The most common one is InfluxDB.

3: Accessing the historic state of an Item at some point in time, or an aggregation (min, max, average) of all the states of an Item over a period of time for use in a Rule.