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?
Announcement
Collapse
No announcement yet.
Enter a device value in MySQL
Collapse
X
-
Originally posted by sparkman View PostHere 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
Aldo
Sent from my SM-G935V using Tapatalk
Comment
-
Originally posted by sparkman View Post
Sorry, saw sql and assumed sqlite. Should put my reading glasses on next time 😂
Sent from my SM-G935V using Tapatalk
Comment
-
Originally posted by alphatech View PostNot 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.
Code:Dim CurValue As String = CStr(hs.DeviceValueEx(refID))
HS 3.0.0.548: 1990 Devices 1172 Events
Z-Wave 3.0.1.262: 126 Nodes on one Z-Net
Comment
Comment