Has anyone created a binding/addon for reading and writing to Microsoft SQL Server?
Thanks.
Did you mean a Persistence Service, where states of items are stored in an MS SQL database server based on persistence configuration or are you actually intending on having items which communicate with a specific value store in a column of a specific row of a table within an MSSQL database to integrate with some sort of specific product?
No. I managed to get the persistence service working for storing states in MS SQL Server; however, I would like to communicate with my Microsoft SQL Server. I am using a Raspberry PI to connect to my alarm system. It’s a serial connection so the communication is in the form of a long ASCII string. I would like to store the configuration information in MS SQL server and pass the alarm responses to a SQL server stored procedure and let the MS SQL server engine handle the parsing and pass back the information I need.
Is this possible?
Anything is possible, the question is whether or not something is an effective or efficient solution. Given that you are intending to use MS SQL basically as a message manipulation and interaction system for your alarm, I would think something like MQTT and it’s relevant binding would be a more appropriate choice than MS SQL.
Not really looking for the MQTT message manipulation. I really need to do a log of logic on the MSSQL server side. Has anyone thought of a way to interact directly with MSSQL Server?
Please try my binding.
Kazuhiro,
Sorry for such the long delay but I’ve been very busy. Finally got around to testing your binding. I set everything up but getting the below error message over and over. Any ideas?
2015-10-24 19:47:19.793 [ERROR] [o.o.b.A.internal.ACDBBinding ] - ACSQLServer:データの検索に失敗しました。
java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
at org.eclipse.osgi.internal.loader.BundleLoader.findClassInternal(BundleLoader.java:501) ~[na:na]
at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:421) ~[na:na]
at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:412) ~[na:na]
at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:107) ~[na:na]
at java.lang.ClassLoader.loadClass(ClassLoader.java:357) ~[na:1.8.0]
at java.lang.Class.forName0(Native Method) ~[na:1.8.0]
at java.lang.Class.forName(Class.java:259) ~[na:1.8.0]
at org.openhab.binding.ACDBCommon.db.ServerInfo.openConnection(ServerInfo.java:102) ~[na:na]
at org.openhab.binding.ACDBCommon.db.ServerInfo.getConnection(ServerInfo.java:87) ~[na:na]
at org.openhab.binding.ACDBCommon.db.DBManager.select(DBManager.java:70) ~[na:na]
at org.openhab.binding.ACDBCommon.internal.ACDBBinding.execute(ACDBBinding.java:117) ~[na:na]
at org.openhab.core.binding.AbstractActiveBinding$BindingActiveService.execute(AbstractActiveBinding.java:156) [org.openhab.core_1.7.1.jar:na]
at org.openhab.core.service.AbstractActiveService$RefreshThread.run(AbstractActiveService.java:173) [org.openhab.core_1.7.1.jar:na]
Kazuhiro,
I got it working. Had to update your sqlijdbc4 jar to the latest version. For some reason the one on GitHub is missing a lot of classes. Is is possible to use your binding in the rules or scripts? I need to pass a parameter from the rules if possible.
Thanks.
Nathan,
I’m very sorry. My database access bindings need common part. Later, I upload common part binding named org.openhab.binding.ACDBCommon.
Thanks.
Thanks. I cannot seem to get the parameter portion to work. Keeps saying something like a value was not provided for parameter 1.
Kazuhiro,
I am really close to getting this working. Can you provide a better example of how to pass a parameter value into the items? Ideally I would like this to come from the rules.
Thanks.
Nathan,
Simple example is described below. Please try.
(1)openhab.conf
acsqlserver:url=jdbc:sqlserver://192.168.8.1:1443;databaseName=SAMPLE_DB;
acsqlserver:user=kaz
acsqlserver:password=kaz
(2)items
Number Data1 {acsqlserver="<[SELECT Value FROM TABLE1 WHERE recordId=1], >[UPDATE TABLE1 SET value=? WHERE recordId=1]"}
(3)sitemap
Setpoint item=Data1 minValue=0 maxValue=100 step=10
Kazuhiro,
I tried that but couldn’t get it to work. Can you provide an example that just returns a value vs. updating a value?
Something like Select Value from Table where Filter = ?.
I can get it to work if i hardcode the filter like Select Value from Table where Filter = 1
Thanks,
Nathan
Update…
I did get the parameter value working on an update command. I could not get it to work on the select command. Kept saying value was not specified.
I can get my project to work this way.
Thanks.
Nathan,
Can you tell me your table structure? If you can, I will describe sample item configuration.
Thanks,
Kaz
I am have an issue with the Binding the error is
015-11-24 09:56:12.697 [ERROR] [o.o.b.A.internal.ACDBBinding ] - ACSQLServer:???
java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
at org.eclipse.osgi.internal.loader.BundleLoader.findClassInternal(BundleLoader.java:501) ~[na:na]
at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:421) ~[na:na]
at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:412) ~[na:na]
at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:107) ~[na:na]
at java.lang.ClassLoader.loadClass(Unknown Source) ~[na:1.8.0_65]
at java.lang.Class.forName0(Native Method) ~[na:1.8.0_65]
at java.lang.Class.forName(Unknown Source) ~[na:1.8.0_65]
at org.openhab.binding.ACDBCommon.db.ServerInfo.openConnection(ServerInfo.java:102) ~[na:na]
at org.openhab.binding.ACDBCommon.db.ServerInfo.getConnection(ServerInfo.java:87) ~[na:na]
at org.openhab.binding.ACDBCommon.db.DBManager.select(DBManager.java:70) ~[na:na]
at org.openhab.binding.ACDBCommon.internal.ACDBBinding.execute(ACDBBinding.java:117) ~[na:na]
Any help would be great.
Allen_Farmer,
Please check there is SQLServerDriver named sqljdbc4-4.0.jar in openhab/server/configuration/org.eclipse.osgi/bundles/xxx/1/.cp/lib/ .
Where xxx is depend upon your installation.
Regards,
Kaz
Kazuhiro,
Yes sqlijdbc4-4.0.jar is there along with commons-lang3-3.3.2.jar and org.openhab.binding.ACDBCommon-1.8.0-snapshot.jar. Is there anything else that I can check?
Allen_Farmer,
I found sqljdbc4-4.0.jar in my github was broken. Please download sqljdbc4-4.0.jar from http://maven.lib.umd.edu/maven2/com/microsoft/sqlserver/sqljdbc4/4.0/ and place it in org.openhab.binding.ACSQLServer/lib. Then build binding with ‘mvn clean build’. The artifact is ‘target/org.openhab.binding.ACSQLServer-1.8.9-SNAPSHOT.jar’ and copy to your addons directory.
I think it will work fine.
Regards,
Kaz
Kazuhiro,
Sorry it took so long to get back to you. I was able to correct the issue by rebuilding the binding. But I have one more question: How do you run a Insert statement. My query as follows will error with Incorrect syntax near ‘]’
INSERT INTO [dbo].MessagesVALUES(‘0014f5400002’,‘2016-03-02 14:05:24.000’,2,0,0,0)
Switch gate {acsqlserver="<[INSERT INTO WWMS.dbo.Messages(MACAddress,Timestamp,TypeID,Taken,Attempts,IsGoodSend]VALUES(‘0014f5400002’,‘2016-03-02 14:05:24.000’,2,0,0,0)",autoupdate=“false”}