Announcement

Collapse
No announcement yet.

Enter a device value in MySQL

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

  • sparkman
    replied
    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

    Leave a comment:


  • alphatech
    replied
    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

    Leave a comment:


  • sparkman
    replied
    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 😂

    Leave a comment:


  • alphatech
    replied
    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

    Leave a comment:


  • sparkman
    replied
    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:

    Leave a comment:


  • alphatech
    started a topic Enter a device value in MySQL

    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?
Working...
X