Announcement

Collapse
No announcement yet.

Enter a device value in MySQL

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

    Enter a device value in MySQL

    Trying to write a script that takes a device value from within the script and enter these values in MySQL table. Does anyone have a script that I can customize for my need?

    #2
    Here you go, let me know if you need any help with anything. I do include a reference in the settings.ini file to the SQL library:
    HS 3.0.0.548: 1990 Devices 1172 Events
    Z-Wave 3.0.1.262: 126 Nodes on one Z-Net

    Comment


      #3
      Originally posted by sparkman View Post
      Here you go, let me know if you need any help with anything. I do include a reference in the settings.ini file to the SQL library:

      Code:
      ScriptingReferences=System.Data.SQLite;C:\Program Files (x86)\HomeSeer HS3\Bin\System.Data.SQLite.dll
      Code:
      Imports System.Data.SQLite
      Imports System.IO
      
      Sub Main(ByVal Parms As String)
      
          Dim logName As String = "Log to DB"                        'set log type for HS log
          Dim Debug As Boolean = False                            'set to True if the script give you errors and it will provide additional info in the HS3 log to help troubleshoot
      
          Try 
              Dim ParmArray() As String = Split(Parms,"|")        'split parameter into an array
              Dim refID As Integer = ParmArray(0)                    'the first parameter is the reference id of the device    
              Dim loggingFileName As String = lcase(ParmArray(1))    'the second parameter is the file name to log too
      
              Dim CurDate As String = Now.ToString("yyyy-MM-dd")
              Dim CurTime As String = Now.ToString("hh:mm:ss tt")
              Dim CurValue As String = CStr(hs.DeviceValueEx(refID))
      
              Dim mydb As String = hs.GetAppPath & "/data/" & loggingFileName & ".db"
      
              If Debug Then hs.writelog(logName,mydb)
              If Debug Then hs.writelog(logName,CurDate & "," & CurTime & "," & CurValue)
      
              Dim insertResult As Integer = dbInsert(CurDate, CurTime, refID, CurValue, mydb)
              If Debug Then hs.writelog(logName,CStr(insertResult))
      
          Catch ex As Exception
              hs.WriteLog(logName, ex.Message)
          End Try
      
      End Sub
      
      Private Function dbInsert(CurDate As String, CurTime As String, refID As Integer, CurValue As Double, db As String) As Integer 
      
          Dim logName As String = "Log to DB SQL"
          Dim Debug As Boolean = False
          Dim cnn As SQLiteConnection = Nothing 
          Dim comm As SQLiteCommand = Nothing 
          Dim OSType As String = hs.GetOSType
      
          If Debug Then hs.writelog(logName,db)
          Dim id As Integer = 0 
      
          'confirm that database file exists.  Create if not.
          Try 
              If Not File.Exists(db) Then
                  Dim SQLconnect As New SQLite.SQLiteConnection()
                  Dim SQLcommand As SQLiteCommand
                  SQLconnect.ConnectionString = "Data Source=" & db & ";Version=3;"
                  SQLconnect.Open()
                  SQLcommand = SQLconnect.CreateCommand
                  'SQL query to Create Table
                  SQLcommand.CommandText = "CREATE TABLE devLog(EventDate TEXT, EventTime TEXT, DeviceRefID INTEGER, DeviceValue REAL);"
                  SQLcommand.ExecuteNonQuery()
                  SQLcommand.Dispose()
                  SQLconnect.Close()
              End If
          Catch ex As Exception 
              hs.WriteLog(logName, ex.Message)
          End Try 
      
          'insert event into database
          Try 
              cnn = New SQLiteConnection("Data Source=" & db & ";Version=3;") 
              cnn.Open() 
              comm = New SQLiteCommand("insert into devLog(EventDate,EventTime,DeviceRefID,DeviceValue) values (@EventDate,@EventTime,@DeviceRefID,@DeviceValue);select last_insert_rowid();", cnn) 
              comm.Parameters.AddWithValue("@EventDate", CurDate) 
              comm.Parameters.AddWithValue("@EventTime", CurTime) 
              comm.Parameters.AddWithValue("@DeviceRefID", refID) 
              comm.Parameters.AddWithValue("@DeviceValue", CurValue) 
              id = comm.ExecuteScalar() 
          Catch ex As Exception 
              hs.WriteLog(logName, ex.Message)
          Finally 
              If (Not comm Is Nothing) Then comm.Dispose() 
              If (Not cnn Is Nothing) Then cnn.Close() 
          End Try 
          Return id 
      
      End Function
      Thanks, this looks like more toward sqllite. I was looking for. Mysql.

      Aldo

      Sent from my SM-G935V using Tapatalk

      Comment


        #4
        Originally posted by alphatech View Post
        Thanks, this looks like more toward sqllite. I was looking for. Mysql.

        Aldo
        Sorry, saw sql and assumed sqlite. Should put my reading glasses on next time 😂
        HS 3.0.0.548: 1990 Devices 1172 Events
        Z-Wave 3.0.1.262: 126 Nodes on one Z-Net

        Comment


          #5
          Originally posted by sparkman View Post

          Sorry, saw sql and assumed sqlite. Should put my reading glasses on next time 😂
          Not a problem, same here 😁 I'm actually looking to read a device from within the script and put it into mysql. I think I have something already to build the insert statement but I'm nut sure how to read the device in a script.

          Sent from my SM-G935V using Tapatalk

          Comment


            #6
            Originally posted by alphatech View Post
            Not a problem, same here 😁 I'm actually looking to read a device from within the script and put it into mysql. I think I have something already to build the insert statement but I'm nut sure how to read the device in a script.
            In my script, I pass the reference ID of the device I want to insert its value into the database. Then the reference ID is used this way:

            Code:
             
             Dim CurValue As String = CStr(hs.DeviceValueEx(refID))
            That HS function and others similar to it are described in the help file here: http://help.homeseer.com/help/HS3/st...or_last_change
            HS 3.0.0.548: 1990 Devices 1172 Events
            Z-Wave 3.0.1.262: 126 Nodes on one Z-Net

            Comment

            Working...
            X