Accessing an external database for persistance

Tutorials & Examples
, ,
1

Hi,

Does anyone know of a way to write to and read from an external database from within a rule.

What i want to achieve is to be able to write to a database record using an external source and then have OH read this database record to set certain global variables in OH.

I was hoping to use MySQL but am not sure where to start.

Many Thanks

2

Technically, you can run any binary or script from a rule with executeCommandLine(). I don’t think you can grab the return value from the script in the rule, but you can let the script update an via the REST API or other means.

3

Well i was hoping to use an externally hosted database rather than a local one. all the example i have found are locally based databases.

4

Given your stated problem:

I would do the following.

  • Set up your global variables as Items
  • Create a .persist file for MySQL and put those global Items in that file. Make sure to install the mysql binding jar and configure it in openhab.cfg. You can point at a MySQL server hosted anywhere that your openHAB server can see including on some cloud server or the like. It doesn’t have to be local.
  • Also set the restoreOnStartup strategy on those Items so your DB values get reloaded on a restart
  • Make sure these items are excluded from your default persistence config or at a minimum make sure they are not part of the restoreOnStartup in your default persistence so your local values don’t override your remote MySQL values
  • If you don’t want to mess with the restoreOnStartup strategy you can have a system started rule that repopulates the Items with a call like this: myItem.postUpdate(myItem.previousState("mysql")) which will pull the previous state from your mysql persist rather than your default persist.
  • You will have to bootstrap the values somehow. I would just log into the MySQL and manually set the values.

This approach lets you take advantage of the facilities already built into openHAB.

Otherwise you will need to do as @pelnet suggests and call an external script, or go through the pain of trying to figure out how to do JDBC from with Rules, assuming it is even possible (i.e. you can get access to the right libraries, I’m not sure the extent of the classpath available in Rules).

If you use executeCommandLine(cmd,timeout) it will return the result from the script as a String.

1 Like
5

Maybe I’m being a little dense here, but once you’ve abstracted the process - there is no difference between an external or a local database from OpenHAB’s point of view?

@rlkoshak: Thanks for refreshing my memory - I just twigged that I’m actually using that functionality right now… :pensive:

6

For the MySQL persistence binding you can configure it to point at any MySQL server running on any computer your openHAB server can get to on the network. You provide the name or IP address of the MySQL server in the mysql:url parameter in openhab.cfg.