I work with Microsoft SQL Server all day at work and spend my time writing reports and dashboard. Has anyone managed to get OpenHAB persistence data stored in a MS SQL database?
I see it more as a long term data store of what happened, if I wanted rules that did something based on an average over the last x hours then I’d probably use another persistence service for that.
The JDBC Binding should be able to let you use SQL Server. It isn’t one of the tested DBs on the wiki page but as long as you have the driver jar file that shouldn’t matter.
I’ve not done this myself though so I can’t say what troubles you may encounter.
I have setup a completely new OpenHAB server with just 3 items… Persisting to rrdj4 is working as expected but I just can’t get the JDBC to persist anything? I don’t event get any error message.
Might just be my tired eyes, will have to look again tomorrow…
I’m not sure I’ve enabled the right level of debugging, couldn’t easily find anything in the wiki but a few forum searches gave me a vague idea.
I added this to my logback_debug.xml file and restarted…
Okay, thought I’d try something that is known to work first and then go from there. My NAS box allows me to setup a “MariaDB” database so I’ve done that and configured everything according to the wiki.
On startup the JDBC binding now creates an “Items” table in my database but never adds anything to it. For now I’m persisting “AllItems” ever minute and every change just to test it out.
The last few lines here are telling me that it’s doing something like closing down the connection to the DB?
Hi All, I’m also trying to get MSSQL persistence functioning, but in OpenHAB2. I have the mssql jar file, and configured a persistence file and jdbc.cfg file. However, I’m not sure how to install JDBC as all of the installers available from paperUI are for specific db’s and I can’t see anywhere to download manually online. Any help greatly appreciated!
I’ve managed to get the driver loaded. Version 6.2.2.
But now i get an error when i save the jdbc.conf. When i change the connection string to mysql it works an openhab tells me that the driver is loaded. Changing the line back to sqlserver causes the same error
2017-12-03 14:42:01.014 [ERROR] [org.openhab.persistence.jdbc ] - [org.openhab.persistence.jdbc(237)] The activate method has thrown an exception
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(String.java:1967) [?:?]
at org.openhab.persistence.jdbc.utils.StringUtilsExt.stringBeforeSubstr(StringUtilsExt.java:224) [227:org.openhab.persistence.jdbc:1.11.0.201712030210]
at org.openhab.persistence.jdbc.utils.StringUtilsExt.parseJdbcURL(StringUtilsExt.java:158) [227:org.openhab.persistence.jdbc:1.11.0.201712030210]
at org.openhab.persistence.jdbc.utils.StringUtilsExt.parseJdbcURL(StringUtilsExt.java:57) [227:org.openhab.persistence.jdbc:1.11.0.201712030210]
at org.openhab.persistence.jdbc.internal.JdbcConfiguration.updateConfig(JdbcConfiguration.java:84) [227:org.openhab.persistence.jdbc:1.11.0.201712030210]
at org.openhab.persistence.jdbc.internal.JdbcConfiguration.<init>(JdbcConfiguration.java:68) [227:org.openhab.persistence.jdbc:1.11.0.201712030210]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.updateConfig(JdbcPersistenceService.java:217) [227:org.openhab.persistence.jdbc:1.11.0.201712030210]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.activate(JdbcPersistenceService.java:61) [227:org.openhab.persistence.jdbc:1.11.0.201712030210]
at sun.reflect.GeneratedMethodAccessor113.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:?]
at org.apache.felix.scr.impl.inject.BaseMethod.invokeMethod(BaseMethod.java:229) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.inject.BaseMethod.access$500(BaseMethod.java:39) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.inject.BaseMethod$Resolved.invoke(BaseMethod.java:650) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.inject.BaseMethod.invoke(BaseMethod.java:506) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.inject.ActivateMethod.invoke(ActivateMethod.java:307) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.inject.ActivateMethod.invoke(ActivateMethod.java:299) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.SingleComponentManager.createImplementationObject(SingleComponentManager.java:298) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.SingleComponentManager.createComponent(SingleComponentManager.java:109) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:906) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.SingleComponentManager.getServiceInternal(SingleComponentManager.java:879) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:823) [42:org.apache.felix.scr:2.0.12]
at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse$1.run(ServiceFactoryUse.java:212) [?:?]
at java.security.AccessController.doPrivileged(Native Method) ~[?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.factoryGetService(ServiceFactoryUse.java:210) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.getService(ServiceFactoryUse.java:111) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceConsumer$2.getService(ServiceConsumer.java:45) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.getService(ServiceRegistrationImpl.java:496) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.getService(ServiceRegistry.java:461) [?:?]
at org.eclipse.osgi.internal.framework.BundleContextImpl.getService(BundleContextImpl.java:619) [?:?]
at org.apache.felix.scr.impl.manager.SingleRefPair.getServiceObject(SingleRefPair.java:72) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.inject.BindMethod.getServiceObject(BindMethod.java:651) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.DependencyManager.getServiceObject(DependencyManager.java:2201) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.DependencyManager.invokeUnbindMethod(DependencyManager.java:1829) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.SingleComponentManager.invokeUnbindMethod(SingleComponentManager.java:395) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.DependencyManager$MultipleDynamicCustomizer.removedService(DependencyManager.java:375) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.DependencyManager$MultipleDynamicCustomizer.removedService(DependencyManager.java:291) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.customizerRemoved(ServiceTracker.java:1241) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.customizerRemoved(ServiceTracker.java:1136) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.ServiceTracker$AbstractTracked.untrack(ServiceTracker.java:996) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.serviceChanged(ServiceTracker.java:1175) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.BundleComponentActivator$ListenerInfo.serviceChanged(BundleComponentActivator.java:127) [42:org.apache.felix.scr:2.0.12]
at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:109) [?:?]
at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:915) [?:?]
at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) [?:?]
at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:862) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:801) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.unregister(ServiceRegistrationImpl.java:222) [?:?]
at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.unregister(AbstractComponentManager.java:909) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.unregister(AbstractComponentManager.java:874) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.RegistrationManager.changeRegistration(RegistrationManager.java:139) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.AbstractComponentManager.unregisterService(AbstractComponentManager.java:951) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.AbstractComponentManager.doDeactivate(AbstractComponentManager.java:806) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.AbstractComponentManager.deactivateInternal(AbstractComponentManager.java:788) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.SingleComponentManager.reconfigure(SingleComponentManager.java:658) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.SingleComponentManager.reconfigure(SingleComponentManager.java:609) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.ConfigurableComponentHolder.configurationUpdated(ConfigurableComponentHolder.java:426) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.RegionConfigurationSupport.configurationEvent(RegionConfigurationSupport.java:284) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.RegionConfigurationSupport$1.configurationEvent(RegionConfigurationSupport.java:89) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.cm.impl.ConfigurationManager$FireConfigurationEvent.sendEvent(ConfigurationManager.java:2090) [7:org.apache.felix.configadmin:1.8.16]
at org.apache.felix.cm.impl.ConfigurationManager$FireConfigurationEvent.run(ConfigurationManager.java:2058) [7:org.apache.felix.configadmin:1.8.16]
at org.apache.felix.cm.impl.UpdateThread.run0(UpdateThread.java:141) [7:org.apache.felix.configadmin:1.8.16]
at org.apache.felix.cm.impl.UpdateThread.run(UpdateThread.java:109) [7:org.apache.felix.configadmin:1.8.16]
at java.lang.Thread.run(Thread.java:748) [?:?]
2017-12-03 14:42:01.020 [WARN ] [org.openhab.persistence.jdbc ] - FrameworkEvent WARNING - org.openhab.persistence.jdbc
org.osgi.framework.ServiceException: org.apache.felix.scr.impl.manager.SingleComponentManager.getService() returned a null service object
at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.factoryGetService(ServiceFactoryUse.java:232) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.getService(ServiceFactoryUse.java:111) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceConsumer$2.getService(ServiceConsumer.java:45) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.getService(ServiceRegistrationImpl.java:496) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.getService(ServiceRegistry.java:461) [?:?]
at org.eclipse.osgi.internal.framework.BundleContextImpl.getService(BundleContextImpl.java:619) [?:?]
at org.apache.felix.scr.impl.manager.SingleRefPair.getServiceObject(SingleRefPair.java:72) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.inject.BindMethod.getServiceObject(BindMethod.java:651) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.DependencyManager.getServiceObject(DependencyManager.java:2201) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.DependencyManager.invokeUnbindMethod(DependencyManager.java:1829) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.SingleComponentManager.invokeUnbindMethod(SingleComponentManager.java:395) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.DependencyManager$MultipleDynamicCustomizer.removedService(DependencyManager.java:375) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.DependencyManager$MultipleDynamicCustomizer.removedService(DependencyManager.java:291) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.customizerRemoved(ServiceTracker.java:1241) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.customizerRemoved(ServiceTracker.java:1136) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.ServiceTracker$AbstractTracked.untrack(ServiceTracker.java:996) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.serviceChanged(ServiceTracker.java:1175) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.BundleComponentActivator$ListenerInfo.serviceChanged(BundleComponentActivator.java:127) [42:org.apache.felix.scr:2.0.12]
at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:109) [?:?]
at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:915) [?:?]
at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) [?:?]
at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:862) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:801) [?:?]
at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.unregister(ServiceRegistrationImpl.java:222) [?:?]
at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.unregister(AbstractComponentManager.java:909) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.unregister(AbstractComponentManager.java:874) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.RegistrationManager.changeRegistration(RegistrationManager.java:139) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.AbstractComponentManager.unregisterService(AbstractComponentManager.java:951) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.AbstractComponentManager.doDeactivate(AbstractComponentManager.java:806) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.AbstractComponentManager.deactivateInternal(AbstractComponentManager.java:788) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.SingleComponentManager.reconfigure(SingleComponentManager.java:658) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.SingleComponentManager.reconfigure(SingleComponentManager.java:609) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.ConfigurableComponentHolder.configurationUpdated(ConfigurableComponentHolder.java:426) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.RegionConfigurationSupport.configurationEvent(RegionConfigurationSupport.java:284) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.scr.impl.manager.RegionConfigurationSupport$1.configurationEvent(RegionConfigurationSupport.java:89) [42:org.apache.felix.scr:2.0.12]
at org.apache.felix.cm.impl.ConfigurationManager$FireConfigurationEvent.sendEvent(ConfigurationManager.java:2090) [7:org.apache.felix.configadmin:1.8.16]
at org.apache.felix.cm.impl.ConfigurationManager$FireConfigurationEvent.run(ConfigurationManager.java:2058) [7:org.apache.felix.configadmin:1.8.16]
at org.apache.felix.cm.impl.UpdateThread.run0(UpdateThread.java:141) [7:org.apache.felix.configadmin:1.8.16]
at org.apache.felix.cm.impl.UpdateThread.run(UpdateThread.java:109) [7:org.apache.felix.configadmin:1.8.16]
at java.lang.Thread.run(Thread.java:748) [?:?]
I never got anywhere but am still interested to try and get it working. Azure would be good but local would let me write reports and dashboards off it nice and easily.
The source code for the jdbc driver seems to be looking for the following format for the connection string (altered version of the one provided earlier in this conversation):