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 
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?
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)
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.
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
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
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.