Short Tutorial on MySql and Homeseer Integration

[sdanks]

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 www.MySql.org and download the database program. (Free - Did I mention Free?)
2. Go to www.MySql.org/MyODBC 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
Server: 192.168.1.200
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
'chapter.

'Open the database connection

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

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.
objRS.MoveNext
loop

'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

objRS.Close
Set objRS = Nothing
objConn.Close
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.

[Michael McSharry]

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.

[JLehnert]

Thank you. Very informative.

[sdanks]

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.

[jon00]

Scott,

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

[sdanks]

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?

[johnwpb]

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.