SQLite database of thermostat changes - support thread

[nfrobertson]

The latest version of this plugin 3.0.3.2, has added initial support for SQLite to capture changes to thermostat(s) over time. This support is still preliminary. Eventually I hope to create graphs from this data as a value-add option with this plugin.

I'm opening this thread for anyone who may have problems specifically with the SQLite support. All of it is protected with try/catch blocks so this should cause no issues with current functionality.

Nathan

[automateme]

3.0.3.3 Startup Errors

Nathan,

I updated to version 3.0.3.3 and I see a few startup errors in the log. My Tstats appear to be working, but I will keep an eye on it.

Sep-15 8:09:42 PM Insteon Thermostat Error Unable to insert into SQLite table: Upstairs Perhaps you have an old format DB file? : SQL logic error or missing database table Upstairs has 10 columns but 3 values were supplied
Sep-15 8:09:42 PM Insteon Thermostat Registered Insteon Thermostat Upstairs with address: 14.F7.44
Sep-15 8:09:41 PM Insteon External device name Upstairs (Address:14.F7.44) has registered as external device #3
Sep-15 8:09:40 PM Insteon Device Upstairs (14.F7.44) was not found in the externally registered device list.
Sep-15 8:09:40 PM Insteon Thermostat Error Unable to insert into SQLite table: South Hallway Perhaps you have an old format DB file? : SQL logic error or missing database table SouthHallway has 10 columns but 3 values were supplied
Sep-15 8:09:40 PM Insteon Thermostat Registered Insteon Thermostat South Hallway with address: 14.FA.69
Sep-15 8:09:39 PM Insteon External device name South Hallway (Address:14.FA.69) has registered as external device #2
Sep-15 8:09:38 PM Insteon Device South Hallway (14.FA.69) was not found in the externally registered device list.
Sep-15 8:09:38 PM Insteon Thermostat Error Unable to insert into SQLite table: North Hallway Perhaps you have an old format DB file? : SQL logic error or missing database table NorthHallway has 10 columns but 3 values were supplied
Sep-15 8:09:38 PM Insteon Thermostat Registered Insteon Thermostat North Hallway with address: 14.F8.22
Sep-15 8:09:37 PM Insteon External device name North Hallway (Address:14.F8.22) has registered as external device #1
Sep-15 8:09:36 PM Insteon Device North Hallway (14.F8.22) was not found in the externally registered device list.

[nfrobertson]

I merged your new thread into the SQLite support thread I had opened as I knew at least 1 or 2 people would hit this situation due to using a pre-release plugin version while doing other research.

The easiest fix is to stop the plugin and go find and delete the SQLite DB file that was created from a previous version then restart the plugin which will create the file again new in the correct format. You will find it under HS3\Data\InsteonThermostat.db3

Here's my full path on my windows 7 machine:
C:\Program Files (x86)\HomeSeer HS3\Data\InsteonThermostat.db3

Also worth noting that the SQLite table names don't actually have whitespace in them. That error message used the unmodified thermostat name while the actual SQLite operaitons trim all whitespace. I'll fix that message in the next release.

[automateme]

Thanks Nathan. Sorry I missed that in the other thread. Your fix worked perfectly and eliminated the errors on startup. The graphs sound really exciting - to track things like the amount of time the A/C's run every day, and in what part of the house - temperature/humidity trends, etc.

[claude]

Nathan,

HS3: 3.0.0.198
Insteon: 3.0.5.28
Thermostat: 3.0.3.3

I'm still seeing the following errors; I've deleted the DB and restarted a few times. I should also mention I'm running HS3 as a service, although I don't believe this would be an issue. The DB gets created each time but stays at 0 bytes. The 2441TH reports fine.

Sep-17 14:01:43 Insteon Thermostat Error Error creating SQLite table: 2441TH : SQL logic error or missing database unrecognized token: "2441TH"
Sep-17 14:01:43 Insteon Thermostat Error Unable to insert into SQLite table: 2441TH Perhaps you have an old format DB file? : SQL logic error or missing database unrecognized token: "2441TH"

[nfrobertson]

Thanks for the catch Claude!

I just posted v3.0.3.4 which is to address this issue. The SQLite table names need to be quoted if they don't start with a letter. That has been fixed.

Nathan

[claude]

3.0.3.4 did it for me, thanks!