Sample script to access SQLite database

[ScottBot]

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.

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

[markd]

Wow, and I thought I was daring when I used to tweak the MDB with external tools between runs.

Hey, the rss feed seems to be dead. Is this a permanent thing, ie, should I remove it? I only realized it recently, so I've missed at least couple of months of "stuff".

[ScottBot]

Hey, the rss feed seems to be dead. Is this a permanent thing, ie, should I remove it? I only realized it recently, so I've missed at least couple of months of "stuff".

The URL may have changed a while back. This URL works for me.