HomeSeer MySQL device status

Homeseer Forum in het Nederlands (DUTCH forum)

Moderator: Ruud

HomeSeer MySQL device status

Postby raymonvdm » Tue Nov 06, 2012 3:18 pm

In the past i used Domotiga and a php script to pull device status data from the database. This way i could create Cacti graphs of my device status

Now i`m using HomeSeer and would like to achieve the same. I have found the following script

http://www.iease.nl/2012/02/15/homeseer-mysql-2-2/

But i get the following error

Code: Select all
6-11-2012 15:14:15  - Error - Script compile error: Het type MySqlConnection is niet gedefinieerd.on line 21


C:\Program Files\HomeSeer HS2\Scripts\statusCallBack.vb

------------------------------------------------

Code: Select all

Imports System.Data
Imports MySql.Data.MySqlClient

Dim Public ID As String = "statusCallBack.vb"
Sub checkStatus(ByVal aParm as Object)
dim stat, hc, dc, hdc,refID, sName as String

Dim conn As MySqlConnection
Dim myCommand As New MySqlCommand
Dim varDate as String
Dim varTime as String
Dim varDeviceName as String
Dim varDeviceCode as String

'---------------------------------------------------------------
'PLACE THIS STATEMENT IN YOUR STARTUP SCRIPT
' hs.RegisterStatusChangeCB "statusCallBack.vb","checkStatus"
'---------------------------------------------------------------
hc = aParm(0)
dc = aParm(1)
stat = aParm(2)
refID = aParm(3)
hdc = hc & dc

Dim device
Dim dn
Dim type as String
Dim tijd

tijd = Datepart("yyyy",Now()) & "-" & Right("0" & DatePart("m",Now()),2) & "-" & Right("0" & DatePart("d",Now()),2) & " " & Right("0" & DatePart("h",Now()),2) & ":" & Right("0" & DatePart("n",Now()),2) & ":" & Right("0" & DatePart("s",Now()),2)

  device = hs.GetDeviceByRef(refID)

  dn = device.name

'
'Detect type of device
'

Select Case (hc)

  Case "#"
     type = "temp"

  Case "$"
     type = "minmax"

  Case "["
     type = "ping"

  Case "A"
     type = "Schakelaar"

  Case "C"
     type = "Schakelaar"

  Case "H"
     type = "Schakelaar"

  Case "K"
     type = "Schakelaar"

  Case "P"
     type = "Schakelaar"

  Case "R"
     type = "Remeha Calenta"

  Case "W"
     type = "Weer"

  Case "Z"
     type = "Server"

  Case "_"
     type = "Plugwise"

End Select

'hs.writelog(ID,"House Code: " & hc & " Device Code " & dc & " Status: " & stat & " Device Value: " & hs.DeviceValue(hdc) & " Device String: " & hs.DeviceString(hdc) & " Name: " & dn & " Type: " & type & " Time: " & tijd)

conn = New MySqlConnection()
conn.ConnectionString = "server=localhost; user id=xxxxxxx; password=xxxxxxxx; database=HomeSeerDB"

Try
    conn.Open()
    'hs.writelog ("mySQL", "Connection Opened Successfully!")
    myCommand.CommandText = "INSERT INTO tbldevices (hc,devicename,type,dc,value,status,ts)" & " VALUES ('" & hc & "','" & dn & "','" & type & "','" & dc & "','" & hs.DeviceValue(hdc) & "','" & stat & "','" & tijd & "')"
    myCommand.Connection = conn
    myCommand.ExecuteNonQuery()
    'hs.writelog ("mySQL", "Logged to database: " & varDeviceName & ", " & varDeviceCode & ", " & iStatus)
    conn.Close()
    Catch myerror As MySqlException
    hs.writelog ("mySQL", "Error Connecting to Database: " & myerror.Message)
End Try

End Sub



--------------------------------------------------------------------------------------------------------------------

C:\Program Files\HomeSeer HS2\Scripts\Startup.txt

De volgende regel toegevoegd net boven End Sub

Code: Select all
hs.RegisterStatusChangeCB "statusCallBack.vb","checkStatus"
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
raymonvdm
Senior Member
Senior Member
 
Posts: 1152
Joined: December 2011

Re: HomeSeer MySQL device status

Postby AshaiRey » Tue Nov 06, 2012 4:27 pm

Sorry, maar wat is nu de vraag?
Bram
AshaiRey
Senior Member
Senior Member
 
Posts: 1304
Joined: February 2009
Location: Netherlands

Re: HomeSeer MySQL device status

Postby raymonvdm » Tue Nov 06, 2012 4:45 pm

I want to resolve the issue that the script is sending me the following error

Code: Select all
6-11-2012 15:14:15  - Error - Script compile error: Het type MySqlConnection is niet gedefinieerd.on line 21


I think my Windows XP is missing some MySQL software but i installed the following .net connector

http://www.automatedhomeonline.com/Home ... QL5.1.html
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
raymonvdm
Senior Member
Senior Member
 
Posts: 1152
Joined: December 2011

Re: HomeSeer MySQL device status

Postby Irritanterik » Wed Nov 07, 2012 9:26 am

I found this on the Homeseer forum:

1. Downloaded and installed the Connector/Net from http://www.mysql.com/downloads/connector/net/
2. Copied MySql.Data.dll (from MySQL\MySQL Connector Net 6.4.4\Assemblies\v4.0) to the HomeSeer HSPRO folder
3. Updated the settings.ini file (in Config folder) to include: ScriptingReferences=System.Drawing;System.Drawing.dll,System .XML;System.XML.dll,System.Web;System.Web.dll,System.Data;sy stem.Data.dll,MySql.Data.MySqlClient;MySql.Data.dll


Source: http://board.homeseer.com/showthread.php?p=992569
HP t5630w with Homeseer 2 > Will migrate to Homey | RFXLAN | IRTrans | Opentherm Gateway | ITHO ECO-FAN integration | Harrison Curtain | Z-wave
User avatar
Irritanterik
Member
Member
 
Posts: 82
Joined: April 2011
Location: Amersfoort

Re: HomeSeer MySQL device status

Postby raymonvdm » Wed Nov 07, 2012 10:04 am

That seems to do the trick, now i need to find out how to create the database with the needed columns
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
raymonvdm
Senior Member
Senior Member
 
Posts: 1152
Joined: December 2011

Re: HomeSeer MySQL device status

Postby raymonvdm » Wed Nov 07, 2012 11:40 am

I`m trying the following database

Code: Select all


-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 11, 2010 at 11:05 PM
-- Server version: 5.1.37
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `homeseerdb2`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbldevices`
--

CREATE TABLE IF NOT EXISTS `tbldevices` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `hc` varchar(1) DEFAULT NULL,
  `devicename` varchar(40) DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL,
  `dc` varchar(3) DEFAULT NULL,
  `value` varchar(10) DEFAULT NULL,
  `status` int(10) DEFAULT NULL,
  `ts` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pkID2` (`id`),
  KEY `idcDC2` (`dc`),
  KEY `idxHC2` (`hc`),
  KEY `idxTS2` (`ts`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3231458 ;

Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
raymonvdm
Senior Member
Senior Member
 
Posts: 1152
Joined: December 2011

Re: HomeSeer MySQL device status

Postby Irritanterik » Wed Nov 07, 2012 2:36 pm

I'm using a 'FLOAT'-field for device value, so temperatures and other values can be saved correctly without losing the decimals.
HP t5630w with Homeseer 2 > Will migrate to Homey | RFXLAN | IRTrans | Opentherm Gateway | ITHO ECO-FAN integration | Harrison Curtain | Z-wave
User avatar
Irritanterik
Member
Member
 
Posts: 82
Joined: April 2011
Location: Amersfoort

Re: HomeSeer MySQL device status

Postby raymonvdm » Tue Nov 13, 2012 9:14 pm

I already change it to Float.

Now i`m looking for a change to the script so that every status update is a database update and not an insert. To keep the database small and simple. The updates of temp devices is not working because of the following error

Code: Select all
mySQL - Error Connecting to Database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Woonkamer T','RFXcom','3','191','2','2012-11-13 21:32:01')' at line 1



I`m also looking for php scripts to poll and show status of the devices
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
raymonvdm
Senior Member
Senior Member
 
Posts: 1152
Joined: December 2011

Re: HomeSeer MySQL device status

Postby Willem4ever » Tue Nov 13, 2012 10:18 pm

Bit difficult like this, why don't you print the entire insert/update string before you attempt to do the actual sql command. This will aid in debugging ....
User avatar
Willem4ever
Global Moderator
Global Moderator
 
Posts: 804
Joined: October 2006
Location: Uithoorn / Netherlands

Re: HomeSeer MySQL device status

Postby AshaiRey » Wed Nov 14, 2012 11:01 am

raymonvdm wrote:Now i`m looking for a change to the script so that every status update is a database update and not an insert. To keep the database small and simple. The updates of temp devices is not working because of the following error

Code: Select all
mySQL - Error Connecting to Database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Woonkamer T','RFXcom','3','191','2','2012-11-13 21:32:01')' at line 1


Raymon
I don't mean to be rude or so but i think it's better that you have a look at some tutorials first about SQL. What you want to accomplish here is rather strange to do with a database. You want to update records on status updates instead of inserting them. This means you don't need the history of the device and using a DB for that is a huge overkill. Again i just want to help here so bare with me please.

Now i`m looking for a change to the script so that every status update is a database update and not an insert

This is a lot easier if you use a ini file and hs.GetINISetting() and hs.SetINISetting() or even use a virtual device for it.

Code: Select all
mySQL - Error Connecting to Database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Woonkamer T','RFXcom','3','191','2','2012-11-13 21:32:01')' at line 1

This line tells you that there is something wrong in your sql string and around the mentioned point. Although SQL is helpfull it is sometimes not right about where the error occures. There can be something wrong further down the line. For example HC can be a value that need to be placed in [ ] to prevent them from being interpeted as a keyword or special character (as you use in Type). Sometimes it help to make a SQL string first and give that to CommandText
Example:
Code: Select all
    Dim strSQL as String = ""
'  Note: I removed the " & "  between ) and VALUES. There is no need for that
    strSQL = "INSERT INTO tbldevices (hc,devicename,type,dc,value,status,ts) VALUES ('" & hc & "','" & dn & "','" & type & "','" & dc & "','" & hs.DeviceValue(hdc) & "','" & stat & "','" & tijd & "')"
    hs.writelog ("mySQL", "strSQL: " & strSQL) 'this will tell you what you send to the server
    myCommand.CommandText = strSQL
    hs.writelog ("mySQL", "strSQL executed")


BUT best thing for last. This error can also mean that there is an error in the connectionstring because that is what it tells you.
And if i look at your example earlier i think there is one there.
Have a look here: http://www.connectionstrings.com/mysql

Good luck with debugging!
Bram
Bram
AshaiRey
Senior Member
Senior Member
 
Posts: 1304
Joined: February 2009
Location: Netherlands


Return to Homeseer algemeen Forum

Who is online

Users browsing this forum: No registered users and 1 guest