Hildebrand Glowmarkt API Binding [3.3.0;4.0.0)

logo

This binding allows import of Smart Meter data from the Hildebrand’s Glowmarkt API.
The information here is similar to what can be obtained using their Bright app.
You can use this to get your historic meter data from DCC.

img.png

Configuring

In order to use this binding, you will need to register for an account to use
the Glowmarkt API, and agree for them to import your Smart Meter data from DCC.

You will also need to install and configure an OpenHAB persistence service
that supports updating of past Item data via ModifiablePersistenceService.
As of OpenHAB 3.3.0 the default RRD4J persistence service doesn’t support this,
so you will need to use one of the other persistence services such as JDBC.

Once you have added the Glowmarkt API Bridge Thing, configure the User name and
password for your Glowmarkt API access, and the name of your persistence service.

It should then discover the smart meter Virtual Entity Thing and it will appear
in your Inbox. The Virtual Entity should then have the gas and electricity channels.

Historic meter reading data

You will need to link items to the cost and consumption channels. Please note that
the readings reported by the items linked to these channels will always read NULL
as current readings are never obtained. Instead, you should create charts against these
items in order to view the historic values once they are downloaded.

The binding will then attempt daily to download the gas and electricity meter readings.
Be aware this may take some time to complete.

Current tariff information

The binding will create channels that obtain the current standing charge and per-unit tariffs
for gas and electricity. Unlike the meter reading data, historic data for these values
is not available and the current values are reported directly in items linked to the
channels in the normal way.

Supported Channels

  • Gas Consumption
  • Gas Cost
  • Electricity Consumption
  • Electricity Cost
  • Gas Standing Charge
  • Gas Per Unit Rate
  • Electricity Standing Charge
  • Electricity Per Unit Rate

The downloaded data is at 30-minute interval resolution, and it will download all the data
that it can find. Every 24 hours it will download any new data it doesn’t already have.

Example Chart Configuration

To create a chart like that shown above, first of all link the Gas cost and Electricity cost channels to some Number Point items.
You will then need to create a persistence configuration to ensure that the data can be saved.

Persistence configuration (JDBC example)

You will need to have a supported database installed, such as MySQL, and then install the corresponding persistence addon
in Settings → Other Add-ons → JDBC Persistence {name of your database}.

Then create a persistence configuration file to configure the default persistence strategy for your items to use that
persistence service. For example for JDBC, create in conf/persistence a jdbc.persist file:

Strategies {
}
Items {
        DCCSmartMeters_* : strategy = everyChange
}

Chart creation

Then create a chart, go to Pages → (+) Create Chart.
Select Chart Type = Month. Then configure axes:

Y Axis (Value Axis)

Click Add Value Axis to create a Y axis, give it a name (like cost)

X Axis (Category Axis)

Click Add Category Axis to create an X axis, give it a name (like day of month)

Item Setting
Name Day of month
Categories Days of month

Data series

Click Add Aggregate series to add an aggregate series

Item Setting
Name e.g. Gas cost
Item Your Gas Cost item
Type Bar
First Dimension Day of Month
Aggregation Function Sum
Persistence Service (this is an advanced option) persistence service you are using e.g. JDBC

Changelog

3.3.6

Max number of years of data to fetch can now be configured.

3.3.5

Add support for reporting current tariff standing charge and per-unit rates.
Fix #19 Using openhab in Docker prevents authentication - Illegal key size - CRYPTO_POLICY ==> limited

3.3.3

Stored credentials are now encrypted for better security

3.3.1

This is the initial release.

Resources

Github Release Binary
Github Source Code

1 Like

Thank you for providing this binding.

Have you tried it with jdbc::sqlite?
I could not find out if this supports ModifiablePersistenceService or not, but didn’t want to go through installing MySql
It appears to connect to my Glowmarkt account OK because it retrieves the correct cost and consumption values, but I get many many errors similar to this:

2022-11-30 17:34:37.324 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "#dbType": syntax error) Query: INSERT OR IGNORE INTO item0004 (TIME, VALUE) VALUES( ?, CAST( ? as #dbType#) ) Parameters: [2022-11-24 19:00:00.0, 0.255, 0.255]
	at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[?:?]
	at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[?:?]
	at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[?:?]
	at org.knowm.yank.Yank.execute(Yank.java:194) ~[?:?]
	at org.knowm.yank.Yank.execute(Yank.java:177) ~[?:?]
	at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doStoreItemValue(JdbcBaseDAO.java:336) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:168) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.internalStore(JdbcPersistenceService.java:156) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:140) ~[?:?]
	at com.qubular.binding.glowmarkt.internal.GlowmarktVirtualEntityHandler.lambda$batchFetchHistoricData$12(GlowmarktVirtualEntityHandler.java:273) ~[?:?]
	at java.util.ArrayList.forEach(ArrayList.java:1541) ~[?:?]
	at com.qubular.binding.glowmarkt.internal.GlowmarktVirtualEntityHandler.batchFetchHistoricData(GlowmarktVirtualEntityHandler.java:272) ~[?:?]
	at com.qubular.binding.glowmarkt.internal.GlowmarktVirtualEntityHandler.fetchHistoricDataForMissingPeriod(GlowmarktVirtualEntityHandler.java:253) ~[?:?]
	at com.qubular.binding.glowmarkt.internal.GlowmarktVirtualEntityHandler.fetchHistoricData(GlowmarktVirtualEntityHandler.java:226) ~[?:?]
	at com.qubular.binding.glowmarkt.internal.GlowmarktVirtualEntityHandler.handleCommand(GlowmarktVirtualEntityHandler.java:178) ~[?:?]
	at org.openhab.core.thing.binding.BaseThingHandler.channelLinked(BaseThingHandler.java:180) ~[?:?]
	at org.openhab.core.thing.internal.ChannelLinkNotifier.lambda$3(ChannelLinkNotifier.java:72) ~[?:?]
	at org.openhab.core.thing.internal.ChannelLinkNotifier.call(ChannelLinkNotifier.java:96) ~[?:?]
	at org.openhab.core.thing.internal.ChannelLinkNotifier.added(ChannelLinkNotifier.java:72) ~[?:?]
	at org.openhab.core.thing.internal.ChannelLinkNotifier.added(ChannelLinkNotifier.java:1) ~[?:?]
	at org.openhab.core.common.registry.AbstractRegistry.notifyListeners(AbstractRegistry.java:367) ~[?:?]
	at org.openhab.core.common.registry.AbstractRegistry.notifyListenersAboutAddedElement(AbstractRegistry.java:400) ~[?:?]
	at org.openhab.core.thing.link.ItemChannelLinkRegistry.notifyListenersAboutAddedElement(ItemChannelLinkRegistry.java:181) ~[?:?]
	at org.openhab.core.thing.link.ItemChannelLinkRegistry.notifyListenersAboutAddedElement(ItemChannelLinkRegistry.java:1) ~[?:?]
	at org.openhab.core.common.registry.AbstractRegistry.added(AbstractRegistry.java:175) ~[?:?]
	at org.openhab.core.common.registry.AbstractRegistry.added(AbstractRegistry.java:1) ~[?:?]
	at org.openhab.core.common.registry.AbstractProvider.notifyListeners(AbstractProvider.java:60) ~[?:?]
	at org.openhab.core.common.registry.AbstractProvider.notifyListeners(AbstractProvider.java:79) ~[?:?]
	at org.openhab.core.common.registry.AbstractProvider.notifyListenersAboutAddedElement(AbstractProvider.java:83) ~[?:?]
	at org.openhab.core.common.registry.AbstractManagedProvider.add(AbstractManagedProvider.java:67) ~[?:?]
	at org.openhab.core.common.registry.AbstractRegistry.add(AbstractRegistry.java:346) ~[?:?]
	at org.openhab.core.io.rest.core.internal.link.ItemChannelLinkResource.link(ItemChannelLinkResource.java:259) ~[?:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
	at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
	at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:179) ~[bundleFile:3.4.5]
	at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96) ~[bundleFile:3.4.5]
	at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:201) ~[bundleFile:3.4.5]
	at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:104) ~[bundleFile:3.4.5]
	at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59) ~[bundleFile:3.4.5]
	at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96) ~[bundleFile:3.4.5]
	at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308) ~[bundleFile:3.4.5]
	at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121) ~[bundleFile:3.4.5]
	at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:265) ~[bundleFile:3.4.5]
	at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234) ~[bundleFile:3.4.5]
	at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208) ~[bundleFile:3.4.5]
	at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160) ~[bundleFile:3.4.5]
	at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:225) ~[bundleFile:3.4.5]
	at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:298) ~[bundleFile:3.4.5]
	at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doPut(AbstractHTTPServlet.java:234) ~[bundleFile:3.4.5]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:710) ~[bundleFile:3.1.0]
	at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:273) ~[bundleFile:3.4.5]
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:550) ~[bundleFile:9.4.46.v20220331]
	at org.ops4j.pax.web.service.jetty.internal.HttpServiceServletHandler.doHandle(HttpServiceServletHandler.java:74) ~[bundleFile:?]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440) ~[bundleFile:9.4.46.v20220331]
	at org.ops4j.pax.web.service.jetty.internal.HttpServiceContext.doHandle(HttpServiceContext.java:294) ~[bundleFile:?]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) ~[bundleFile:9.4.46.v20220331]
	at org.ops4j.pax.web.service.jetty.internal.JettyServerHandlerCollection.handle(JettyServerHandlerCollection.java:90) ~[bundleFile:?]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.Server.handle(Server.java:516) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487) ~[bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883) [bundleFile:9.4.46.v20220331]
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034) [bundleFile:9.4.46.v20220331]
	at java.lang.Thread.run(Thread.java:829) [?:?]

I also noticed this recent fix for jdbc::PostgreSQL
[jdbc] Fix PostgreSQL query for storing timestamps #13745
and wondered if it was related.

Which version of openHAB are you running?

  • Platform information:
    • Hardware: RPI4 8G
    • OS: Raspbian GNU/Linux 10 (buster). OpenHABian
    • Java Runtime Environment: openjdk 11.0.16
    • openHAB version: 3.4.0.M3

It’s related - the same fix is needed for sqllite. I’ll provide a fix in the evening (CET). I cannot test it myself, so hoping you can help. :slight_smile:

org.openhab.persistence.jdbc-3.4.0-SNAPSHOT.jar

You will need to place this in the addons directory also:
https://repo1.maven.org/maven2/org/xerial/sqlite-jdbc/3.16.1/sqlite-jdbc-3.16.1.jar

And probably rename it to sqlite-jdbc.jar

Yes I’m happy to give it a try, but bear in mind all I have to test it with is this binding. When it fails, it spits out MegaBytes of logs making openHAB unresponsive for several minutes. So please be patient!

1 Like

Pull request here:

I have put the two files into addons, and renamed the latter but get this

2022-12-01 17:41:15.977 [WARN ] [e.karaf.internal.ConsoleSupportKaraf] - bundle org.openhab.core.io.console.karaf:3.4.0.M3 (169)[org.openhab.core.io.console.karaf.internal.ConsoleSupportKaraf(276)] : Could not get service from ref {org.openhab.core.io.console.extensions.ConsoleCommandExtension}={service.id=443, service.bundleid=215, service.scope=bundle, component.name=org.openhab.persistence.jdbc.internal.console.JdbcCommandExtension, component.id=1}
2022-12-01 17:41:15.986 [ERROR] [Events.Framework                    ] - FrameworkEvent ERROR
org.osgi.framework.ServiceException: Exception in org.apache.felix.scr.impl.manager.SingleComponentManager.getService()
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.factoryGetService(ServiceFactoryUse.java:230) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.getService(ServiceFactoryUse.java:118) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceConsumer$2.getService(ServiceConsumer.java:48) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.getService(ServiceRegistrationImpl.java:547) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.getService(ServiceRegistry.java:533) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.getService(BundleContextImpl.java:655) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.apache.felix.scr.impl.manager.SingleRefPair.getServiceObject(SingleRefPair.java:88) ~[?:?]
	at org.apache.felix.scr.impl.inject.methods.BindMethod.getServiceObject(BindMethod.java:675) ~[?:?]
	at org.apache.felix.scr.impl.manager.DependencyManager.getServiceObject(DependencyManager.java:2556) ~[?:?]
	at org.apache.felix.scr.impl.manager.DependencyManager$MultipleDynamicCustomizer.prebind(DependencyManager.java:431) ~[?:?]
	at org.apache.felix.scr.impl.manager.DependencyManager.prebind(DependencyManager.java:1827) ~[?:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.collectDependencies(AbstractComponentManager.java:1057) ~[?:?]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getServiceInternal(SingleComponentManager.java:953) ~[?:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.activateInternal(AbstractComponentManager.java:785) ~[?:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.enableInternal(AbstractComponentManager.java:674) ~[?:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.enable(AbstractComponentManager.java:437) ~[?:?]
	at org.apache.felix.scr.impl.manager.ConfigurableComponentHolder.enableComponents(ConfigurableComponentHolder.java:667) ~[?:?]
	at org.apache.felix.scr.impl.BundleComponentActivator.initialEnable(BundleComponentActivator.java:305) ~[?:?]
	at org.apache.felix.scr.impl.Activator.loadComponents(Activator.java:554) ~[?:?]
	at org.apache.felix.scr.impl.Activator.access$200(Activator.java:70) ~[?:?]
	at org.apache.felix.scr.impl.Activator$ScrExtension.start(Activator.java:421) ~[?:?]
	at org.apache.felix.scr.impl.AbstractExtender.createExtension(AbstractExtender.java:196) ~[?:?]
	at org.apache.felix.scr.impl.AbstractExtender.modifiedBundle(AbstractExtender.java:169) ~[?:?]
	at org.apache.felix.scr.impl.AbstractExtender.modifiedBundle(AbstractExtender.java:49) ~[?:?]
	at org.osgi.util.tracker.BundleTracker$Tracked.customizerModified(BundleTracker.java:488) ~[osgi.core-7.0.0.jar:?]
	at org.osgi.util.tracker.BundleTracker$Tracked.customizerModified(BundleTracker.java:420) ~[osgi.core-7.0.0.jar:?]
	at org.osgi.util.tracker.AbstractTracked.track(AbstractTracked.java:232) ~[osgi.core-7.0.0.jar:?]
	at org.osgi.util.tracker.BundleTracker$Tracked.bundleChanged(BundleTracker.java:450) ~[osgi.core-7.0.0.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:944) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:234) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:151) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEventPrivileged(EquinoxEventPublisher.java:229) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEvent(EquinoxEventPublisher.java:138) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEvent(EquinoxEventPublisher.java:130) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.framework.EquinoxContainerAdaptor.publishModuleEvent(EquinoxContainerAdaptor.java:217) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.container.Module.publishEvent(Module.java:499) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.container.Module.start(Module.java:486) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.framework.EquinoxBundle.start(EquinoxBundle.java:445) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.framework.EquinoxBundle.start(EquinoxBundle.java:464) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.apache.karaf.features.internal.service.BundleInstallSupportImpl.startBundle(BundleInstallSupportImpl.java:165) ~[?:?]
	at org.apache.karaf.features.internal.service.FeaturesServiceImpl.startBundle(FeaturesServiceImpl.java:1160) ~[?:?]
	at org.apache.karaf.features.internal.service.Deployer.deploy(Deployer.java:1041) ~[?:?]
	at org.apache.karaf.features.internal.service.FeaturesServiceImpl.doProvision(FeaturesServiceImpl.java:1069) ~[?:?]
	at org.apache.karaf.features.internal.service.FeaturesServiceImpl.lambda$doProvisionInThread$13(FeaturesServiceImpl.java:1004) ~[?:?]
	at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
	at java.lang.Thread.run(Thread.java:829) ~[?:?]
Caused by: java.lang.NoClassDefFoundError: org/openhab/core/io/console/ConsoleCommandCompleter
	at java.lang.ClassLoader.defineClass1(Native Method) ~[?:?]
	at java.lang.ClassLoader.defineClass(ClassLoader.java:1017) ~[?:?]
	at org.eclipse.osgi.internal.loader.ModuleClassLoader.defineClass(ModuleClassLoader.java:283) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.classpath.ClasspathManager.defineClass(ClasspathManager.java:716) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.classpath.ClasspathManager.findClassImpl(ClasspathManager.java:639) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.classpath.ClasspathManager.findLocalClassImpl(ClasspathManager.java:607) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.classpath.ClasspathManager.findLocalClassImpl(ClasspathManager.java:587) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.classpath.ClasspathManager.findLocalClass(ClasspathManager.java:566) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.ModuleClassLoader.findLocalClass(ModuleClassLoader.java:335) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.BundleLoader.findLocalClass(BundleLoader.java:397) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass0(BundleLoader.java:488) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:416) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.ModuleClassLoader.loadClass(ModuleClassLoader.java:168) ~[org.eclipse.osgi-3.17.200.jar:?]
	at java.lang.ClassLoader.loadClass(ClassLoader.java:522) ~[?:?]
	at org.eclipse.osgi.internal.framework.EquinoxBundle.loadClass(EquinoxBundle.java:622) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.initDependencyManagers(AbstractComponentManager.java:1026) ~[?:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.collectDependencies(AbstractComponentManager.java:1054) ~[?:?]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getServiceInternal(SingleComponentManager.java:953) ~[?:?]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:918) ~[?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse$1.run(ServiceFactoryUse.java:220) ~[org.eclipse.osgi-3.17.200.jar:?]
	at java.security.AccessController.doPrivileged(Native Method) ~[?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.factoryGetService(ServiceFactoryUse.java:217) ~[org.eclipse.osgi-3.17.200.jar:?]
	... 47 more
Caused by: java.lang.ClassNotFoundException: org.openhab.core.io.console.ConsoleCommandCompleter cannot be found by org.openhab.persistence.jdbc_3.4.0.202212011626
	at org.eclipse.osgi.internal.loader.BundleLoader.generateException(BundleLoader.java:529) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass0(BundleLoader.java:475) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:416) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.ModuleClassLoader.loadClass(ModuleClassLoader.java:168) ~[org.eclipse.osgi-3.17.200.jar:?]
	at java.lang.ClassLoader.loadClass(ClassLoader.java:522) ~[?:?]
	at java.lang.ClassLoader.defineClass1(Native Method) ~[?:?]
	at java.lang.ClassLoader.defineClass(ClassLoader.java:1017) ~[?:?]
	at org.eclipse.osgi.internal.loader.ModuleClassLoader.defineClass(ModuleClassLoader.java:283) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.classpath.ClasspathManager.defineClass(ClasspathManager.java:716) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.classpath.ClasspathManager.findClassImpl(ClasspathManager.java:639) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.classpath.ClasspathManager.findLocalClassImpl(ClasspathManager.java:607) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.classpath.ClasspathManager.findLocalClassImpl(ClasspathManager.java:587) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.classpath.ClasspathManager.findLocalClass(ClasspathManager.java:566) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.ModuleClassLoader.findLocalClass(ModuleClassLoader.java:335) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.BundleLoader.findLocalClass(BundleLoader.java:397) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass0(BundleLoader.java:488) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:416) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.eclipse.osgi.internal.loader.ModuleClassLoader.loadClass(ModuleClassLoader.java:168) ~[org.eclipse.osgi-3.17.200.jar:?]
	at java.lang.ClassLoader.loadClass(ClassLoader.java:522) ~[?:?]
	at org.eclipse.osgi.internal.framework.EquinoxBundle.loadClass(EquinoxBundle.java:622) ~[org.eclipse.osgi-3.17.200.jar:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.initDependencyManagers(AbstractComponentManager.java:1026) ~[?:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.collectDependencies(AbstractComponentManager.java:1054) ~[?:?]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getServiceInternal(SingleComponentManager.java:953) ~[?:?]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:918) ~[?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse$1.run(ServiceFactoryUse.java:220) ~[org.eclipse.osgi-3.17.200.jar:?]
	at java.security.AccessController.doPrivileged(Native Method) ~[?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.factoryGetService(ServiceFactoryUse.java:217) ~[org.eclipse.osgi-3.17.200.jar:?]
	... 47 more

I tried stopping openHAB, clean cache, etc but I don’t much of a clue when it comes to understanding.
Hopefully you do?

Sorry, I didn’t think of that. Your 3.4.0M3 version is too old for the new console extensions with tab completion, so you would have to upgrade to at least M4. Preferable M5 which was released on Sunday.

I think it might work even with this exception though, but the console extensions will not work.

When reviewing my own code I found a mistake, so please download the JAR again (same link) before continuing the tests.

Ok thanks. I couldn’t upgrade to M4 because of the jsscripting bug but hopefully that’s fixed now in M5. Tomorrow job for me.

Yes, it’s fixed in M5.

So after upgrade to M5, and putting the linked JARs in addons, when the Glowmarkt bridge is installed:

2022-12-02 16:20:27.582 [WARN ] [mmon.WrappedScheduledExecutorService] - Scheduled runnable ended with an exception: 
java.lang.NullPointerException: null
	at java.util.Base64$Decoder.decode(Base64.java:561) ~[?:?]
	at com.qubular.binding.glowmarkt.internal.CryptUtil.decrypt(CryptUtil.java:52) ~[?:?]
	at com.qubular.binding.glowmarkt.internal.GlowmarktBridgeHandler.getGlowmarktSession(GlowmarktBridgeHandler.java:192) ~[?:?]
	at com.qubular.binding.glowmarkt.internal.GlowmarktBridgeHandler.getVirtualEntities(GlowmarktBridgeHandler.java:203) ~[?:?]
	at com.qubular.binding.glowmarkt.internal.GlowmarktDiscoveryService.lambda$scanJob$1(GlowmarktDiscoveryService.java:67) ~[?:?]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) ~[?:?]
	at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305) ~[?:?]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
	at java.lang.Thread.run(Thread.java:829) [?:?]

but the bridge thing comes online.

I tried to establish if the jdbc persistence is working by changing one of the items, but it looks like it isn’t i.e. the database file is not updated.

jdbc.persist:

Strategies {
}
Items {
  SmartMeter_* : strategy = everyChange
}

the only active line in jdbc.cfg:

url=jdbc:sqlite:./Glowmarkt.db

Just FYI tried the new console commands:

openhab> openhab:jdbc
Usage: openhab:jdbc schema check - check schema integrity
Usage: openhab:jdbc schema fix [<itemName>] - fix schema integrity
Usage: openhab:jdbc tables list [all] - list tables (all = include valid)
Usage: openhab:jdbc tables clean [<itemName>] [force] - clean inconsistent items (remove from index and drop tables)
Usage: openhab:jdbc reload - reload item index/schema
openhab> openhab:jdbc schema check
Table     Item                                                            Issue
--------  --------------------------------------------------------------  ----------------------------------------------------------------
org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException: Error in SQL query!!!; [SQLITE_ERROR] SQL error or missing database (no such table: information_schema.columns) Query: SELECT column_name, column_type, is_nullable FROM information_schema.columns WHERE table_schema='/var/lib/openhab/./Glowmarkt.db' AND table_name='item0009' Parameters: []; Pool Name= yank-default; SQL= SELECT column_name, column_type, is_nullable FROM information_schema.columns WHERE table_schema='/var/lib/openhab/./Glowmarkt.db' AND table_name='item0009'
openhab> openhab:jdbc tables list
org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException: Error in SQL query!!!; [SQLITE_ERROR] SQL error or missing database (no such table: information_schema.tables) Query: SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='/var/lib/openhab/./Glowmarkt.db' AND NOT table_name='items' Parameters: []; Pool Name= yank-default; SQL= SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='/var/lib/openhab/./Glowmarkt.db' AND NOT table_name='items'
openhab>

Perhaps this should be moved to a new thread - but I don’t know how to do that.

Okay, let’s verify a few things. First, did you uninstall JDBC Persistence before dropping the JAR files in the addons directory?

Now, let’s check filenames and sizes:

  • org.openhab.persistence.jdbc-3.4.0-SNAPSHOT.jar - size: 344812 bytes
  • sqlite-jdbc.jar - size: 6161579 bytes

If it matches, do you see anything in the log before the entry you posted? You could try to enable debug logging:

ssh -p 8101 openhab@localhost 'log:set debug org.openhab.persistence.jdbc'

If you remove “org.openhab.persistence.jdbc-3.4.0-SNAPSHOT.jar” from the addons directory and put it back in, you should see something in the log.

Yes, that would be best. I don’t run SQLite myself, so probably won’t be able to support that for all the new console features, but I will do a quick check to see if there is something obvious to try in order to fetch column definitions in SQLite.

Can you check if openhab:jdbc tables list works with this version:
org.openhab.persistence.jdbc-3.4.0-SNAPSHOT.jar

?

Yes I did uninstall JDBC Persistence before dropping the JAR files in the addons directory.

The filenames and sizes are correct.

Remove - replace of the jar gives:

2022-12-04 10:40:16.870 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource

I’ll try adding debug logs now.

Yes it works.

openhab> openhab:jdbc tables list
Table            Row Count  Item                                                            Status
---------------  ---------  --------------------------------------------------------------  ----------------------
item0001                 1  DCCSmartMeters__gasconsumptionTariffStandingCharge              Item missing
item0002                 1  DCCSmartMeters__gasconsumptionTariffPerUnitCharge               Item missing
item0003                 0  DCCSmartMeters__GasConsumption                                  Item missing
item0004                 0  DCCSmartMeters__ElectricityConsumption                          Item missing
item0005                 0  DCCSmartMeters__GasCost                                         Item missing
item0006                 0  DCCSmartMeters__Electricitycost                                 Item missing
item0007                 1  DCCSmartMeters__electricityconsumptionTariffStandingCharge      Item missing
item0008                 1  DCCSmartMeters__electricityconsumptionTariffPerUnitChargeTier1  Item missing
sqlite_sequence          0                                                                  Orphan table

The items were created and entered the first time I installed the binding, so using jdbc:sqlite from M3.
After the errors, I deleted the items to start again, thinking perhaps the item names were too long for sqlite.