HomeSeer MySQL device status

Homeseer Forum in het Nederlands (DUTCH forum)

Moderator: Ruud

Post Reply
raymonvdm
Senior Member
Senior Member
Posts: 1153
Joined: Sun Dec 18, 2011 1:23 am

HomeSeer MySQL device status

Post by raymonvdm »

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
AshaiRey
Senior Member
Senior Member
Posts: 1310
Joined: Mon Feb 02, 2009 5:27 pm
Location: Netherlands
Contact:

Re: HomeSeer MySQL device status

Post by AshaiRey »

Sorry, maar wat is nu de vraag?
Bram
raymonvdm
Senior Member
Senior Member
Posts: 1153
Joined: Sun Dec 18, 2011 1:23 am

Re: HomeSeer MySQL device status

Post by raymonvdm »

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
User avatar
Irritanterik
Member
Member
Posts: 82
Joined: Tue Apr 19, 2011 1:00 pm
Location: Amersfoort
Contact:

Re: HomeSeer MySQL device status

Post by Irritanterik »

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
raymonvdm
Senior Member
Senior Member
Posts: 1153
Joined: Sun Dec 18, 2011 1:23 am

Re: HomeSeer MySQL device status

Post by raymonvdm »

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: 1153
Joined: Sun Dec 18, 2011 1:23 am

Re: HomeSeer MySQL device status

Post by raymonvdm »

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
User avatar
Irritanterik
Member
Member
Posts: 82
Joined: Tue Apr 19, 2011 1:00 pm
Location: Amersfoort
Contact:

Re: HomeSeer MySQL device status

Post by Irritanterik »

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
raymonvdm
Senior Member
Senior Member
Posts: 1153
Joined: Sun Dec 18, 2011 1:23 am

Re: HomeSeer MySQL device status

Post by raymonvdm »

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
User avatar
Willem4ever
Global Moderator
Global Moderator
Posts: 805
Joined: Mon Oct 30, 2006 3:48 pm
Location: Uithoorn / Netherlands

Re: HomeSeer MySQL device status

Post by Willem4ever »

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 ....
AshaiRey
Senior Member
Senior Member
Posts: 1310
Joined: Mon Feb 02, 2009 5:27 pm
Location: Netherlands
Contact:

Re: HomeSeer MySQL device status

Post by AshaiRey »

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
Post Reply

Return to “Homeseer algemeen Forum”