Posts Tagged ‘sql database’

One of the questions ive been asked is how to I put my flash script to grab data from the SQL database and provide updates if the device is on or off etc.

Well the awnser is, I dont. Like mentioned before to have your flash talk directly to a sql database you will need additional components which I dont want to install $$$, such as cold fusion or some advanced Flash stuff.  Again I want speed and functionality, especially if its only for a few users.

So what I did was use a internal web server which is installed with VB 2008, to do all the talking for me, more like a middle man.  for example my flash program talks to this web page using the ‘load’ function which sends data using the POST commands (example http://192.168.0.101/finddata.aspx&IN=14) and the code runs and returns the values needed in a specific variable in this case ‘VALUE’. you can also use the LoadVariables command which explain that in this article.

Flash:Loading Variables from files into Flash

So here is a high level of what is being done in the background.

All all times the Insteon Server is updating the SQL Server with the status of the devices as they are turned on or off, so we dont need to connect there. Also note that the Local .net, SQL database and insteon server are all on the same box!

For example lets say I want to pull the status of device #14. (Fan in master bedroom)  This is what I would do from the Flash Program

Example in FLASH CLIENT;

Prepare: In Flash you will need to create a empty movie clip called ‘fan_master’.  Also have images already in your webpage in the location mentioned below. I call it right of the same page you can use your local library if you like.

Here are my images I use in my Flash program,  you may use them if you like (Just right click on them and use SAVE AS)

Light_bulb_off.png

Light_bulb_on.png

Off Button (small_button_on.png)

On Button (small_button_off.png)

In my actionScript, I would put on one of the frames;

//--------------------- SETUP A NEW VARIABLE TO GRAB MY DATA THIS FUNCTION WILL RUN WHEN THE BELOW SITE IS OPENED
var mfan:LoadVars = new LoadVars();
    mfan.onLoad = function() {

//------ Here I check the return value that is returned back as 'value' if its is ON then replace the fan_master 'movie' to the bulb on image
          if (mfan.value=="ON"){
            loadMovie("http://192.168.0.101/images/small_button_on.png", fan_master);
        } else {
            loadMovie("http://192.168.0.101/images/small_button_off.png", fan_master);
        }
    };
//---------------- do the actual opening of the webpage and pass the variable IN and the device im asking for
mfan.load("http://192.168.0.101/finddata.aspx?in=14");
stop();

BLUE BOX (SERVER .NET SIDE)

A description of my Devices Database is found here

‘————————— VB 2008 / WEBPAGE filename finddata.aspx

On my Server side I would grab the value on “IN” using the

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
      '------- Device will hold the value of in 'IN' which is #14
      device = Context.Request.Params("IN")
      value = Lg_Get_Device_Status(device)
      Response.Write("value=" + value)
 End Sub

This will return to my program the following text
“value=ON” or “value=OFF”

‘——– Function to grab the device data by the device #

    Public Function Lg_Get_Device_Status(ByVal Marker As String) As String

        Dim sqlConnection10 As New System.Data.SqlClient.SqlConnection(ConnectionSTring) ' Set your connectionstring accordingly.
        Dim Cmd10 As New System.Data.SqlClient.SqlCommand

        '-------- We'll use the SqlDataReader to get the values of the table 
        Dim Reader As SqlClient.SqlDataReader

        Cmd10.CommandText = "SELECT * FROM Devices where Marker= '" + Marker + "'"
        Cmd10.Connection = sqlConnection10
        sqlConnection10.Open()
        Reader = Cmd10.ExecuteReader()

        ' check for data, if not return 'FALSE'
        If Reader.HasRows = False Then
            Lg_Get_Device_Status = False
            Exit Function
        End If
        Reader.Read()
        '-------- RETURN THE VALUE FROM THE DATABASE SUCH AS ON OR OFF OF %
        Lg_Get_Device_Status = Reader.Item("status")
        sqlConnection10.Close()

    End Function

And there you have it, I do use the same file (finddata.aspx) for other stuff and you can easily query what is being passed and eventually act on it like this,

For example if I pass(showall=1) it would run another function, but that is another article.

        '------------------- SHOW ALL DEVICES
        If Context.Request.Params("showall") <> "" Then Show_insteon_devices() : Exit Sub

        '------------------- SHOW ALL DEVICES by MAC address
        If Context.Request.Params("showmacs") <> "" Then Show_insteon_devices_mac() : Exit Sub

        '------------------- SHOW ALL CONFIGS
        If Context.Request.Params("show_all_configs") <> "" Then Show_all_configs() : Exit Sub
Advertisements

The “Devices” data dictionary

I’m always talking about the database this, database that,

So here is the layout of what I have.  This main database ‘Devices table’ that holds all the mac address of each device I have in my home. This makes easy to call devices by number and not by mac.

As mentioned before its a SQL database, you can use what ever you want, This is included in the free version of VB 2008 express. I recommend you download it.

Here is what each field means,

Marker=  is setup as a sequential number which is automatically set so it increments automatically, this is the # of my device, since there can only be one.

Name= The name of device, for example I try to use a standard name, this makes it easier if there is more than one device in a room, for example “Kitchen MainLamp” or “Kitchen Sink lamp”

Mac = The Insteon address, including the dots, for example “01.41.5F” all in caps

Type = “L” for lamp, “F” for fixed I dont know why but I dont use this yet.

Protocol= “I” for Insteon or “X” for X10

Dimmable=”True or False”

Status = Current status of device, if its ON, or OFF, and even % of device such as 50%

Poll = True or false, when I want to check the status of all devices, setting this to True will include it.  For example I don’t want to poll a device which isn’t on line since it will eventually retry 3 times and time out the others.  This is specifically for Christmas decorations.

Last_seen = Last time any change was done to the device, on,off, etc.

randompoll = Sometimes some devices can get pesky so I poll a device every couple of minutes, but only one devices, that is where setting this to True means it will include this on the mix

That’s it, that’s my ‘devices’ database… once they are loaded into memory I reference them by number!

Loading the Insteon Database into Memory

In my code I reference them by using VB structures like this, below is the definition which is defined as public so its available to everyone.

   '----------------------------------- maint structure for insteon devices
    Public Structure Insteon_Device
        Dim MAC As String
        Dim MacSP As String
        Dim MacNP As String
        Dim Name As String
        Dim Type As String
        Dim Protocol As String
        Dim Dimmable As String
    End Structure

Then I call it as a type of variable like this;

'---- I set this to 40 to make space for more. This is set way on top of the program as public so its accessible in all the program.
Public InD(40) As Insteon_Device

This allows me to call the variables like this

debug.print (Ind(1).Mac) ' This will give me the mac of the device by #.. Neat!

Since i come from VB6 Im used to the old classic SQL connection definitions, like all my code there is many ways of doing this so yes you can use the table-adapters and other .NET goodies since I only want Raw power not any fancy tables or colors, I got with my trusty code.

Here is how I load them into the variables itself calling my public function.

Public Function Load_Insteon_devices()
        Dim marker As Integer
        Dim TotalDevices As Integer

        '----------------- SETUP THE SQL CONNECTION AND DATA-READER COMPONENTS
        Dim sqlConnection2 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString)
        Dim cmd2 As New System.Data.SqlClient.SqlCommand
        Dim counter As Integer
        Dim Reader As SqlClient.SqlDataReader

        '---------------- FIRST QUERY CHECKS THE AMOUNT OF DEVICES I HAVE
        cmd2.CommandType = System.Data.CommandType.Text
        cmd2.Connection = sqlConnection2
        cmd2.CommandText = "SELECT COUNT(*) AS CNT FROM Devices"

        sqlConnection2.Open() : Reader = cmd2.ExecuteReader() : Reader.Read()
        TotalDevices = Reader.Item("CNT") '---- TotalDevices will grab to # of devices I have :)
        sqlConnection2.Close()

        '-------------------- NOW THAT I GOT THE AMOUNT GO AHEAD AND READ ALL THE FIELDS
        cmd2.CommandText = "SELECT * FROM Devices"
        sqlConnection2.Open() : Reader = cmd2.ExecuteReader() : Reader.Read()

        '------------------ SCROLL THRU THE DEVICES AND UPDATED EACH STRUCTURE PROPERTY 
        For marker = 1 To TotalDevices
            counter = Reader.Item("Marker")
            InD(counter).MAC = Reader.Item("MAC")

            '-------------- THE VARIABLES MACSP IS THE SAME AS THE MAC ONLY REPLACING THE DOTS FOR SPACES
            InD(counter).MacSP = Trim(InD(counter).MAC.Replace(".", " "))  

            '-------------- THE VARIABLE MAPNP IS THE SMAE AS THE MAC ONLY THAT THERE IS NO SPACES
            InD(counter).MacNP = Trim(InD(counter).MAC.Replace(".", ""))
            InD(counter).Name = Reader.Item("Name")
            InD(counter).Protocol = Reader.Item("Protocol")
            InD(counter).Type = Reader.Item("Type")
            InD(counter).Dimmable = Reader.Item("Dimmable")
            InD(counter).Mastermapping = Reader.Item("Mastermapping")
            Reader.Read()
        Next
        DEBUG.PRINT ("FINISHED LOADING DEVICES")
    End Function

How I update my Devices Database via code

For my own fancy, Here is the code I use to update a device by #.  Like mentioned before when you call a INSTEON group which affects more than one device you dont get individual response from each item so I like to update the DB manually.

So I call another custom function that will update the device using the mac address, and pass the status, such as on or off. For example;

Lg_Update_Device(InD(20).MacNP, "OFF")

Public Function Lg_Update_Device(ByVal ID As String, ByVal value As String) As Boolean

        '------------------------ SETUP CONNECTION AND OTHER GOODIES BEFORE UPDATING ANYTHING
        Dim sqlConnection34 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString)
        Dim cmd34 As New System.Data.SqlClient.SqlCommand
        cmd34.CommandType = System.Data.CommandType.Text
        cmd34.Connection = sqlConnection34

        '------ THIS IS THE ACTUAL SQL COMMAND TO UPDATE THE DEVICE USING THE MAC AS  THE KEY.      

        cmd34.CommandText = "UPDATE Devices SET status= '" + value + "', last_seen='" + Now().ToString + "' WHERE MAC='" + ID + "'"
        If sqlConnection34.State = ConnectionState.Open Then sqlConnection34.Close()
        sqlConnection34.Open()
        cmd34.ExecuteNonQuery()

        '---------------- CLOSE CONNECTION AND EXIT FUNCTION
        sqlConnection34.Close()
        Update_TimeStamp()

    End Function

My Sql database runs on the same box as the ‘server’ and is really fast, SQL caches information in memory so the more you have the better.  It looks like a lot of code but it flyes!.  Hopefully this example will help you on creating your own DB.

Cheers,