The “Configuration” database
August 23, 2008 at 6:38 am | In Software Setup | Leave a CommentTags: configuration database, Home Automation, Insteon, reading insteon configuration, sql 2005, vb, vb 2005, vb 2008, X10
As previously mentioned my main program depends 100% off a SQL database, below is a dump of my “Configuration” database, that holds all the system settings.
Many are loaded into memory the first time, or when I run PollDevices query this way the information is updated when needed. I know its a lot of information but maybe a developer can use these fields as ideas for their program.
Below are my two main functions I’ve created call (get a value or update a value).
Grabbing a value from the “Config” Database
For example this function will return the value of a specific query for example
lg_get_config("wake_up_time")
Public Function Lg_Get_config(ByVal field As String) As String
'------ SETUP CONNECTION TO DATABASE (CONNECTION STRING MAY VARY AMONG SYSTEM OR WHERE you have DB)
Dim sqlConnection35 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString)
Dim cmd35 As New System.Data.SqlClient.SqlCommand
'------ SETUP READER TO GRAB THE NECESSARY DATA
Dim Reader As SqlClient.SqlDataReader
cmd35.CommandText = "SELECT * FROM Config where config= '" + field.ToLower.Trim + "'"
cmd35.Connection = sqlConnection35
sqlConnection35.Open()
'------ OPEN CONNECTION AND CHECK TO MAKE SURE
Do While sqlConnection35.State <> ConnectionState.Open
Loop
Reader = cmd35.ExecuteReader()
' --------- check for data, if not return 'FALSE' AND leave function
If Reader.HasRows = False Then
Lg_Get_config = False
Exit Function
End If
Reader.Read()
'---------- return the value and close all connections and exit.
Lg_Get_config = Reader.Item("value")
Reader.Close()
sqlConnection35.Close()
End Function
Setting a value in the “Config” Database
The next example updates a field, and is very similar just that it uses the SQL update command
For example lets say I want to update the “wake_up_time” to something else
lg_set_config("wake_up_time","9:00:00 AM")
Public Function Lg_Set_config(ByVal field As String, ByVal value As String) As Boolean
'------------- SETUP CONNECTION
Dim sqlConnection4 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString)
Dim cmd4 As New System.Data.SqlClient.SqlCommand
cmd4.CommandType = System.Data.CommandType.Text
cmd4.Connection = sqlConnection4
'------ The Update Command to modify the field
cmd4.CommandText = "UPDATE Config SET Value= '" + value + "' WHERE config='" + field.Trim.ToLower + "'"
sqlConnection4.Open()
'--------- Since we are not getting anything in return this is a NonQuery Executre
cmd4.ExecuteNonQuery()
Lg_Set_config = True
sqlConnection4.Close()
Update_TimeStamp() ' - I update the TimeStamp Value so anytime its queried they know is been updated.
End Function
Data Dictionary The “Config” Database
marker – Int field
config – nvarchar(128)
value – nvarchar(255)
description – nvarchar(255)
* marker field is my primary key, this is also set to “Identity Specification” which means it will autopopulate with a incremental value, so no record is the same, example (1,2,3,4,5,6 etc)
Below is a dump of all my fields and a quick explanation of what they represent. Descriptions starting with RST are the ones that are updated by the system automatically. For example if I turn on the outside lights, I update the field at that moment to say it has been turned on.
| outdoor_turn_off_time | 10:32 PM | When will all outdoor lights will turn off |
| outdoor_turnedon | Y | RST, If outdoor lights are on? |
| extractor_girls | N | RST, If the extractor is on or off |
| extractor_girls_time | 10 | In Minutes, Timeout for the girls extractor |
| timer_randomlights | N | If the random lights are on |
| random_lights_min | 10 | Random lights timer in minutes |
| time_after_sunset_to_turn_on | 12 | Time in minutes after sunset the lights outside will turn on |
| temperature | 75% | RST, Current temperature |
| temperature_last_read | 6:40:29 AM | RST, When was the temperature last read |
| temperature_text | Mostly Cloudy | RST, Text from NOAA.GOV |
| temperature_image | http://rssweather.cachefly.net/images/weather-symbols/mcloudyn.png | RST, Local image |
| SunRise | 6:59:52 AM | RST, SunRise in time |
| SunSet | 7:56:07 PM | RST, SunSet in Time |
| Outdoor_turn_on_time | 8:08:07 PM | RST from program, Outdoor turn on time |
| fan_off_low | 15 | Turn off Fan Threshold |
| fan_on_high | 88 | Turn on fan Threshold |
| voicemails | N | RST,If Voicemails are present? |
| master_closet_timer | 3 | Master Closet Timer in Minutes |
| master_closet | N | RST,Master Closet is on? |
| alarm_clock_status | ON | Mine, Turn on Alarm clock, M-F |
| poll_devices_interval | 525 | Minutes to poll each device |
| poll_devices_next_poll | 8/23/2008 15:28 | RST, Next time to poll |
| master_closet_turn_off_time | 8/22/2008 22:39 | RST, Master Closet |
| extractor_girls_turn_off_time | 8/22/2008 21:46 | RST,Girls Turn off Extractor time |
| Entrance_turnedon | N | RST, Is entrance on? |
| entrace_turn_on_time | 8:01:07 PM | RST, What time to turn entrance on |
| time_after_sunset_entrance_turn_on | 5 | Time to turn on Entrace after SunSet in Minutes |
| house_state | NIGHT | House State (E,O,I) |
| wake_up_time | 6:15:00 AM | Master Wake up time, M-F only |
| time_after_sunrise_to_turn_off | 10 | Time after SunRise to turn off Nook (Sat and sun Only) |
| incomming_call | N | CallerID Found? – No Longer Used |
| incomming_stamp | 8/22/2008 17:29 | RST, TimeStamp to clear call (No longer used) |
| incomming_name | Luis Garcia | RST, Caller ID |
| incomming_image | luis.png | RST, Imaged pulled from Contacts DB |
| incomming_date | 8/22/2008 17:29 | RST, Incomming Time |
| incomming_number | xxxxxxxxxx | RST, Incomming Number |
| entrance_turn_off_pre | -5 | Time in minutes before turn off time to turn off the entrance only |
| entrance_turn_off_pre_time | 10:27:00 PM | From program result of entracne_Trun_off_pre |
| motion_hallway | N | Motion is detected in hallway |
| extractor_master | N | If the extractor is on or off |
| extractor_master_time | 10 | Timeout for Extrator Master |
| extractor_master_turn_off_time | 8/23/2008 6:18 | RST,Master Turn off Extractor time |
| sensor_hallway | N | IF Hallway Sensor is on |
| sensor_hallway_time | 6 | Timeout Hallway |
| sensor_hallway_turn_off_time | 8/23/2008 6:14 | Hallway Timeout |
| sensor_hallway_bypass | N | Set to N if normal and Y to ignoreTimer |
| sensor_entrance_last_seen | 8/23/2008 2:48 | RST, Last time movement was detected |
| sensor_entrance_rescan_timeout | 3 | Minutes before the entrance sends out another signal |
| sensor_entrance_bypass | Y | If sensor entrance is bypassed (Y)=dont check (N)=yes check |
| sensor_master_closet_bypass | N | If sensor master is bypassed (Y)=dont check (N)=yes check |
| sensor_hallway_last_seen | 8/23/2008 6:08 | RST, Last time movement was detected |
| sensor_master_bathroom_last_seen | 8/23/2008 6:09 | RST,Last time movement was detected in master batrhoom |
| sensor_den_last_seen | 8/22/2008 23:05 | RST,Last time movement was detected in DEN |
| sensor_garage_last_seen | 8/22/2008 20:59 | RST,Last time movement was detected in Garage |
| sensor_hallway_start_time | 6:00:00 PM | Time that the Sensor will kick in. Usually set 3pm since its day time it wont turn on |
| sensor_garage_door_state | CLOSED | Garage door if open |
| temperature_last_read_wdw | 6:40:29 AM | Last time temperature was read for WDW |
| sensor_outside_bathroom_last_seen | 8/23/2008 0:25 | RST,Last time movement was detected in master batrhoom |
| sensor_outside_bathroom_timeout | 25 | Outside bathroom timeout |
| sensor_outsidebathroom_enabled | Y | RST, Enabled or not? |
| music_playing_status | N | RST, Is Radio Music Playing |
| sensor_garage_door_state_last | 8/22/2008 20:58 | RST, Last time Garage was open or closed |
| sensor_garage_door_timer | 6/23/2008 20:48 | RST, Time is has been opened |
| decorations_status | OFF | Status of all decorations |
| sensor_den_timer | 8/22/2008 23:10 | RST, Time the Den will turn off it no movement |
| sensor_den_timer_timeout | 5 | Minutes, Den will turn off after no activity |
| sensor_master_closet_last_seen | 8/22/2008 22:36 | RST, MasterCloset Last Seen |
| decorations_on | 7:58:07 PM | Time Decorations turn on |
| decorations_off | 10:45:00 PM | Time Decorations turn off |
| decorations_after_sunset_on | 2 | Decorations turn on after sunset |
| speakers_status | OFF | RST, if speakers are on |
| coffee_machine_status | TRUE | CoffeeMachine timer if on or OFF |
| coffee_machine_timer | 6:20:00 AM | CoffeeMachine OnTime |
| sensor_laundry_last_seen | 8/22/2008 21:30 | RSt, LAST seen activity in Laundry |
| sensor_laundry_timeout | 3 | Minutes before laundry turns off |
| sensor_laundry_timer | 8/22/2008 21:33 | RST, When will it turn off |
| kelly_alarm_on | 6:15:00 AM | Kelly On_Time |
| kelly_alarm_status | ON | IF Kellys alarm is on or off |
| barbie_alarm_on | 7:01:00 AM | Barbie OnTime |
| barbie_alarm_status | ON | Barbie on-time |
| back_house_lights_on | 9:00:00 PM | When will the backlights turn on |
| back_house_lights_off | 9:30:00 PM | When will the backlights turn off |
| guilda_alarm_on | 6:15:00 AM | Guildas Light will turn on |
| guilda_alarm_status | ON | Guildas Alarm status |
| kelly_alarm_song | S:\Mp3\Hanna Montana6 – I Got Nerve.mp3 | Kelly AlarmSong |
| barbie_alarm_song | S:\Mp3\Abba\(1994) Thank You For The Music\Disc 1\21 – Abba – My Love, My Life.mp3 | Kelly AlarmSong |
| kelly_artist_songs1 | hanna mon | Kelly selected artist for Alarm |
| kelly_artist_songs2 | vanessa | Kelly selected artist for Alarm |
| kelly_artist_songs3 | ashley tins | Kelly selected artist for Alarm |
| barbie_artist_songs1 | cascada | Barbie Selected artist for Alarm |
| barbie_artist_songs2 | Abba | Barbie Selected artist for Alarm |
| barbie_artist_songs3 | Rick Springfield | Barbie Selected artist for Alarm |
| kelly_alarm_song_title | Hanna Montana – I Got Nerve | Last song Played |
| barbie_alarm_song_title | ABBA – My Love, My Life | Last song Played |
Setting up Insteon groups via SDM or ’soft linking’ commands
July 1, 2008 at 7:45 pm | In Software Setup | 2 CommentsTags: controller, creating insteon groups, groups, Insteon, insteon group commands, insteon groups, linking, on levels, ramp rates, responder, SDM, sdmserver, setting up insteon groups, soft linking, vb, vb 2005
If your looking to create groups using the simplehome.net utility click on the link below.
Setting up Insteon groups via software or ’soft linking’ using Simplehome.nets utility
Lets say you want to be able to link and create groups without having to use the Simplehome.net utility, this is how its done.
Lets assume with the previous post that you already have your PLC talking to your PC so you know you can use the Intellisense in VB to see the SDM commands available
setOnLevelText=<INSTEONid>,<onLevelCmdOrValue>[,<hops>] for example
Sm.SetOnLevelText(“0D.39.61″, “10%”) or even or
Sm.SetOnLevelText(“0D.39.61″, 255) for 100%
A good link and my reference I used to the SDM commands can be found here
Live Example
For the example let’s say you have two devices (Lamplincs, or dimmers) and want to create a group where both of them have a 19 second ramp rate and turn on 100 percent.
The first devices address is 01.02.03
The second device is 05.06.07.
Since we are using the native SDM commands we can use decimals numbers. Here is a break down of the command / “SetupLink”
SetupLink (“<Device to Modify>” , <True for Responder / False for Controller>, <Group #>, “<My PLC Address>”, <onlevel>, <RampRate>)
<Device to Modify> would be entered with the dots and quotes, in this case “02.03.04″
<True> for Responder for both of them since the PLC will be controlling them.
<Group #>, entered in Decimal for this example we will use #34
<My PLC address>, entered the same as above, with dots and in quotes
<onLevel>, using from 0-255, 0=off thru 255 = 100% on.
<rampRate> using one of the 32 ramp rates., use the chart below, for example 31 is .1 seconds. I like to use either #23 or #24.
| 31 | 0.1 | Seconds |
| 30 | 0.2 | . |
| 29 | 0.3 | . |
| 28 | 0.5 | . |
| 27 | 2 | . |
| 26 | 4.5 | . |
| 25 | 6.5 | . |
| 24 | 8.5 | . |
| 23 | 19 | . |
| 22 | 21.5 | . |
| 21 | 23.5 | . |
| 20 | 26 | . |
| 19 | 28 | . |
| 18 | 30 | . |
| 17 | 32 | . |
| 16 | 34 | . |
| 15 | 38.5 | . |
| 14 | 43 | . |
| 13 | 47 | . |
| 12 | 1 | Minutes |
| 11 | 1.5 | . |
| 10 | 2 | . |
| 9 | 2.5 | . |
| 8 | 3 | . |
| 7 | 3.5 | . |
| 6 | 4 | . |
| 5 | 4.5 | . |
| 4 | 5 | . |
| 3 | 6 | . |
| 2 | 7 | . |
| 1 | 8 | . |
| 0 | 9 | . |
Here is the command below
Sm.SetupLink(“01.02.03″, True, 34, “0D.51.32″, 255, 23, 0)
Simple right?. I recommend testing this with a external device jsut to be sure, the best thing of this is that when the command is executed it automatically creates the controller link on your PLC, so basically you dont have to do anything else!! When you run the command make sure you dont have other stuff going on, like motion sensors, X10 data, it tends to not act nice. If you look at the data comming in you will see a confirmation string at the end like this.
7/1/2008 8:30:49 PM receiveinsteonraw=04 01 02 03 0D 51 32 22 2B 17
7/1/2008 8:30:50 PM setupLinc=true,01.02.03,34,0D.51.32,1,255,23,C,0xA2,0,08.B6.6C[7]0×0FC8,[0]0×0000
and our set, now if we want to setup the second one we do the same thing but change the first address. Like this,
Sm.SetupLink(“04.05.06″, True, 34, “0D.51.32″, 255, 23, 0)
Now using our group commands both will turn on at the same time using the defined ramp ramp to the specified value, But you can define different ramp rates and on levels to different device on the same group have a really neat effect, if that is what you want. For example you can have two devices on the same group but one with 50% on and the over 100% and with one command you set up a scene!!! neat!.
Here is the command I use to turn on group #34, since im using the sendtext to the SDM directly the values will need to be entered in HEX for #34 is the equivalent to #22.
Sm.SendINSTEONRaw(“0D 51 32 00 00 22 CF 11 FF”, 3) ‘ sEND broadcast group 17 ON!
#22 is the equpvalent of #34 in Hex and you already know #11 is the on command, to turn off its #13, You can see a breakdown of the commands here. You can brighten, dim all the devices at once also!!
Only down side of the groups is that you wont get a feedback on the individual devices. So you may need to poll them individually.
Before you ask, yes there is a way to confirm the record was created but that will be for another post.
Enjoy!
Luis
Setting up Insteon groups via software or ’soft linking’ using Simplehome.nets utility
June 27, 2008 at 1:28 am | In Software Setup | 4 CommentsTags: controller, creating insteon groups, groups, Insteon, insteon group commands, insteon groups, on levels, ramp rates, responder, SDM, sdmserver, setting up insteon groups, vb, vb 2005
Insteon is great and is extremely fast when turning on / off devices, but lets say you want to turn on 10 lights all at the same time?
This is what they call groups. Groups allow you to store in each individual device settings such as turn on, ramp rate commands, for a specific group #.
Let me give you a example, Let say you want to be able to turn on all your master bedroom lights with one command. What we would do is create a group number and all the devices which will be affected will have this same number and respond as (Responder) to the person sending the command. Don’t worry its easier than it sounds. Let me go thru the basics and you’ll see how its done.
First for starters easiest way is to use some sort of spreadsheet, place all your device from left to right and then fill out what you want to turn on. Here is a example (I’ll explain what the numbers mean shortly). This way I know what group will do what and who will be ‘affected’. Then its very easy to program going down the list.

In my case I have for example, Group #15 (Its HEX) called “End of Day” and what it does is turn off all lights but and turn on the kitchen sink all with one command. So I mapped out all my devices and put how they are to be affected.
On Level
As you can see my values are two sets of numbers (I’ll select #15 the 2nd value of (FF/1B)
The first stands for the OnLevel (In the case of the dimmers (0-100%) but note its a HEX value, and it runs from 0-255. Here is a quick list which I use.
| HEX | BRIGHTNESS |
| FF | 255 |
| F0 | 240 |
| DC | 220 |
| C8 | 200 |
| BE | 190 |
| B9 | 185 |
| AF | 175 |
| A5 | 165 |
| 9B | 155 |
| 8C | 140 |
| 78 | 120 |
| 64 | 100 |
| 50 | 80 |
| 4E | 78 |
| 4D | 77 |
| 4B | 75 |
| 3C | 60 |
| 1E | 30 |
| 00 | 0 |
This way if I want to do a 50% I would use the A5 value, since im using FF it stands I want full blast, 100%!.
You will have to play with these to get the best ’setting’ for your environment.
You can set a device to turn on to a 0% value which is the same as off, for example I have a group setup to turn on the sink night light but all other devices in the group are set to a ONVALUE of 0! NEAT!. But OFF is OFF, no mater what you set it to. You can see the VB samples below. Basically the commands are the same instead of calling a device you call a group.
So with group #15 you can see the first value of all the devices is 00 with the exception of the kitchen sink which is FF so when the group command is sent all devices are dimmed while the Kitchen sink is turned on, all at the same time, the effect is very cool.
Ramp Rate’s
The second value is the RAMP rate (This only applies to devices which can ‘DIM’, and sadly its not included on the ICON dimmers, this is one of the reasons of the price difference.) These special devices have 32 levels of Ramp rates, from .1 second (which is what it is on the Icon dimmers, to 9 minutes!!) Here is the conversion of the HEX value to the RAMP time
| RAMP RATE | TIME | Factor |
| 1F | 0.1 | Seconds |
| 1E | 0.2 | . |
| 1D | 0.3 | . |
| 1C | 0.5 | . |
| 1B | 2 | . |
| 1A | 4.5 | . |
| 19 | 6.5 | . |
| 18 | 8.5 | . |
| 17 | 19 | . |
| 16 | 21.5 | . |
| 15 | 23.5 | . |
| 14 | 26 | . |
| 13 | 28 | . |
| 12 | 30 | . |
| 11 | 32 | . |
| 10 | 34 | . |
| 0F | 38.5 | . |
| 0E | 43 | . |
| 0D | 47 | . |
| 0C | 1 | Minutes |
| 0B | 1.5 | . |
| 0A | 2 | . |
| 9 | 2.5 | . |
| 8 | 3 | . |
| 7 | 3.5 | . |
| 6 | 4 | . |
| 5 | 4.5 | . |
| 4 | 5 | . |
| 3 | 6 | . |
| 2 | 7 | . |
| 1 | 8 | . |
| 0 | 9 | . |
So a value of 1F is instant on, a value of 0C is 1 minute, and a value of 08 is 3 minutes!!! The effect is awesome! Note that once you send the command to the device to turn on with a specific RAMP rate it will go on its own and you can send more commands to other devices… NEAT! Better yet you can have different device in the same group with different ramp rates. You know where im going with that!! What i’ve read on the post that a slow ramp rate will prolong the life of certain lights, in my case all are CFL’s so I only use RAMP rates for external decorations and maybe a light or two.
Lets setup a group!
Lets show you some screens so you can see how its done.
Like all other things Im suspecting there are other ways of doing this, but this the way ive learned to do it and has always worked for me.
- Download Simplehome.net’s program which you can find under my “Programs to have” and install it. Im suspecting its free since its right off their support page. Once installed go ahead and fire it up.
- I’ve found that the program works better if there isnt any activiting on the network, also if my dryer isnt ok. Lol,. So please disconnect any X10 transievers or other information which may confuse the program.
- Since the utility is used to program many devices lets make sure we select that we are programming our PLC, select the USE PLC followed by ‘Connect’
If you get an error, make sure you SDM driver is loaded, it should load automatically if not. This is also available from the ‘Programs to have’ page.

- Select the “Manage Device Links”
- Now hit your caps lock as all values need to be capital and the devices ID need to have (.) between them, like in the sample. (Here we go!)
- In the first red square you will be entering the device you want to control (or in this case add to the group)
- In the green square, we need to add what device will be controlling it or responding to it. If the device is going to respond its command we would select ‘Responder Below’, so this means when I call the group command from the device in the green box, the device in the RED box will respond. In the case of a Controller its backwards.Since Im using my PC to send out group commands, we can assume that the ‘Second Insteon ID’ field will always be my computer PLC address. This of course can be any other item, this way you can have one device control another or vice-versa! In the case of 3 way switches this is where you would have two entries on both devices, and each is a responder and controller of each other!!
- In the Yellow box is where we 1st define the On Level (In HEX) , followed by the 2nd box contains the RAMP rate, 3rd box .
- Enter a group number in HEX.
- The last option as mentioned before its a responder or controller. For the sake of this example its Responder.
- Hit the ‘Create New Link’

11. And your SET!.
Now from your Code you can send a group ON command and watch the magic happen. Here is example using the SendInsteonRaw command to turn on group #16
Sm.SendINSTEONRaw(“0D 51 32 00 00 16 CF 11 FF“, 3) ‘ sEND broadcast group 16 ON (Command is #11)
Sm.SendINSTEONRaw(“0D 51 32 00 00 16 CF 13 00“, 3) ‘ sEND broadcast group 16 OFF! (Command is #13)
Just replace the MAC address with the group number and presto, you can DIM you whole house, For example
Sm.SendINSTEONRaw(“0D 51 32 00 00 16 CF 16 00“, 3) ‘ sEND broadcast group 16 DIM! (Command is #16)
Sm.SendINSTEONRaw(“0D 51 32 00 00 16 CF 15 00“, 3) ‘ sEND broadcast group 16 Brighten! (Command is #15)
NEAT!
There is a catch to group commands, that they WONT RETURN THE STATUS OF THE INDIVIDUAL ITEMS, you will only get a response that the command to the group was sent successfully. In my case I usually send the command twice to make sure they all are hit.
The first 3 sets of digits are my PLC, the next three are the group number
CF = ?
11 = the On Command
FF = the On Value
Like mentioned before, depending on your device and your RAMP rate settings the device will fade on, ICON switches will just jump to the level you have then set to.
For my program I have groups setup for
- Turn on/OFF all house lights
- Turn on/off both children’s room
- Turn on/off all external/indoor decorations
- Turn off all bathroom lights
- Turn off all lights but turn kitchen sink on
- Movie scenes Dim lights etc.
- Turn on/off all fans (We’ll get to that later using the Inline Linc’s)
- The sky’s the limit
You are of course limited to the amount of groups, depending on the device, for example the ICON’s will hold less groups but I doubt you’ll use them all.
Using this program is where I also link the device to the PLC so I can catch the commands coming in if I cant use the TAP/TAP method, for example I want to see when someone turns off a device, I would do everything the same except I would select ‘Controller’. Even thought the switch really doesn’t turn on anything on my PLC I need to have that ‘group’ setup so I can capture it in my code.
Good luck!
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.
