JDBC Persistence Service missing in 1.7

I wanted to setup JDBC with SQLite but the persistence binding seems to be missing in the addons download. Where can I download it or is there an alternative to use a SQLite database?

Should be in the latest successfully built Snapshot (1081)

Oh nice! Downloading now and will take it for a spin.
Any eta for 1.8 release?

afaik near end of december '15

So, I tried it out and it looks ok but it doesn’t write anything to the database. There are two warnings in the log but they don’t tell me anything what might be wrong. It also doesn’t automatically create a database if there isn’t anyone there. Do I need to create the SQLite database with a certain schema?

Config:

jdbc:url=jdbc:sqlite:./database/openhab.db
jdbc:user=myuser
jdbc:password=mypassword

Logfile:

22:26:41.189 [DEBUG] [o.p.j.i.JdbcPersistenceService:90   ] - JDBC::setItemRegistry
22:26:41.192 [DEBUG] [o.p.j.i.JdbcPersistenceService:60   ] - JDBC::activate: persistence service activated
22:26:41.194 [DEBUG] [o.p.j.i.JdbcPersistenceService:214  ] - JDBC::updateConfig
22:26:41.196 [DEBUG] [p.j.internal.JdbcConfiguration:64   ] - JDBC::JdbcConfiguration
22:26:41.199 [DEBUG] [p.j.internal.JdbcConfiguration:72   ] - JDBC::updateConfig: configuration.size = 8
22:26:41.202 [DEBUG] [p.j.internal.JdbcConfiguration:77   ] - JDBC::updateConfig: url=jdbc:sqlite:./database/openhab.db
22:26:41.204 [DEBUG] [p.j.internal.JdbcConfiguration:87   ] - JDBC::updateConfig: found serviceName = 'sqlite'
22:26:41.206 [DEBUG] [p.j.internal.JdbcConfiguration:98   ] - JDBC::updateConfig: Init Data Access Object Class: 'org.openhab.persistence.jdbc.db.JdbcSqliteDAO'
22:26:41.208 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:101  ] - JDBC::initSqlTypes: Initialize the type array
22:26:41.210 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:74   ] - JDBC::initSqlQueries: 'JdbcSqliteDAO'
22:26:41.213 [DEBUG] [o.o.p.jdbc.db.JdbcSqliteDAO   :42   ] - JDBC::initSqlQueries: 'JdbcSqliteDAO'
22:26:41.215 [DEBUG] [p.j.internal.JdbcConfiguration:105  ] - JDBC::updateConfig: dBDAO ClassName=org.openhab.persistence.jdbc.db.JdbcSqliteDAO
22:26:41.217 [DEBUG] [p.j.internal.JdbcConfiguration:144  ] - JDBC::updateConfig:  user=homebase
22:26:41.219 [DEBUG] [p.j.internal.JdbcConfiguration:157  ] - JDBC::updateConfig:  password=<masked> password.length=6
22:26:41.223 [DEBUG] [p.j.internal.JdbcConfiguration:181  ] - JDBC::updateConfig: tableUseRealItemNames=true
22:26:41.225 [DEBUG] [p.j.internal.JdbcConfiguration:215  ] - JDBC::updateConfig: enableLogTime false
22:26:41.227 [WARN ] [p.j.internal.JdbcConfiguration:236  ] - JDBC::updateConfig: try to load JDBC-driverClass: 'org.sqlite.JDBC'
22:26:41.229 [DEBUG] [p.j.internal.JdbcConfiguration:239  ] - JDBC::updateConfig: load JDBC-driverClass was successful: 'org.sqlite.JDBC'
22:26:41.231 [DEBUG] [p.j.internal.JdbcConfiguration:265  ] - JDBC::updateConfig: configuration complete. service=jdbc
22:26:41.233 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:33   ] - JDBC::pingDB
22:26:41.235 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:153  ] - JDBC::openConnection isDriverAvailable: true
22:26:41.237 [WARN ] [o.o.p.jdbc.internal.JdbcMapper:155  ] - JDBC::openConnection: setupDataSource.

@lewie, why are those messages WARN?

@gerryK,

please use this Driver (copy into your [openHAB Main folder]/addons folder):
http://mvnrepository.com/artifact/org.xerial/sqlite-jdbc/3.8.11.2 (older Versions caused problems to load as OSGI-Bundle)

You do not need to create the SQLite database and Schema!
Does the folder [openHAB Main folder]/database exist?

Tested this setup, and it works as expected yet.

@watou, You’re right, should be INFO!

@gerryK,
eventually, only one connection can be opened to sqlite, at a time.
If you have opened a second connection for observe if openHB writes data, this connection must be read only.
Otherwise this can cause problems.

I would suggest that those and many other messages in the JDBC persistence bundle ought to be DEBUG. In the normal running of openHAB, users would not expect to be interested in so many log messages.

I personally think “JDBC::store: ignore Item because it is UnDefType”, table creation or if a driver is loaded right are relevant Informations at runtime/startup.
But debug makes eventually sense here.
The need to finally decide you as maintainer.

Log messages that could potentially repeat would be the biggest offenders if they were logged at INFO or higher level. Table creation and loading a driver should be logged as WARN (or ERROR if system stability is affected) but only if they fail. Perhaps other intermediate steps that could help troubleshooting could be logged as DEBUG or TRACE as you see fit.

I like how the MySQL persistence bundle has very little to log, unless there’s a problem or I’m trying to troubleshoot an issue with it.

I used the driver as you suggested already because jdbc persistence was complaining about the version. The database folder does also exsist in my openhab directory. Specified user has also rw access rights to folder and file. I also would assume that the log would tell me if either of those settings were wrong. Does the binding report if it creates a new database if it doesn’t exist?
Would I see a log entry if it wrote to the db?

I am running openhab on raspbian jessey with Java 8 and SQLite3 btw. At the moment I manually start openhab with sudo so access rights should be fine.

22:26:41.233 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:33   ] - JDBC::pingDB

Then database is created/connected. Read works then. For writing in DB, see next paragraphs.

Later when an Item should be written how defined in ./configurations/persistence/jdbc.persist like

Items {    *     : strategy = everyChange, restoreOnStartup }

You should see debug messages like:

21:40:40.927 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:120  ] - JDBC::storeItemValue: Item=Mitte_Temperature (Type=NumberItem, State=23.12)
21:40:40.927 [DEBUG] [.persistence.jdbc.model.ItemVO:38   ] - JDBC:ItemVO tableName=mitte_temperature_0023; newTableName=null; 
21:40:40.927 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:478  ] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item Mitte_Temperature
21:40:40.927 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:341  ] - JDBC::storeItemValueProvider: item 'Mitte_Temperature' as Type 'NUMBERITEM' in 'mitte_temperature_0023' with state '23.12'
21:40:40.928 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:348  ] - JDBC::storeItemValueProvider: getState: '23.12'
21:40:40.928 [DEBUG] [.persistence.jdbc.model.ItemVO:47   ] - JDBC:ItemVO setValueTypes dbType=DOUBLE PRECISION; javaType=class java.lang.Double;
21:40:40.928 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:355  ] - JDBC::storeItemValueProvider: newVal.doubleValue: '23.12'
21:40:40.928 [DEBUG] [.o.p.jdbc.db.JdbcPostgresqlDAO:146  ] - JDBC::doStoreItemValue sql=INSERT INTO mitte_temperature_0023 (TIME, VALUE) VALUES( NOW(), CAST( ? as DOUBLE PRECISION) ) value='23.12'
21:40:40.933 [DEBUG] [o.p.j.i.JdbcPersistenceService:228  ] - JDBC: Stored item 'Mitte_Temperature' as '23.12' in SQL database at Mon Dec 14 21:40:40 CET 2015 in 6ms.

Every read/write is documented in debug mode.
If something goes wrong you get a mass of error messages!

“jessey with Java 8” my preferred environment, raspbian I don’t use yet.

Hope this helps.

@watou, I am absolutely in your opinion!

I would move to debug:

22:26:41.227 [WARN ] [p.j.internal.JdbcConfiguration:236  ] - JDBC::updateConfig: try to load JDBC-driverClass: 'org.sqlite.JDBC'

I would move to INFO or let it stay in DEBUG: INFO, because it is only on startup and it shows, that the right driver could be loaded.

22:26:41.229 [DEBUG] [p.j.internal.JdbcConfiguration:239  ] - JDBC::updateConfig: load JDBC-driverClass was successful: 'org.sqlite.JDBC'

I would move to debug:

22:26:41.237 [WARN ] [o.o.p.jdbc.internal.JdbcMapper:155  ] - JDBC::openConnection: setupDataSource.

I have still no luck. I only get the already posted messages. Nothing indicating a write to the database nor massive error messages.

jdbc.persist

Strategies {
	every10sec : "0/15 * * * * ?"
	default = everyChange
}

Items {
	* : strategy = everyChange, restoreOnStartup
	currentTempOutside : strategy = every10sec
}

currentTempOutside is a Yahoo binding which gets updated every 15s. And I can see those updates in the log.

Any other bindings I need to install? At the moment there are following in my addons folder:

org.openhab.binding.exec-1.8.0-SNAPSHOT.jar
org.openhab.binding.http-1.8.0-SNAPSHOT.jar
org.openhab.binding.ntp-1.8.0-SNAPSHOT.jar
org.openhab.binding.weather-1.8.0-SNAPSHOT.jar
org.openhab.binding.wemo-1.8.0-SNAPSHOT.jar
org.openhab.io.dropbox-1.8.0-SNAPSHOT.jar
org.openhab.io.gpio-1.8.0-SNAPSHOT.jar
org.openhab.persistence.exec-1.8.0-SNAPSHOT.jar
org.openhab.persistence.jdbc-1.8.0-SNAPSHOT.jar
sqlite-jdbc-3.8.11.2.jar

Did you set persistence:default=jdbc in openhab.cfg?

Did you test if runtime 1.8.0 with demo configuration 1.8.0 works on raspbian?

Do not see any problems.

Interesting. I get following warning with the demo setup:

2015-12-16 23:41:00.230 [WARN ] [.o.c.p.e.PersistenceExtensions] - There is no queryable persistence service registered with the name 'jdbc'

The persistence file is at its place and also it is configured in openhab.cfg. Driver and binding are in addons.

Is it a problem if openhab is not in its default dir /opt/openhab ?
I will try a vanilla raspbian tomorrow. Maybe there is a pack missing.

If you get this line, jdbc-bundle is definitely started.
If openHAB normally asks for its name, it will be registered as persistence service with the name ‘jdbc’.

Do you really get

22:26:41.233 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:33   ] - JDBC::pingDB

AND in the same start-log

2015-12-16 23:41:00.230 [WARN ] [.o.c.p.e.PersistenceExtensions] - There is no 
queryable persistence service registered with the name 'jdbc'

I guess now is a different problem.

So, tested it on a freshly installed pi with jessey and downloaded OH 1.8 snapshot and installed the demo setup. Same as before. Nothing gets written to database nor is a database created if there isn’t any. And yes I get

22:26:41.233 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:33 ] - JDBC::pingDB

And the new warning with no query able persistence service is also showing up with the demo setup.

I zipped the setup if you want to take a look. Maybe I did a mistake in the config:

Sounds like this is possibly a critical bug, @lewie? Could you investigate and open a critical issue against it if you find that’s the case?