Announcement

Collapse
No announcement yet.

SQLite database of thermostat changes - support thread

Collapse
This is a sticky topic.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLite database of thermostat changes - support thread

    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
    HS 3.0.0.435 (PRO)
    Hardware: Napco GEM-P9600 | VenstarT1800 w/Insteon 2441V adapter | Insteon PLM
    Plugins HS3: Napco Gemini (mine) | Insteon Thermostat (mine) | Insteon Plug-in (mnsandler) | HSTouch Server (HST)
    Platform: Windows 10 Pro 64bit, core2 duo 2.0Ghz, 4GB memory
    http://www.kazteel.com/

  • #2
    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.

    Comment


    • #3
      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.
      HS 3.0.0.435 (PRO)
      Hardware: Napco GEM-P9600 | VenstarT1800 w/Insteon 2441V adapter | Insteon PLM
      Plugins HS3: Napco Gemini (mine) | Insteon Thermostat (mine) | Insteon Plug-in (mnsandler) | HSTouch Server (HST)
      Platform: Windows 10 Pro 64bit, core2 duo 2.0Ghz, 4GB memory
      http://www.kazteel.com/

      Comment


      • #4
        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.

        Comment


        • #5
          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"

          Comment


          • #6
            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
            HS 3.0.0.435 (PRO)
            Hardware: Napco GEM-P9600 | VenstarT1800 w/Insteon 2441V adapter | Insteon PLM
            Plugins HS3: Napco Gemini (mine) | Insteon Thermostat (mine) | Insteon Plug-in (mnsandler) | HSTouch Server (HST)
            Platform: Windows 10 Pro 64bit, core2 duo 2.0Ghz, 4GB memory
            http://www.kazteel.com/

            Comment


            • #7
              3.0.3.4 did it for me, thanks!

              Comment

              Working...
              X