Openhab2 JDBC Persistence SQLite

All,
I am struggling with getting SQLite to work.
Currently rr4dj is running successfully for numbers and I would like to use sqlite for other item types.

Raspberry Pi running on openhabian

What I did: Instalation of SQLite Addon through Paper UI
jdbc.cfg:
url=jdbc:sqlite:/var/lib/openhab2/persistence/jdbc-sqlite.db

created jdbc.persist for the group items which should go into this db.

But I get:
[persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: no driver available!

and
[jdbc.internal.JdbcPersistenceService] - JDBC::store: No connection to database.

Any idea?

As far as I understood form other discussions, “driver” means the binding (in this case: persistence-jdbc-sqlite - 1.9.0.SNAPSHOT).

Additionally there is no db generated in /var/lib/openhab2/persistence
(where the other persistence folders can be found)

Does no one have this problem or did I miss any obvious thing?
Any suggestion is greatly appreciated.

the same applies to H2.
So does anybody have a hint for me, where I might have missed something?

There must be something obvious (not for me though) to address this error:
2016-11-11 18:12:36.784 [WARN ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: no driver available!

Is SQLite running on your RPI? I have mySQL running well - but in addition to setting it up in OH2, you need to install and configure it. See http://raspberrywebserver.com/sql-databases/set-up-an-sqlite-database-on-a-raspberry-pi.html for SQLite install.

Best of luck

Hi @WayneStronach,

thanks for your response.
I have checked the website you mentioned.
sqlite3 is installed on my raspi and I generated a db.

This time it seems to work:
JDBC::openConnection: Driver is available::Yank setupDataSource

However, the documentation in jdbc.cfg mentiones:
If no database is available it will be created, for example the url 'jdbc:h2:./testH2' creates a new DB in OpenHab Folder.

So it’s strange, but it’s fixed somehow.
Thanks a lot for your help!!!

too early happy…

Still have issues in the log:

2016-11-11 19:54:41.295 [ERROR] [org.openhab.persistence.jdbc        ] - [org.openhab.persistence.jdbc(234)] The activate method has thrown an exception
com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: org.sqlite.core.NativeDB._open(Ljava/lang/String;I)V

additionally the db I previously tried to generate through the console does not exist…

I also ran in to that exact error when attempting to enable SQLite Persistence with Openhab2. The issue seems to be a bug in the sqlite_jdbc java library that is included with Openhab2. Specifically version 3.8.11.2. I tried to figure out how to update the version included but that version seems to be hard coded somewhere that I was unable to find.

As a workaround, however, I downloaded a new version of the library from https://bitbucket.org/xerial/sqlite-jdbc/downloads then copied it overtop of /usr/share/openhab2/runtime/system/org/xerial/sqlite-jdbc/3.8.11.2/sqlite-jdbc-3.8.11.2.jar

It is only a workaround because the version numbers are all wrong but it does allow the SQLite persistence to start up without error. I haven’t done much with it as I am still figuring things out so your mileage may vary.

Thanks for the suggestion.
I started from scratch (regarding the jcdb) after an update / upgrade a few days ago.
After adding H2 (instead of sqlite) in Paper UI and changing the config files: addons.cfg, jcdb.cfg and jcdb.persist it works like a charm.
However, I don’t know, what I did differently (beside an upgrade) to the last time and thats a little frustrating.

Same issue on my Synology with “OpenHAB-2.0.0.008-SNAPSHOT-DSM6-syno-noarch-0.001.spk”:
2017-01-09 14:11:31.809 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2017-01-09 14:11:31.828 [ERROR] [org.openhab.persistence.jdbc ] - [org.openhab.persistence.jdbc(182)] The activate method has thrown an exception
com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: org.sqlite.core.NativeDB._open(Ljava/lang/String;I)V

Any ideas? Will try the procedure of WRXIzumi

edit: doesn’t work for me. Will try another persistance.

Hi all,
after day of research i found that sqlite-jdbc version 3.8.11.2 works only in certain systems/processors. There is something wrong in compiled native library which resides inside of bundle. Bundle itself is dynamically downloaded on install and restart?

However, I’ve tried to change version number to latest atm 3.16.1 and everything worked fine. Latest version has definitely better support for various systems/processors. So it is a fix for upcoming version 2.0.

Library version can be changed in /usr/share/openhab2/runtime/system/org/openhab/distro/openhab-aggregate-xml/2.0.0.b5/openhab-aggregate-xml-2.0.0.b5-features.xml line 1569.

Hope that helps

1 Like