Hello everyone,
I just started using openHab for a project. I managed to do all the basic configuration, make my Zwaves sensors work and establish some rules. However what I would like to do now is retreiving data from a MicrosoftSQL server. The problem is I only have a user account with ‘readonly’ capabilities on that server.
I found that the JDBC persistence module can be used to connect a DB. I installed it and I also installed sqldbc 6.2 from microsoft website but I have no clue of how to make it works.
Lets say for the example that the ip addr of my server is 192.168.1.2 and I want to access data from the table ‘test’.
At the moment here is what I have in my jdbc.cfg file (I just followed the basic template without really understanding so obviously it isn’t working ) :
url=jdbc:mssql://192.168.1.2:1433/test
user=test_user
password=test_password
Could you please help me figure out what I should put in my jdbc.cfg and/or any other configuration that I would have to do to make it works ?
Well… then this won’t work
The persistence service needs a user with write access to the DB to be able to create tables and insert data into them…
What will you retrieve with the readonly user if there is no data stored in the DB?
If you want to populate the DB manually and only perform select statements to pull specific (already stored) data, you should look into using another method (not persistence). A possible solution would be exec binding with (bash) scripts, running sql selects.
What goal are you aiming to achieve?
You want to store and/or to retrieve data from the M$SQL DB?
First of all, thank you four your answer.
Well, my test table is already filled with many data which are automatically updated from another system. I just want to read those data on a periodic basis. I do not want to write any data to the DB.
I checked the exec binding. It appears it is something like it that I need. I’ll check if a same feature exist with python instead of bash.
Persistence is for OH to write and read its own data. There is no way built into OH persistence to read data in arbitrary table spaces.
Theoretically, it might be possible to figure out how to add the right jar files in the right place so they can be accessed from Rules and then write rules that use the JDBC drivers and classes directly. But frankly I think it would be a lot less work to write an external python script or the like to periodically read the data on the DB and push the data into OH through MQT, the REST API, or Exec binding.
Thank you for your answer.
I found out how to execute python scripts with the exec binding so everything should be fine now. Just need to write the full script now .
) :
.