Announcement

**mrhappy** · January 19, 2015, 03:44 PM

It has been some years since I last used ADO with VB6 (I think) but I think ADODB is a COM interface? I think the ScriptingReferences is just for .Net http://homeseer.com/support/homeseer...namespaces.htm

Try taking a look at the System.Data.OleDb namespace, it should allow you to work with Access databases. I think I even wrote a script to query an Access DB to produce charts from mcsTemp some time ago.

**mrhappy** · January 19, 2015, 03:50 PM

And as if by magic I have found it, this code is somewhat unpleasant now I have looked at it (says I last messed with it in 2012 but I think it was some time before then I wrote it) but if you were looking at querying a database then it may help you.

Code:

Imports System.Data.OleDB
Imports System.Data
Imports System.Text

Sub Main(ByVal Parms As Object)

'### THESE DECLARATIONS (MIGHT) NEED AMENDMENT ###
Dim colGraphBackG As String = "DCDCDC" 'colour for the graph background - in Hex format
Dim ColLine As String = "FF0000"

    'colour for the line of the chart - in hex format
    Dim Nation As String = "US"
    Dim ChartTitle As String = "My HSTouch Chart"
    '### ###

    '### Leave These Alone ###
    Dim SensorID As String = ""
    Dim DBPath As String = hs.getapppath & "\mcsTemperature.mdb"
    Dim DivideByTime As Integer = 0

    Dim dS As New DataSet
    Dim conObj As New OleDbConnection
    Dim providerStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    Dim sourceStr As String = "Data Source=" & DBPath

    Dim StartDTime As String
    Dim FinishDTime As String
    Dim sD As New StringBuilder
    Dim sT As New StringBuilder
    Dim sG As New StringBuilder
    Dim MaxVal As Integer = 0
    Dim MinVal As Integer = 8000
    Dim usDateTimeFormat As DateTime
    Dim OffSet As Double = 0.5 'this is a +/- value for the axis, adjust to suit
    Dim DTFinish As DateTime
    Dim DTStart As DateTime
    Dim yAxisTStr As String = ""
    '### ###

    '<<< Time >>>
    StartDTime = "12/22/2011 00:01:00 AM"
    FinishDTime = "12/22/2011 10:00:00 PM"
    '<<<      >>>

    If Nation = "US" Then
        yAxisTStr = "h:mm tt" 'see http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx for example formats
    Else
        yAxisTStr = "HH:mm"
    End If



    If Parms.Length = 0 Then
        'this tests if the parameter passed is empty, if so it defaults to the family thermostat
        SensorID = "ThermoStatFamily"
    Else
        'if not it reads the value in and uses it as the SensorID
        For j As Integer = 0 To (Parms.Length - 1)
            SensorID = SensorID & Parms(j)
        Next
    End If

    If Nation = "US" Then
        DTStart = DateTime.Parse(StartDTime, System.Globalization.CultureInfo.GetCultureInfo("en-us"))
        DTFinish = DateTime.Parse(FinishDTime, System.Globalization.CultureInfo.GetCultureInfo("en-us"))
    Else
        DTStart = DateTime.Parse(StartDTime, System.Globalization.CultureInfo.GetCultureInfo("en-gb"))
        DTFinish = DateTime.Parse(FinishDTime, System.Globalization.CultureInfo.GetCultureInfo("en-gb"))
    End If


Dim Span As TimeSpan = DTFinish - DTStart

Select Case Span.Hours

Case <= 0
DivideByTime = 1
Case 0 to 6
DivideByTime = 1
Case 7 to 12
DivideByTime = 2
Case 13 to 18
DivideByTime = 5
Case 19 to 24
DivideByTime = 5
Case > 24
DivideByTime = 10
End Select

conObj.ConnectionString = providerStr & sourceStr

Try

conObj.Open

Dim SQLString As String = "SELECT " & SensorID & ", SampleDate, Minute(SampleDate) FROM Temperature WHERE SampleDate BETWEEN #" & StartDTime & "# AND #" & FinishDTime & "#"
Dim dA As OleDbDataAdapter = New OleDbDataAdapter(SQLString, conObj)

dA.Fill(dS, "TempTable")

If dS.Tables("TempTable").Rows.Count = 0 Then

hs.writelog("DBQ", "There is no data for this period in the database")

Else

'starts looping through the data in the dataset, adding values to a larger string to eventually pass to the google chart
'min/maxval are important for the later data scaling of the google chart

For i As Integer = 0 to (dS.Tables("TempTable").Rows.Count - 1)

If Nation = "US" Then
usDateTimeFormat = ds.Tables("TempTable").Rows(i).Item(1).ToString
Else
usDateTimeFormat = DateTime.Parse(ds.Tables("TempTable").Rows(i).Item(1).ToString, System.Globalization.CultureInfo.GetCultureInfo("en-gb"))
End If

If ds.Tables("TempTable").Rows(i).Item(2).ToString Mod DivideByTime = 0 Then
sD.Append((ds.Tables("TempTable").Rows(i).Item(0) / 100) & ",")
End If

If CInt(ds.Tables("TempTable").Rows(i).Item(0) / 100) > MaxVal Then
MaxVal = CInt(ds.Tables("TempTable").Rows(i).Item(0) / 100)
End If

If CInt(ds.Tables("TempTable").Rows(i).Item(0) / 100) < MinVal Then
MinVal = CInt(ds.Tables("TempTable").Rows(i).Item(0) / 100)
End If

If i = 0 then

sT.Append(usDateTimeFormat.ToString(yAxisTStr) & "|")

ElseIf i = (dS.Tables("TempTable").Rows.Count - 1) Then

sT.Append(dtFinish.ToString(yAxisTStr) & "|")

Else

Select Case Span.Hours

Case 0 to 2

If usDateTimeFormat.ToString("mm") = "30" Then
sT.Append(usDateTimeFormat.ToString(yAxisTStr) & "|")
ElseIf usDateTimeFormat.ToString("mm") = "15" Then
sT.Append(usDateTimeFormat.ToString(yAxisTStr) & "|")
End If

Case 2 to 12

If usDateTimeFormat.ToString("mm") = "00" Then
sT.Append(usDateTimeFormat.ToString(yAxisTStr) & "|")
End If

Case 13 to 24

If usDateTimeFormat.ToString("HH") Mod 5 = 0 Then
If usDateTimeFormat.ToString("mm") = "00" Then
sT.Append(usDateTimeFormat.ToString(yAxisTStr) & "|")
End If
End If

End Select

End If

'the above statement is for the y-axis Time value, if you print every time to the chart then you will fill the axis too much

Next

sD.Remove((sD.Length - 1), 1)
sT.Remove((sT.Length - 1), 1)

'removes the last comma from the stringbuilder strings
'starts now to build the actual string for the chart

With sG
        .Append("chart?cht=lc")
        .Append("&chs=300x300")
        .Append("&chds=" & (MinVal - OffSet) & "," & (MaxVal + OffSet))       
	.Append("&chd=t:" & sD.ToString)
        .Append("&chxt=x,y&chxl=0:|" & sT.ToString)
        .Append("&chxr=1," & (MinVal - OffSet) & "," & (MaxVal + OffSet))
	.Append("&chf=bg,s," & colGraphBackG)
	.Append("&chco=" & colLine)
	.Append("&chtt=" & ChartTitle)
End With

'hs.writelog("DBQ", "Length Of String: " & sg.Length & " characters")
'can embed it into the log for debugging, uncomment this line if required
hs.writelog("DBQ", "<img src='http://chart.apis.google.com/" & sG.ToString & "'>")
'this now downloads the image, it will save it to the root HS path (C:\Program Files\HomeSeer HSPRO\xxxx.png), the title of the image is the sensorID
'Dim Result As String = hs.getURLImageEx("http://chart.apis.google.com/", sG.ToString, hs.getapppath & "\html\" & SensorID & ".png")

'If Result <> "" Then
'hs.writelog("DBQ", "Error in download of image file from Google Charts: " & Result)
'End If

End If

Catch ex As Exception
hs.writelog("DBQ", "Unknown Error: " & ex.message)
End Try

End Sub

**iamjwk** · January 19, 2015, 03:50 PM

Hmm. my script is almost ready and is an updated of a homeseer v2 script. I developed it with tenScripting under visual studio 2012 and worked fine in that environment.

Not checked yet what difference is in commands/possibilities between oledb and ADODB, but I hope somebody can help me with settings.ini as will save me several hours tweaking my script and testing it again.

Also fetched adodb.dll from the bin folder of homeseer v3, suggesting it can be used

Regards,
Jan-Willem

**iamjwk** · January 19, 2015, 04:05 PM

Bits and pieces of my script showing the difference in commands. As you can see will cost me time if I need to change, presuming I can use the same tricks to get for example the ID of the last record etc.

So I hope somebody else nows if using ADODB is still possible and how to reference it from settings.ini. The homeseer v3 helptext is not usefull for that.

Code:

NOTE: incomplete example code 

   Dim objConn As ADODB.Connection
   Dim strDatabase As String
   Dim strConnectionString As String
   Dim rsDevices As ADODB.Recordset


    '** OPEN DATABASE **            
    strDatabase = Trim(hs.GetINISetting("Database", "Path", DefaultPath, jwSaveData_IniFile))
    objConn = CreateObject("ADODB.Connection")
    strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase
    objConn.Open(strConnectionString)
        
    '** READING INFO FROM table ***
    rsDevices = CreateObject("ADODB.Recordset")
    strSQL = "SELECT tblDevice.* FROM tblDevice WHERE (tblDevice.DeviceObsolete=False) " & _
             "ORDER BY tblDevice.HomeseerDeviceCategory, tblDevice.HomeseerDeviceRoom, tblDevice.HomeseerDeviceName "
    rsDevices.Open(strSQLdata, objConn, adOpenForwardOnly)

    SavedDeviceList(iSaved).HomeseerDeviceAddress = CheckForNull(rsDevices.Fields.Item("HomeseerDeviceAddress").Value, "")
    SavedDeviceList(iSaved).HomeseerDeviceInterface = CheckForNull(rsDevices.Fields.Item("HomeseerDeviceInterface").Value, "")
    etc.
    
    
    '*** SAVING DATA TO TABLE ***
    sql = "insert into " & DeviceDataTable & " (HomeseerDeviceReferenceID, HomeseerDeviceName, ReceivedDateTime, ReceivedValue, Remark) " & _
          "values (" & DeviceRef & ", '" & DeviceName & "', #" & Format(CurrentDateTime, "yyyy/MM/dd HH:mm:ss") & "#,'" & strNewValue & "','" & Remark & "')"    
    objConn.Execute(sql)
    'Get ID of just added data record
    rsTemp.Open("SELECT @@identity AS NewID", objConn)
    DataRecordID = rsTemp("NewID").Value
    rsTemp.Close()    
    
    'Save timespan to previous data record
    If PreviousDataRecordID <> jwNULLvalue Then
        strSQL = "UPDATE " & DeviceDataTable & " SET Duration_Minutes = " & Replace(CStr(TimeSpan), ",", ".") & " WHERE (ID=" & PreviousDataRecordID & ")"
        objConn.Execute(strSQL)
    End If
    'Save last value and data record ID to tblDevices
    strSQL = "UPDATE tblDevice SET DeviceLastSavedDate = #" & Format(CurrentDateTime, "yyyy/MM/dd HH:mm:ss") & "#, DeviceLastSavedValue = '" & NewValue & "', DeviceLastDataRecordID =" & DataRecordID & " " & _
             "WHERE (HomeseerDeviceReferenceID=" & DeviceRef & ") "
    objConn.Execute(strSQL)

**Cleavitt76** · January 19, 2015, 06:40 PM

I think MrHappy is right. ADODB is very old technology that was never intended for use in .Net beyond backwards compatibility for project upgraded from VB6. ADO.Net is the standard for .Net. I don't know of any way to make ADODB load in HS3 since it's not a .Net library.

For what it's worth, here is another example of ADO.Net / OleDb that includes logic to get the last record ID...

Code:

     Private Function DB_InsertChemicalPumpDose(ByVal recordDate As DateTime, ByVal doseChemical As String, _
        ByVal doseType As String, ByVal doseRuntime As Int32, ByVal doseVolume As Int32, ByVal actualDoseRuntime As Int32) As Int32
        
         Dim Conn As New OleDbConnection
         Dim CmdInsert As New OleDbCommand
        
         Try
            'Open database connection.
            Conn.ConnectionString = _DBConnString
            Conn.Open()

            'Prepare the database command.
            With CmdInsert
                .Connection = Conn
                .CommandType = Data.CommandType.Text
                .CommandText = "INSERT INTO ChemicalPumpDose " & _
                               "(RecordDate, DoseChemical, DoseType, DoseRuntime, DoseVolume, ActualDoseRuntime) " & _
                               "VALUES " & _
                               "(?, ?, ?, ?, ?, ?);"
                .Parameters.Add("", OleDbType.Date).Value = recordDate
                .Parameters.Add("", OleDbType.VarWChar).Value = doseChemical
                .Parameters.Add("", OleDbType.VarWChar).Value = doseType
                .Parameters.Add("", OleDbType.Integer).Value = doseRuntime
                .Parameters.Add("", OleDbType.Integer).Value = doseVolume
                .Parameters.Add("", OleDbType.Integer).Value = actualDoseRuntime
            End With
 
            'Execute the database command.
            Dim AffectedRows As Int32 = CmdInsert.ExecuteNonQuery()
 
            'Get the MS Access autonumber.
            CmdInsert.CommandText = "SELECT @@Identity;"
            Dim RecordID As Int32 = Convert.ToInt32(CmdInsert.ExecuteScalar)
 
             Conn.Close()
             Return RecordID
 
         Finally
            If Conn IsNot Nothing Then Conn.Dispose()
            If CmdInsert IsNot Nothing Then CmdInsert.Dispose()
        End Try
 
     End Function

Announcement

How to reference ADODB in settings.ini?

How to reference ADODB in settings.ini?

Comment

Comment

Comment

Comment

Comment