Sample script to access SQLite database
Posted: Wed Apr 18, 2018 10:09 am
It's not advisable to access the HouseBot configuration database. However, for those who are bold and have a good idea of what they are doing (and have backups), here's one method to access the database from a HouseBot Script Device. Also, be aware that if data (e.g. a Property Value) is updated with an UPDATE statement, HouseBot will not recognize the change. To update Property Values, be sure to use the SetPropertyValue HouseBot API call.
First install the latest SQLite ODBC driver from here. During setup, go with all of the default options, and there's no need to install the "SQLite 2 Drivers" or "SQLite+TCC".
Here's a sample script that will connect to the database, read a record from the database and display in a message box, and increment the value of the Sample Theme Device.Volume Property.
First install the latest SQLite ODBC driver from here. During setup, go with all of the default options, and there's no need to install the "SQLite 2 Drivers" or "SQLite+TCC".
Here's a sample script that will connect to the database, read a record from the database and display in a message box, and increment the value of the Sample Theme Device.Volume Property.
Code: Select all
Set dbConn = CreateObject( "ADODB.Connection" )
' Set path to MDB file in variable
Dim pathToDB
pathToDB = "C:\Program Files\HouseBot\Config\HBData.db"
' Open connection to DB
dbConn.Open "DRIVER=SQLite3 ODBC Driver;LongNames=0;Timeout=1000;NoTXN=0; SyncPragma=NORMAL;StepAPI=0;Database=" & pathToDB
' Execute query for the Description of the System Time Device
Set rs = dbConn.Execute( "select Description from Devices where Name='System Time'" )
' Check the result
if (rs.EOF) Then
MsgBox( "No Data Found" )
Else
MsgBox( rs("Description") ) ' Show result in message box
' Get the Volume property of the sample theme device.
rs = dbConn.Execute( "select CurrentValue from DevicePropertyMap where DeviceID=3 and PropertyID=37" )
Dim newValue
newValue = rs("CurrentValue")+1
if (newValue > 99) Then
newValue = 1
End If
' Use SetPropertyValue API so HouseBot will receive the change.
SetPropertyValue "Sample Theme Device.Volume", newValue
End If