Announcement

Collapse
No announcement yet.

HS3 write data to external SQL database ?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    HS3 write data to external SQL database ?

    Hi

    In HS2 it was possible via a plugin to write events and polling data to an underlying SQL database, how do I do that in HS3 ?

    Terkild

    #2
    I've done SQLite with a script, a relatively simple script at that as long as the correct DLL files were referenced. If you have a HS2 plugin that wrote the data to a DB then it is worth searching on here to see if either that plugin is being ported to HS3 or an equivalent is out there.

    Comment


      #3
      Originally posted by terkild View Post
      Hi

      In HS2 it was possible via a plugin to write events and polling data to an underlying SQL database, how do I do that in HS3 ?

      Terkild
      Several of Blade's plugins use Access for logging. Maybe he has a script or plugin he could share.
      Regards,
      Michael

      HS3, W10 Home, HSTouch, W800, Z-Stick+

      Comment


        #4
        I've used both SQLite and MySQL with HS3 - no problems.
        Any of the .NET examples online should work fine for you.

        SQLite is much easier to work with than MySQL though...
        HS4Pro on a Raspberry Pi4
        54 Z-Wave Nodes / 21 Zigbee Devices / 108 Events / 767 Devices
        Plugins: Z-Wave / Zigbee Plus / EasyTrigger / AK Weather / OMNI

        HSTouch Clients: 1 Android

        Comment


          #5
          Originally posted by rmasonjr View Post
          ...SQLite is much easier to work with than MySQL though...
          Amen to that, as someone who had messed with ADO in the past and all sorts of Access based VB interfaces SQLite was an absolute breeze to get working.

          Comment


            #6
            Originally posted by rmasonjr View Post
            I've used both SQLite and MySQL with HS3 - no problems.
            Any of the .NET examples online should work fine for you.

            SQLite is much easier to work with than MySQL though...
            Thanks, good to hear that it is possible, do you have a link to one of the .net examples, or would you share your own solution ?

            Comment


              #7
              Ok, i found hs.RegisterStatusChangeCB
              http://www.homeseer.com/support/home...uschangecb.htm

              I will try to write a script that inserts the Parameters to my sql database.

              Comment


                #8
                Here is one of my insert statements I use for SQLite. Prior to calling this, you must create the database and create the table. This is just the insert function:
                PHP Code:
                Public Function InsertRouter(db As Stringrouter As Stringipaddress As Stringusername As Stringpassword As Stringupdatefrequency As Integerdref As Integer) As Integer
                        Dim cnn 
                As SQLiteConnection Nothing
                        Dim comm 
                As SQLiteCommand Nothing
                        Dim id 
                As Integer 0
                        
                Try
                            
                Log("InsertRouter " dbLogType.LOG_TYPE_DEBUG)
                            
                cnn = New SQLiteConnection("Data Source=" db ";Version=3;")
                            
                cnn.Open()

                            
                comm = New SQLiteCommand("insert into routers(router,ipaddress,username,password,updatefrequency,parentdeviceref) values " _
                                                     
                "(@router,@ipaddress,@username,@password,@updatefrequency,@parentdeviceref);select last_insert_rowid();"cnn)
                            
                comm.Parameters.AddWithValue("@router"router)
                            
                comm.Parameters.AddWithValue("@ipaddress"ipaddress)
                            
                comm.Parameters.AddWithValue("@username"username)
                            
                comm.Parameters.AddWithValue("@password"password)
                            
                comm.Parameters.AddWithValue("@updatefrequency"updatefrequency)
                            
                comm.Parameters.AddWithValue("@parentdeviceref"dref)

                            
                Log("InsertRouter " comm.CommandTextLogType.LOG_TYPE_DEBUG)
                            
                Log("InsertRouter " router "," ipaddress "," username ",xxxxx," updatefrequency "," drefLogType.LOG_TYPE_DEBUG)
                            
                id comm.ExecuteScalar()
                        Catch 
                ex As Exception
                            Log
                ("InsertRouter Exception --> " ex.MessageLogType.LOG_TYPE_DEBUG)
                            Throw New 
                Exception("DBUtil: InsertRouter " ex.Message)
                        Finally
                            If (
                Not comm Is NothingThen comm.Dispose()
                            If (
                Not cnn Is NothingThen cnn.Close()
                            
                Log("InsertRouter finished."LogType.LOG_TYPE_DEBUG)
                        
                End Try
                        Return 
                id
                    End 
                Function 
                HS4Pro on a Raspberry Pi4
                54 Z-Wave Nodes / 21 Zigbee Devices / 108 Events / 767 Devices
                Plugins: Z-Wave / Zigbee Plus / EasyTrigger / AK Weather / OMNI

                HSTouch Clients: 1 Android

                Comment

                Working...
                X