No announcement yet.

Short Tutorial on MySql and Homeseer Integration

  • Filter
  • Time
  • Show
Clear All
new posts

    Short Tutorial on MySql and Homeseer Integration

    I wanted to write a little tutorial for those of you who want to use
    a MySql database to store and retrieve data from inside Homeseer. I
    spent about 3 hours last night making this work. I'm not a programmer
    and certainly someone could take this and add or change things to make
    it better, but I didnt really have a step by step idea of how to make
    this happen. So here is what I found had to happen:

    1. Go to and download the database program. (Free - Did I mention Free?)
    2. Go to and get the ODBC drivers. (Free Again.)
    3. Install both of these. If you want to put MySql on a
    different pc on your network, that is fine, but the
    MyODBC drivers need to be installed on the pc you are
    running Homeseer on. ( You must use this to provide the connection
    to the database - It's how the vbscripting connects to the database.)
    4. Go to start, Settings, control panel on the Homeseer pc.
    I am using Win2KPro.
    5. Go to administrative Tools
    6. Go to Data Sources (ODBC) Connections
    7. Go to System DSN tab
    8. Click on Add
    9. Go down to MySql ODBC Drivers (I used 3.51) (This will appear after
    installing the MyODBC drivers from above.)
    10. Add this driver and fill in the answers as to how you setup your database.
    for example, I used:
    Data Source Name: Bible
    Description: ASV Bible
    User: root
    Password: ************
    Database: bible
    (Of course the database needs to have been setup - instructions on
    this are on the MySql site. They have several tutorials)
    11. Click on the Test button and you should get a message saying that
    it connected sucessfully.
    12. Go to Homeseer pc.

    Here is a snipit of one of my script's code.

    'Bible Verse Script - Bible.txt
    'Opens MySql connection and queries
    'the database to get and speak one

    'Open the database connection

    Dim objConn
    Set objConn = CreateObject("ADODB.Connection")
    objConn.ConnectionString = "DSN=Bible"

    Dim objRS
    Set objRS = CreateObject ("ADODB.Recordset")

    objRS.Open "SELECT * FROM bibleasv WHERE bookid=1 and chapter=1", objConn

    sFull = ""

    do until objRS.EOF
    sFull = sFull & objRS.Fields.item(5) & " "
    'The 5th field is the actual verse text. the first 4 are book, chapter etc.

    'MsgBox sFull
    'Speaking takes a long time. uncomment the above to test quicker.
    'and comment to line below until it works like you want it to.
    hs.speak sFull

    Set objRS = Nothing
    Set objConn = Nothing

    This may seem elementary to some of you, but I had about 7 webpages open cross
    referencing scripts from the web to figure out how to do this. There are ALOT
    of web page and ASP examples, but it was hard to find vbscripting examples to do this.

    I hope this helps someone get an idea of how to connect to a MySql database to
    get going. I know there are alot more commands to put data in and alter the data
    that is there, and create tables and such, but I'm just learning and wanted to
    share what I found out. You can modify the SELECT statement to search the database,
    and return other values. But this is just an example. I found a database on the
    internet that is a MySql dump of the American Standard Version of the bible and
    my wife had the idea to have it speak a chapter a day when we wake up.
    So I will probably add a table to track what I have already played.

    Just think of all the things you can do now with database integration.
    * Security logs (iButtons)
    * Log files
    * Weather temperatures
    * CallerID lookups
    * Reminders
    * Phrases to speak (Maybe at random during seasonal times when people approach the house)
    * Lighting and Device lists

    See the power of this?

    Good Luck.

    -Scott Danks

    p.s. Any comments are welcome from those of you who are Gurus. or anyone else for that matter.
    A computer's attention span is as long
    as it's powercord.

    It is very considerate of you to give step-by-step instructions. Whenever starting out in a subject there is so much that needs to consolidated just to do something that appears to be simple.


      Thank you. Very informative.
      My system is described in my profile.


        You're welcome. I was kind of hoping that someone might add a little here and there, or provide different viewpoints if there is a better way. But hey, it works.
        A computer's attention span is as long
        as it's powercord.



          Thanks for doing this! It is especially useful as MySQL is free.

          May I suggest you post this in the scripts library once you are happy with the code as I fear this may get lost in the general discussion forum.

          Thanks again


            ummm, I'm not sure how to do this. Do I just post the message again in the scripts library or is there more to it?
            A computer's attention span is as long
            as it's powercord.


              Script Library

              First, THanks! I have been thinking about having a data base here to store that mass ammount of stuff that I generate with temperatures, logs, caller ID ect ect...

              To post it in the library, just put a sescription of what it is in the message, and zip up the documentation, and your sample script. This would be a great package to get people started.

              I know nothing about databases, and was leary to get into it as it is just something else to add to my plate Your step by step makes it look feasable, and will probably give it a try here soon.
              Visit My Home Seer Site at:
              Created with LCARS