Announcement

Collapse
No announcement yet.

SQL Server Script - Please help understanding it

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

    SQL Server Script - Please help understanding it

    I found this script in one of the posts that I would like to implement on my system. I'm really bad in scripting, I can make small modifications and I'm able to read them but unfortunately not this one.
    My confusions are as follow:
    It looks like it would read from Parms but from my understanding
    1)it runs from start.vb and I do not see the author passing the parameters to it.
    2)I do not see a sequence in the parms as of parm1, parm2 and parm3, it is skipping parm3
    3) can I run it from an event instead of within the startup.vb and if is yes, how do I define the parms.
    4) it looks like it is missing a field in the sql statement but I think I have it figured out. Right now, I get an error when I run it from the event, not sure what this means
    Apr-24 8:43:04 AM Error 1 Running script C:\HomeSeer3\scripts\L2DB.vb :Exception has been thrown by the target of an invocation.Object variable or With block variable not set.
    Any help would be greatly appreciated.

    Aldo


    'VB.Net script to write Homeseer 3 values to SQL Server Express

    'Add this line to Homeseer\Config\Settings.ini
    ' ScriptingReferences=System.Data;System.Data.Dll

    ' Add this line to Homeseer\Scipts\Startup.ini
    ' hs.RegisterStatusChangeCB("L2DB.vb","Main")

    Imports System.Data.SqlClient

    Sub Main(ByVal Parms As Object)

    Dim device_name, device_id, dv, device_location, device_location2, deviceObj, device_type
    Dim hc, dc, dev_address, dn As String
    Dim device_value As Double
    Dim devRef As Integer
    Dim sdate, stime As String
    Dim cn As New SqlConnection("Server=192.168.2.25,1433;Database=Home;User ID=xx;Password=xxxxxx;Trusted_Connection=False;")

    hs.WriteLog("L2DB", "Script running")
    'hdc = Parms(0) 'address of device
    dev_address = Parms(1) 'address of device
    device_value = Parms(2) 'new value of device
    devRef = Parms(4) 'Device reference of the device

    'Device_id = hs.GetDeviceRef(hdc)
    deviceObj = hs.GetDeviceByRef(devRef)

    device_name = deviceObj.name(hs)
    device_location = deviceObj.location(hs)
    device_location2 = deviceObj.location2(hs)
    device_type = deviceObj.Device_Type_String(hs)
    'device_status = hs.DeviceStatus(devRef)
    sdate = format(Now, "dd-MM-yyyy")
    stime = format(Now, "HH:mm:ss")

    cn.Open()
    hs.WriteLog("L2DB", "Connection is open")
    Dim SQLComm As New SqlCommand("UPDATE devices SET date ='" & sdate & "',time='" & stime & "',type='" & device_type & "',name='" & device_name & "',value='" & device_value & "',location='" & device_location & "',location2='" & device_location2 & "' WHERE code='" & dev_address & "' " & _
    "IF @@ROWCOUNT=0 " & _
    "INSERT INTO devices(date,time,code,type,status,name,value,location,locat ion2) VALUES('" & sdate & "','" & stime & "','" & dev_address & "','" & device_type & "','" & device_name & "','" & device_value & "','" & device_location & "','" & device_location2 & "')", cn)
    SQLComm.ExecuteNonQuery()
    hs.WriteLog("L2DB", "Logged to Database: Address: " & dev_address & ", Device Name: " & device_name & ", New Value: " & device_value)
    cn.Close()
    hs.WriteLog("L2DB", "Connection is closed")
    End Sub

    #2
    This script didn't work for me initially. I had to do some modifications. I can post my script and you can try if you like. I'm not that great at scripts either, but I get by.

    Do you have the DB setup to receive the data already?

    Comment


      #3
      Thanks, that would be great, I tried everything I could. I have SQL already installed, if you could print the table structure that would be fantastic.
      Thanks,
      Aldo

      Sent from my SM-G935V using Tapatalk

      Comment


        #4
        I implemented this script long time ago with help of this group. I would like to exclude selective devices to be appended in the database. As I mentioned, i can hack a script but not sure how I start with it. I was thinking along the line of using a Case statement, what is your opinion? If agreed, where would you include it in the script? Any help would be appreciated.

        Thanks

        Comment


          #5
          mcsMQTT write any HS device value change to SQL Server (mySQL or InfluxDB). The user interface being a table of HS devices where a checkbox is used to select it for inclusion. Seems like an easier approach than mucking with a script.

          Comment


            #6
            Thank you Michael.

            Sent from my SM-S906U using Tapatalk

            Comment

            Working...
            X