Is it possible to use the JDBC service to access MariaDB from a script

This has everything to do with this: Control a water heater and ground source heat pump based on cheap hours of spot priced electricity - #13 by masipila

More detailed context
As documented in the comment linked above, I got this setup fully working so that I was running openHAB (openhabian OS), influxDB and Grafana on the Raspberry Pi4.

Since this stuff is going to control the heating of our house, I was educating myself on the reliability of Raspberry. I managed to setup watchdog to automatically reboot when shit hits the fan and so on, but then I found one of the many threads about corrupted SD cards. And learnt that to lower the risk of corrupting the card (or to try to postpone the time when it eventually happens), one strategy would be to use a database hosted on another server so that the Raspberry would not have to have so many writes to the SD card.

What am I trying to achieve
I happen to have a NAS server in the same LAN so I

  • installed MariaDB on the NAS server (ready package from the NAS, influxDB was not available)
  • and created a database and user for openhab
  • installed the JDBC Persistence MariaDB add-on
  • configured jdbc.cfg and jdbc.persist
  • and observed that the normal item persistence was working as expected

So now I’m on the point where my javascript logic need to start writing points with future timestamps to the database (tomorrow’s electricity prices). As documented in the link above, using InfluxDB2 on the Raspberry, I was able to do this by using the REST API of the InfluxDB.

But what would be the most elegant way to do this with the MariaDB? Openhab definitely has the capability to connect, write and read to MariaDB via the JDBC service, but am I able to somehow call this service from my custom javascript code so that I can make my own insert, update and select queries?

Edit: Others have used the exec binding to execute a shell script that uses the command line client, but I would like to avoid installing the exec if I can.


Did you take a look at DBQuery - Bindings | openHAB ?

Thanks @glhopital ! I had not spotted that binding yet. Looks very promising, thanks for the hint!

Close, but not quite…

This binding allows creating items from the result of native database queries. It currently only supports InfluxDB 2.X.

You might be able to use the openHAB persistence REST API end point to push values with a future date to the database. Go to Developer Tools → API Explorer for more details.

Thanks Rich! That looks promising to the max…

I’m able to read from the mariaDB hosted on my NAS using the jdbc service, using GET /persistence/items

But when I’m trying to write with PUT /persistence/items/SpotPrice, I get HTTP 400 error wth a message: “Persistence service not modifiable: jdbc”

It’s exactly the same issue than here: [SOLVED] REST API: "Persistence service not modifiable: influxdb" - #16 by wjaceh

  • The original poster tried to write to influxdb hosted on another server, I’m trying to write to a remote mariadb, but that should not be relevant
  • The original poster had misconfigured the influxdb so that it did not allow connections from another server and when they fixed that, their issue got resolved
  • That’s not my issue, because my MariaDB does allow connections from others hosts in the same LAN, as can be seen from the fact that I was able to query the GET /persistence/items endpoint…

scratching my head…

Any ideas?

I don’t use JDBC databases with OH so I’ve nothing to offer. It would be odd that it only errors when posting data through the REST API but not when the Items themselves are changing since it’s all activating the same code.

Exactly, the Moon must be on the wrong side of our planet…

Meanwhile I’m “almost there” with installing InfluxDB on my Asustor NAS so that I could write to that with Influx DB’s REST API, just like I did with the Influx server on my Raspberry.

I was able to start Influx with the influxd binary, but the remaining part is to try to get the daemon to start automatically when my NAS reboots. I need to google that a bit more since my NAS doesn’t seem to have /lib/systemd/system/ where I could place influxdb2.service like I did on my Raspberry… But that’s not an OH problem, so I’ll have to figure some other mechanism for it…

Okay, I got the InfluxDB running and autostarting on my Asustor NAS so this jdbc thing will remain a mystery.

Even though this is not an Asustor NAS forum, I’ll share here what I just learnt.

  • I installed InfluxDB with these instructions Install InfluxDB | InfluxDB OSS 2.3 Documentation
  • I created a user influxdb so that I don’t have to run this as root. Also remember to create a home directory.
  • InfluxDB can be started with the influxd daemon as mentioned in the instructions above

To get influxd starting automatically when the Asustor NAS starts

Now I need to configure the influxDB to allow connections from other servers and then I’m ready to call it a day…