Enter a device value in MySQL

[alphatech]

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?

[sparkman]

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:

[alphatech]

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:

ScriptingReferences=System.Data.SQLite;C:\Program Files (x86)\HomeSeer HS3\Bin\System.Data.SQLite.dll

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

[sparkman]

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 😂

[alphatech]

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

[sparkman]

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:

 
 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