OpenHab can write in MySQL?

Tags: #<Tag:0x00007f6173270408> #<Tag:0x00007f6173270318>

Hi
I currently have MySQL persistence installed, I can see my items as columns in my database but I still do not know how I can fill those columns with the information I can collect from openhab. (for example, an off or an on of a switch)

Similarly I do not know how to read the data I have in a table already created

I need help to use my database in Openhab2

persistence service will do that filling up for you. there is no need to manually enter data in the DB.

to see the stored data you can use several tools (e.g. phpMyAdmin)
You can also use graphing tools (e.g. Grafana) to display the data series over time

what do you want to achieve?

Hi, thanks for the quick response.

Currently I have a MySQL server with the Xamp application, I have already my tables configured with phpMyAdmin, these tables are filled by means of an arduino via serial with a program in java that I program myself, this same program fills the tables of my base data, I want to be able to modify the database through openhab and also be able to see the data that is being saved.

But I have no idea how it is done, I have only managed to connect with my MySQL server and create tables in it with the same items that I create in Paper UI mode.

I suggest another approach…
Use the arduino so send the data to openHAB. You can use several means of transferring the data but I recommend MQTT.
Then use the persistence service in openHAB to save the data in mySQL.
You won’t need to manage the table, OH will do that for you.

Just to provide a little more background.

OH has very limited access to databases.

  • It can create table spaces/columns for Items defined in the .persist file and it will save the states of Items to those table spaces/columns based on the criteria (update, command, change, periodically) defined in the .persist file.
  • It can restore the state of an Item with the most recently saved value in the database
  • From Rules you can get the previousState, maximumSince, minimumSince, averageSince, lastUpdate date/time, and the state of an Item at a given time.

That’s it. You can’t query arbitrary data from a database. You can’t really even connect to a database except through the Persistence API.

So in answer to the question of how to get OH to save data, you create an Item, configure the Item to be stored by some criteria in the .persist file and when ever that Item gets an event that matches the criteria it’s state will be saved to the database.

In answer to the question of how to get OH to query your defined database the answer is you can’t. The typical approach is described by Vincent, have you Arduino publish the data to OH. It should not be difficult to add to the code you’ve already written to publish the data using MQTT or interacting with OH’s REST API directly.

Thanks for responding, I understand that OpenHab can save the data in MySql but can not consult them, therefore, in order to see in real time the data in openhab the arduino must send the most updated variable.

but then I ask myself the question, how can I make graphics and see them in openhab if this one can not consult the database?

It isn’t that OH can’t consult the database. It is that you have only limited access to the database. OH can only access an chart information that is associated with an Item and only in certain ways. If you want to graph or chart things that are not saved by OH then you must use an external tool to create and display the charts like Grafana.

Perfect and I begin to understand better, then for my case, I will manipulate the table generated by the openhab with the creation of an item, and in this way, the Openhab can see the changes that have been made.

Now I’m in the configuration part of the Mysql persistence, Try previously with MySQL Persistence https://www.openhab.org/addons/persistence/mysql/

But I did not know how to configure it or not, so now I use the JDBC Persistence (https://www.openhab.org/addons/persistence/jdbc/#table-of-contents) with the mysql section and yes it generates the table of the items in my mysql. I just need to learn to use it

How do I manipulate the items and have them save me data in real time?
For example, create two items called “Interruptorprueba” and “Interruptorprueba2” are a switchs

my document jdbc.persist is

Strategies {
    default = everyChange
}

Items {
    * : strategy = everyChange, restoreOnStartup
     InterruptorPrueba : strategy = everyChange, restoreOnStartup
    InterruptorPrueba2 : strategy = everyChange, restoreOnStartup
}

I do not know how to make the rules and the view to manipulate the switches, keep the database and also consult the table to see their behavior.
.

OH will not interrogate the DB periodically when using a persistence service.
It will write every Item state update but will only read ONCE (when the system starts up and only when using restoreOnStartup strategy)

So, if you make a change in the data contained within the tables of the DB, OH will NOT read it.
There are 2 exceptions to the above:
i) when using a rule and requesting historical data (e.g. averageSince, etc)
ii) when using custom exec scripts and issuing an SQL Query that can be used to update an Item state

This is done by default by the persistence service. Depending on your strategy (e.g. everyChange), OH will store data in the DB.

what exactly are you trying to achieve?
give us an example use case and we can help.

You should fix your jdbc.persist
You have all (*) being persisted and you don’t need to list the 2 individual items
You can also put them in a group and persist the Group (members)

Exactly I have an arduino that is filling a table in MySql (PhpMyAdmin), my intention is that the openhab can visualize the most recent data and also have the possibility of changing the data in the same table.

Example:
with the arduino I can manipulate a virtual switch, and also with the openhab, and at the same time that data is saved to a database.

You should fix your jdbc.persist
You have all (*) being persisted and you don’t need to list the 2 individual items
You can also put them in a group and persist the Group (members)

How would the .Persist file then be?

Change that to have the arduino change the item in openHAB via the REST api or MQTT. openHAB will then take care of the DB for you and all the rest

1 Like

put the 2 individual items (InterruptorPrueba…) in a group called gMysql and then:

Strategies	{ default = everyChange }
Items		{ gMysql* : strategy = everyChange, restoreOnStartup }

Then, any other Item that you add to this Group will have its state persisted also.

you can’t do this with persistence. this service is (primarily) for storing data, not querying data.

shift your deployment as Vincent recommends. it’s the best way. Arduino -> MQTT -> OH2 -> MySQL

by the way, I wouldn’t do restoreOnStartup with MySQL… it could be a bit slow
use a dual persistence setup and restore from a MapDB

read more on multi-tier persistence setups here: Good Tutorial On Persistence (RRD4J and JDBC-MYSQL)? , courtesy of @rlkoshak

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

Okay, then I change the focus of the project.

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,

my idea is to be able to see the data table saved by OH in it, but you They say they can not read constantly, only when they start.

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

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.

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

Do you have any idea about this?

Thank you very much for reply, they have helped me a lot :slight_smile:

I am pretty sure that my spanish is not that good. IT’s actually a bit rusty…
Could you change your post with the translations and/or original quotes, por favor. Gracias

This is what I quote
Sorry Vicent

I know you did. Please change the post above into English. Thanks

Excuse me for translations, it’s ready

For the case of the Android app I think the best is the second exception that Dim says…

How would the exec scripts be?

But why do you want to access the DB from OH. For what purpose?