| Author |
|
renard
Groupie
Joined: November 01 2009
Location: United States
Online Status: Offline
Posts: 74
|
| Posted: December 06 2014 at 06:21 | IP Logged
|
|
|
I have about 80 Insteon controlled lights in my home and for unknown reasons we have a problem with lights turning themselves on without command (confirmed not to be a PH problem). I want to build a macro that runs automatically controlled by a timed trigger that checks a global WEAREAWAY variable and if true, turns off any lights that are not supposed to be on for security reasons.
I need to be able to determine programmatically what 'lighting' devices are actually out there. Dave provides tools to query the PH database to return the IDs of all the identified devices and tools to loop through the query, get the type, and return each item in turn. I use SQL regularly for work but I have no way of seeing the structure of the PH database. I do not believe a database schema, even in abbreviated form, has ever been published. I have Microsoft Access (mentioned as a possible viewer) installed on another computer in the house and MYSQL running my SQL server on the HOUSE CONTROL computer hosting PH but I am concerned with connecting a MYSQL-family tool to the PH DB.
Does anyone have an example of the combination of ph-directSQL, ph_finddata, ph_getcolcount, ph_getcolname, ph_getcoltype, and ph_getdata functions that will query the PH database and return the IDs of all the defined Insteon devices?
Thanks,
__________________
Terry
|
| Back to Top |
|
| |
nick7920
Senior Member
Joined: March 04 2008
Location: United States
Online Status: Offline
Posts: 193
|
| Posted: December 06 2014 at 18:48 | IP Logged
|
|
|
Just some idea's
over the years my Insteon devices (switch or lights) had
following problems.
1. some how got linked to other switch and will turn on and
off by other switch - had to factory reset both to make it
work.
2. some how X10 information was in the switch and was
causing the problem. set the switch to very different x10
address don't member if you can completely disable.
3. some PLM controller was acting up. clean and re install
the device ids.
I dont have that many Insteon devices but I do check my
lights with timed event and check the light status.
do the action by looking at my global variable.
I use following to check quickly all the Insteon which does
not separate the lights for you. but if there is no way to
know device type then may be change your ID - may be ending
with "_L" then work with it.
ph_sql(0,"select ''|| id.id ||''|| (if id.status = 0 then
',OFF' else ',ON' endif) from insteondevices
id,insteontypes it where id.typeid = it.typeid and
id.activeflag = 1 and it.devcat in (1,2) order by id.id")
now if PH does not know current status of the insteon then
may be you have to poll those then perform your command.
dont know if you looking for something this......
Good Luck
Nick
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: December 06 2014 at 20:11 | IP Logged
|
|
|
Terry,
You should be able to connect to the PowerHome database from any ODBC compliant DB tool. The userid/password is ph/ph. I know Access works but havent played with MySQL. If it supports ODBC, then you should be able to do it. I believe you'll have to do it from the PowerHome machine though as I don't believe the runtime version of SQL Anywhere supports remote access.
If you go to the download page for PowerHome, you'll find the old PowerHome documentation which has a listing of the tables/columns in the appendix. It will be seriously out of date though.
If you open the PowerHome multi-editor in SQL mode (Shift-F5), you can see a list of the System tables by navigating to Edit->Autocomplete->SQL System Tables (alt-5). You can see the PowerHome tables by going to Edit->Autocomplete->SQL PH Tables (alt-6). You can programmatically get a list of PowerHome tables by executing:
select table_name from systable where creator = 101
Once you've got the tables, the easiest way to see the structure is to just do a select * from tablename for each table. Of course, using a tool such as Access will make it alot easier.
Dimmable lighting devices have a Device Category of 1. You can get a list of dimmable lighting by using this SQL statement:
select id from insteondevices where typeid in (select typeid from insteontypes where devcat = 1)
ApplianceLinc type devices (relay) have a Device Category of 2. If you're interested in both types, you can change the last part of the SQL to devcat in (1,2).
There are multiple ways of working with SQL and the database programmatically within PowerHome but the easiest (since this is fairly simple SQL) would be to just use the ph_sql function as Nick suggests coupled with a ph_getline function in a loop to parse through each ID one at a time.
Hope this helps,
Dave.
|
| Back to Top |
|
| |
|
|
Active Topics 
Memberlist 
Search 
Help
Register 
Login
PowerHome Messageboard : PowerHome Programming
Topic: Retrieving a List of All Insteon Devices
