[JDBC-Persistence] heave from OH1 to OH2/ESH, choosing a namespace

JDBC-Persistence is working stable and fast a long time in OH1 now.
Copying jdbc-drivers by hand for OH1 is OK, but is not a good idea when using in OH2 I think.
For guiding further the idea of this issue comment I will try to heave JDBC Persistence from OH1 to OH2/ESH.

  1. Beginning with instructions for developing a new binding I’m struggling at choosing a namespace. @Kai, where would be the right namespace for the JDBC Persistence Service?

  2. I want to use Paper UI to configure the Service and to select a jdbc-driver from a List and load it by maven (at the moment: Apache Derby, H2, HSQLDB, MariaDB, MySQL, PostgreSQL, SQLite could be selected). Are there any examples to trigger a library load by maven with ESH?

  3. OH2 Things are very new for me, does it make sense to implement the JDBC Service(s) with Things feature?!

Any ideas.

Thank You
Helmut

I found this article a year or two ago (when I was getting Karaf+Camel+JDBC+MySQL working) that describes the issues and options pretty well, but I don’t know what advances have been made since then.

Hi, @watou,

but there is no issue with OH2, JDBC-Persistance works very well with OH2!
All jdbc-drivers are osgi containers so there is no problem to load and use jdbc-drivers dynamically with OSGI.

I externalized the driver loading, because to bundle all the drivers within JDBC-Persistence, would let grow the JDBC-OSGI-Bundle, with every new database we want to connect with it. This makes no sense, would not be generic!

But now for OH2, I want to use the new cool maven-features of OH2 to download the drivers directly from maven into OH2. At the moment in old manner you have to download and copy it to addons folder, same in OH1 and OH2.

Ah, could you just add bundles to a new feature and make a PR similar to this? (This would get you part of the way there.)

@lewie, There is absolutely no need to port (duplicate) the persistence service to openHAB 2, since there has noting changed about the APIs. We can easily use the 1.x persistence service on the new runtime as well.
And yes, @watou is right, this does not mean that you cannot have the goodies of openHAB 2 at the same time. You can

  • define a Karaf feature (within the openHAB 1 repo) as shown in @watou’s link. This will allow you to define the bundles to install and the files to create upon installation
  • define configuration meta-data, which will make the service appear under “Configuration->Services” in the Paper UI. This is something that I would love to see being added to all openHAB 1 add-ons soon (I didn’t ask for it yet, because I haven’t documented yet, how to do it exactly). But it actually is fairly simple - all you need to do is to add a file ESH-INF/config/config.xml like this one in your bundle. The syntax of this XML file is described here.

Hope this helps!
Kai

@watou, @Kai,

how cool is that, yes this helps.

Thank you very much!
Helmut

1 Like

@watou, @Kai, added PR for this: Add jdbc Persistence to Paperui and features

1 Like

Very good, I see you understood how it works, even without documentation :slightly_smiling:
I just added one minor question to the PR, the rest is good to merge!

Since there is no openhab.cfg in the openhab 2, how do you go about the entire configuraton for the persistence service using msql

Hello @mickey4u,

Automatic Installation:
Goto PaperUI->Extensions->Persistence Services and install your preferred Service/Database.
After installation of a Service, configure it in PaperUI->Configuration->Services.

In OpenHAB2 you can find the Binding configurations in /conf/services/.
For every Binding one *.cfg File. For JDBC Binding its named jdbc.cfg

Edit 20161203 outdated:
At the moment Installation of JDBC Persistence Service does not work correctly in OpenHAB 2. So you have to install it manually. (@Kai, in eclipse environment installation works, in Karaf it doesn’t)

For now, installation manually:

  1. Goto PaperUI->Extensions->Persistence Services and install your preferred Service/Database.
  2. Adjust/create the following file /conf/services/jdbc.cfg
    Edit 20161203 outdated: 3. Select a Driver File and put it in /addons folder.

For MySql for example there should be a /conf/services/jdbc.cfg like:

############################ JDBC Persistence Service ##################################
# I N S T A L L   J D B C   P E R S I S T E N C E   S E R V I C E 
#
# https://github.com/openhab/openhab/wiki/JDBC-Persistence
#
# Tested databases/url-prefix: jdbc:derby, jdbc:h2, jdbc:hsqldb, jdbc:mariadb, jdbc:mysql, jdbc:postgresql, jdbc:sqlite
# 
# derby, h2, hsqldb, sqlite can be embedded, 
# If no database is available it will be created, for example the url 'jdbc:h2:./testH2' creates a new DB in OpenHab Folder. 
#
# Create new database, for example on a MySQL-Server use: 
# CREATE DATABASE 'yourDB' CHARACTER SET utf8 COLLATE utf8_general_ci;

# D A T A B A S E  C O N F I G
# Some URL-Examples, 'service' identifies and activates internally the correct jdbc driver.
# required database url like 'jdbc:<service>:<host>[:<port>;<attributes>]'
# url=jdbc:derby:./testDerby;create=true
# url=jdbc:h2:./testH2
# url=jdbc:hsqldb:./testHsqlDb
# url=jdbc:mariadb://192.168.0.1:3306/testMariadb
# url=jdbc:mysql://192.168.0.1:3306/testMysql
# url=jdbc:postgresql://192.168.0.1:5432/testPostgresql
# url=jdbc:sqlite:./testSqlite.db
# url=
url=jdbc:mysql://192.168.0.1:3306/testMysql

# required database user
#user=
user=TEST

# required database password
#password=
password=TEST

# E R R O R   H A N D L I N G
# optional when Service is deactivated (optional, default: 0 -> ignore) 
#errReconnectThreshold=

# I T E M   O P E R A T I O N S
# optional tweaking SQL datatypes
# see: https://mybatis.github.io/mybatis-3/apidocs/reference/org/apache/ibatis/type/JdbcType.html   
# see: http://www.h2database.com/html/datatypes.html
# see: http://www.postgresql.org/docs/9.3/static/datatype.html
# defaults:
#sqltype.CALL          =   VARCHAR(200)
#sqltype.COLOR         =   VARCHAR(70)
#sqltype.CONTACT       =   VARCHAR(6)
#sqltype.DATETIME      =   DATETIME
#sqltype.DIMMER        =   TINYINT
#sqltype.LOCATION      =   VARCHAR(30)
#sqltype.NUMBER        =   DOUBLE
#sqltype.ROLLERSHUTTER =   TINYINT
#sqltype.STRING        =   VARCHAR(65500)
#sqltype.SWITCH        =   VARCHAR(6)

# For Itemtype "Number" default decimal digit count (optional, default: 3) 
#numberDecimalcount=

# T A B L E   O P E R A T I O N S
# Tablename Prefix String (optional, default: "item") 
# for Migration from MYSQL-Bundle set to 'Item'.
#tableNamePrefix=Item

# Tablename Prefix generation, using Item real names or "item" (optional, default: false -> "item") 
# If true, 'tableNamePrefix' is ignored.
#tableUseRealItemNames=

# Tablename Suffix length (optional, default: 4 -> 0001-9999) 
# for Migration from MYSQL-Bundle set to 0.
#tableIdDigitCount=

# Rename existing Tables using tableUseRealItemNames and tableIdDigitCount (optional, default: false) 
# USE WITH CARE! Deactivate after Renaming is done!
#rebuildTableNames=true

# D A T A B A S E  C O N N E C T I O N S
# Some embeded Databases can handle only one Connection (optional, default: configured per database in packet org.openhab.persistence.jdbc.db.* )
# see: https://github.com/brettwooldridge/HikariCP/issues/256
# jdbc.maximumPoolSize = 1
# jdbc.minimumIdle = 1

# T I M E K E E P I N G
# (optional, default: false) 
#enableLogTime=true
1 Like

Hi,

Does it work on ESH ?

Thanks.

JDBC binding is still a OH1 binding at the moment. Only if in ESH the compatibility Layer for OH1 is working, it could work.

Hello @lewie,

I tried JDBC and SQLite (sqlite-jdbc-3.21.0.jar) file
on ESH repository. But it shows something

    15:34:07.166 DEBUG [FelixStartLevel] BundleEvent STARTING - org.openhab.persistence.jdbc
    15:34:07.260 DEBUG [FelixStartLevel] JDBC::activate: persistence service activated
    15:34:07.318 DEBUG [FelixStartLevel] JDBC::updateConfig
    15:34:07.356 DEBUG [FelixStartLevel] JDBC::JdbcConfiguration
    15:34:07.420 DEBUG [FelixStartLevel] JDBC::updateConfig: configuration size = 5
    15:34:07.516 DEBUG [FelixStartLevel] No jdbc:user parameter defined in openhab.cfg
    15:34:07.536 DEBUG [FelixStartLevel] No jdbc:password parameter defined in openhab.cfg.
    15:34:07.556 WARN [FelixStartLevel] JDBC url is missing - please configure in openhab.cfg like 'jdbc:<service>:<host>[:<port>;<attributes>]'
    15:34:07.606 DEBUG [FelixStartLevel] JDBC::pingDB
    15:34:07.617 DEBUG [FelixStartLevel] JDBC::openConnection isDriverAvailable: false
    15:34:07.623 WARN [FelixStartLevel] JDBC::openConnection: no driver available!
    15:34:07.730 DEBUG [FelixStartLevel] JDBC::pingDB
    15:34:07.746 DEBUG [FelixStartLevel] JDBC::openConnection isDriverAvailable: false
    15:34:07.756 WARN [FelixStartLevel] JDBC::openConnection: no driver available!
    15:34:07.760 DEBUG [FelixStartLevel] JDBC::checkDBAcessability, second try connection: false
    15:34:07.952 DEBUG [FelixStartLevel] JDBC::getName: returning name 'jdbc' for queryable persistence service.
    15:34:08.014 DEBUG [FelixStartLevel] JDBC::updateConfig: configuration complete for service=jdbc.
    15:34:08.026 DEBUG [FelixStartLevel] ServiceEvent REGISTERED - [org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService] - org.openhab.persistence.jdbc
    15:34:08.030 DEBUG [FelixStartLevel] BundleEvent STARTED - org.openhab.persistence.jdbc
    15:34:08.083 DEBUG [FelixStartLevel] JDBC::setItemRegistry
    15:34:08.092 DEBUG [FelixStartLevel] JDBC::getName: returning name 'jdbc' for queryable persistence service.
    15:34:08.096 DEBUG [FelixStartLevel] JDBC::getName: returning name 'jdbc' for queryable persistence service.
    15:34:08.103 DEBUG [FelixStartLevel] JDBC::getName: returning name 'jdbc' for queryable persistence service.
    15:34:08.108 DEBUG [FelixStartLevel] JDBC::getName: returning name 'jdbc' for queryable persistence service.
    15:34:08.118 DEBUG [FelixStartLevel] JDBC::getName: returning name 'jdbc' for queryable persistence service.
    15:34:08.122 DEBUG [FelixStartLevel] JDBC::getName: returning name 'jdbc' for queryable persistence service.
    15:34:08.142 DEBUG [FelixStartLevel] JDBC::pingDB
    15:34:08.148 DEBUG [FelixStartLevel] JDBC::openConnection isDriverAvailable: false
    15:34:08.154 WARN [FelixStartLevel] JDBC::openConnection: no driver available!
    15:34:08.159 DEBUG [FelixStartLevel] JDBC::pingDB
    15:34:08.165 DEBUG [FelixStartLevel] JDBC::openConnection isDriverAvailable: false
    15:34:08.169 WARN [FelixStartLevel] JDBC::openConnection: no driver available!
    15:34:08.172 DEBUG [FelixStartLevel] JDBC::checkDBAcessability, second try connection: false
    15:34:08.176 WARN [FelixStartLevel] JDBC::query: database not connected, query aborted for item 'light'
16:09:29.297 WARN [items-1] JDBC::store:  No connection to database. Cannot persist item 'light (Type=SwitchItem, State=OFF)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:0

I will be using org.openhab.persistence.jdbc binding.

According to openhab offical documentation my jdbc.cfg file like below showing

services/jdbc.cfg

############################ JDBC Persistence Service ##################################
# I N S T A L L   J D B C   P E R S I S T E N C E   S E R V I C E 
#
# https://github.com/openhab/openhab/wiki/JDBC-Persistence
#
# Tested databases/url-prefix: jdbc:derby, jdbc:h2, jdbc:hsqldb, jdbc:mariadb, jdbc:mysql, jdbc:postgresql, jdbc:sqlite
# 
# derby, h2, hsqldb, sqlite can be embedded, 
# If no database is available it will be created, for example the url 'jdbc:h2:./testH2' creates a new DB in OpenHab Folder. 
#
# Create new database, for example on a MySQL-Server use: 
# CREATE DATABASE 'yourDB' CHARACTER SET utf8 COLLATE utf8_general_ci;

# D A T A B A S E  C O N F I G
# Some URL-Examples, 'service' identifies and activates internally the correct jdbc driver.
# required database url like 'jdbc:<service>:<host>[:<port>;<attributes>]'
# url=jdbc:derby:./testDerby;create=true
# url=jdbc:h2:./testH2
# url=jdbc:hsqldb:./testHsqlDb
# url=jdbc:mariadb://192.168.0.1:3306/testMariadb
# url=jdbc:mysql://192.168.0.1:3306/testMysql
# url=jdbc:postgresql://192.168.0.1:5432/testPostgresql
# url=jdbc:sqlite:./testSqlite.db
# url=

url=jdbc:sqlite:./testSqlite.db

# required database user
#user=

user=TEST

# required database password
#password=

password=TEST

# E R R O R   H A N D L I N G
# optional when Service is deactivated (optional, default: 0 -> ignore) 
#errReconnectThreshold=

# I T E M   O P E R A T I O N S
# optional tweaking SQL datatypes
# see: https://mybatis.github.io/mybatis-3/apidocs/reference/org/apache/ibatis/type/JdbcType.html   
# see: http://www.h2database.com/html/datatypes.html
# see: http://www.postgresql.org/docs/9.3/static/datatype.html
# defaults:
#sqltype.CALL          =   VARCHAR(200)
#sqltype.COLOR         =   VARCHAR(70)
#sqltype.CONTACT       =   VARCHAR(6)
#sqltype.DATETIME      =   DATETIME
#sqltype.DIMMER        =   TINYINT
#sqltype.LOCATION      =   VARCHAR(30)
#sqltype.NUMBER        =   DOUBLE
#sqltype.ROLLERSHUTTER =   TINYINT
#sqltype.STRING        =   VARCHAR(65500)
#sqltype.SWITCH        =   VARCHAR(6)

# For Itemtype "Number" default decimal digit count (optional, default: 3) 
#numberDecimalcount=

# T A B L E   O P E R A T I O N S
# Tablename Prefix String (optional, default: "item") 
# for Migration from MYSQL-Bundle set to 'Item'.
#tableNamePrefix=Item

# Tablename Prefix generation, using Item real names or "item" (optional, default: false -> "item") 
# If true, 'tableNamePrefix' is ignored.
#tableUseRealItemNames=

# Tablename Suffix length (optional, default: 4 -> 0001-9999) 
# for Migration from MYSQL-Bundle set to 0.
#tableIdDigitCount=

# Rename existing Tables using tableUseRealItemNames and tableIdDigitCount (optional, default: false) 
# USE WITH CARE! Deactivate after Renaming is done!
#rebuildTableNames=true

# D A T A B A S E  C O N N E C T I O N S
# Some embeded Databases can handle only one Connection (optional, default: configured per database in packet org.openhab.persistence.jdbc.db.* )
# see: https://github.com/brettwooldridge/HikariCP/issues/256
# jdbc.maximumPoolSize = 1
# jdbc.minimumIdle = 1

# T I M E K E E P I N G
# (optional, default: false) 
#enableLogTime=true

my jdbc.persist file

// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
	everyHour : "0 0 * * * ?"
	everyDay  : "0 0 0 * * ?"

	// if no strategy is specified for an item entry below, the default list will be used
	default = everyChange
}

/* 
 * Each line in this section defines for which item(s) which strategy(ies) should be applied.
 * You can list single items, use "*" for all items or "groupitem*" for all members of a group
 * item (excl. the group item itself).
 */
Items {
	// persist all items once a day and on every change and restore them from the db at startup
	* : strategy = everyChange, everyDay, restoreOnStartup
	
	// additionally, persist all temperature and weather values every hour
	Temperature*, Weather* : strategy = everyHour
}

How I can successfully connect jdbc databse.
Does it any configuration required for connecting JDBC DB.

Thanks,

Amar.