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