| Author |
|
lizaoreo
Groupie
Joined: February 11 2013
Location: United States
Online Status: Offline
Posts: 75
|
| Posted: September 14 2013 at 13:00 | IP Logged
|
|
|
I'm trying to use ph_run with a trigger to change a variable in a MySQL database. I already have a trigger on status change for all my devices that updates their icon for the control center.
I'm trying to add a command to use the MySQL.exe (command line utility) to update a database with their status as well. I think it's the quotes that are getting me.
ph_run('"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe" -u user -pPassword homedb -e "UPDATE homedb_status SET `Status`=~'Off~' WHERE `PHID`='BCKPRCHLGT';"')
The query when I type it from a command line (and it does work):
mysql.exe -u user -pPassword homedb -e "UPDATE homedb_status SET `Status`='On' WHERE `PHID`='BCKPRCHLGT';"
Not sure where I'm going wrong with it.
Also, while I'm asking for help :)
How can I have the PHID and Status auto fill in with variables? I'm sure there are some I can use from the trigger, I believe LOCAL10 does that status (right?). Is there a list somewhere of all the values for the TEMP variables that a trigger fills in?
|
| Back to Top |
|
| |
grif091
Super User
Joined: March 26 2008
Location: United States
Online Status: Offline
Posts: 1357
|
| Posted: September 14 2013 at 13:45 | IP Logged
|
|
|
Some of the single quotes are not standard single quotes. Some of the single quotes are what applications like Word use. They are sloped and I don't think they will work. In the ph_run look at the quotes around the word Status and PHID compared to other single quotes.
Edited by grif091 - September 14 2013 at 13:46
__________________
Lee G
|
| Back to Top |
|
| |
lizaoreo
Groupie
Joined: February 11 2013
Location: United States
Online Status: Offline
Posts: 75
|
| Posted: September 14 2013 at 14:33 | IP Logged
|
|
|
Yeah, I had to do that for the MySQL Query to work. It's how the examples were done and until I copied it didn't work.
|
| Back to Top |
|
| |
BeachBum
Super User
Joined: April 11 2007
Location: United States
Online Status: Offline
Posts: 1880
|
| Posted: September 17 2013 at 08:01 | IP Logged
|
|
|
To answer your question about TEMPS. I stick this in the equation to find out what I’m looking for.
"TEMPS " + "|" + ph_getvar_s(2,1) + "|" + ph_getvar_s(2,2) + "|" + ph_getvar_s(2,3) + "|" + ph_getvar_s(2,4) + "|" + ph_getvar_s(2,5) + "|" + ph_getvar_s(2,6) + "|" + ph_getvar_s(2,7) + "|" + ph_getvar_s(2,8) + "|" + ph_getvar_s(2,9) + "|" + ph_getvar_s(2,10) + "|"
Dave also posted this….
For the Insteon Device Chg trigger:
Code:
TEMP1 ID of Trigger
TEMP2 0
TEMP3 Insteon CMD 2
TEMP4 Type of trigger
1 = Direct Command from PH PLC
2 = Group Cleanup from PH PLC
3 = Group Broadcast from PH PLC
4 = Direct Command from device
5 = Group Broadcast from device
6 = Group Cleanup from device
TEMP5 The address of the sending device
TEMP6 20 = Direct type command
21 = Broadcast type command
TEMP7 The address of the receiving device
TEMP8 The Group number for group type commands, 1 otherwise
TEMP9 Insteon CMD 1
TEMP10 ID of Receiving device
__________________
Pete - X10 Oldie
|
| Back to Top |
|
| |
lizaoreo
Groupie
Joined: February 11 2013
Location: United States
Online Status: Offline
Posts: 75
|
| Posted: September 17 2013 at 10:31 | IP Logged
|
|
|
Thanks, now I just gotta figure out these quotes :-)
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: September 17 2013 at 10:45 | IP Logged
|
|
|
lizaoreo,
Took a quick look and you need to escape the single quotes around BCKPRCHLGT like this:
Code:
| ph_run('"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe" -u user -pPassword homedb -e "UPDATE homedb_status SET `Status`=~'Off~' WHERE `PHID`=~'BCKPRCHLGT~';"') |
|
|
That was the only error I found...it seems like you have the right idea. In PowerHome, strings can be enclosed in either single or double quotes (they can be used interchangeably but the opening must match the closing). If you must enclose single quotes within single quotes, then the tilde character must be used to "escape" the inner single quote (sometimes multiple tildes are required depending upon the indirection level like ~~'). Of course, the same applies to double quotes as well.
Give the above a try and see if that helps or not.
Dave.
|
| Back to Top |
|
| |
lizaoreo
Groupie
Joined: February 11 2013
Location: United States
Online Status: Offline
Posts: 75
|
| Posted: September 17 2013 at 12:45 | IP Logged
|
|
|
Hmm, still not getting it to update. Is there a log somewhere to see how PH is parsing
it?
This is my latest attempt after tweaking it around a bit to see what would happen:
Code:
ph_run("~"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe~" -u user -
pPassword homedb -e ~"UPDATE homedb_status SET `Status`='Off' WHERE
`PHID`='BCKPRCHLGT';~"") |
|
|
Also tried escaping the single quotes around Off and BCKPRCHLGT. No go :(
EDIT: Oh, and I did try what you posted as well of course ;)
Edited by lizaoreo - September 17 2013 at 12:47
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: September 17 2013 at 14:26 | IP Logged
|
|
|
Hmmm...Im not sure what the problem would be. As a quick test just to test the escape elements, I ran this:
Code:
| ph_run("~"notepad.exe~" ~"pwrhome.ini~"") |
|
|
and it brought up the pwrhome.ini file just fine so the escaped double quotes should be working. You can always nest single quotes within double quotes without escaping (and vice versa) so that shouldnt be causing you a problem either. Im also pretty sure you said that running just
Code:
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe" -u user -
p Password homedb -e "UPDATE homedb_status SET `Status`='Off' WHERE
`PHID`='BCKPRCHLGT';" |
|
|
from the command line works just fine. Im wondering if saved the working command line text to a batch file and then trying to ph_run the batch file will yield any results.
Dave.
|
| Back to Top |
|
| |
lizaoreo
Groupie
Joined: February 11 2013
Location: United States
Online Status: Offline
Posts: 75
|
| Posted: September 17 2013 at 14:58 | IP Logged
|
|
|
dhoward wrote:
Im wondering if saved the working command
line text to a batch file and then trying to ph_run the
batch file will yield any results. |
|
|
Oh! I hadn't thought to try that  I'll give it a shot
when I get home. If it works, I'll make a PowerShell script
to take care it. I'm using PowerShell scripts to populate
my other databases, don't know why it didn't occur to me to
do it with PowerHome too 
|
| Back to Top |
|
| |
lizaoreo
Groupie
Joined: February 11 2013
Location: United States
Online Status: Offline
Posts: 75
|
| Posted: September 18 2013 at 10:21 | IP Logged
|
|
|
dhoward wrote:
Hmmm...Im not sure what the problem
would be. As a quick test just to test the escape
elements, I ran this:
Code:
| ph_run("~"notepad.exe~" ~"pwrhome.ini~"") |
|
|
and it brought up the pwrhome.ini file just fine so the
escaped double quotes should be working.
|
|
|
Okay, this is goofy, it wouldn't run the bat file. So I
tried what you did above and it wouldn't do that either.
I'm thinking I've got something goofy going on
specifically with my stuff. All this time the code might
have been right
I double checked and PowerHome is running as "user" which
is my generic local admin account I use for everything on
this computer, so it has full admin rights. Not sure
what else to check that would prevent running a program
|
| Back to Top |
|
| |
lizaoreo
Groupie
Joined: February 11 2013
Location: United States
Online Status: Offline
Posts: 75
|
| Posted: September 18 2013 at 14:12 | IP Logged
|
|
|
*facepalm* I'm doing it wrong. I thought I could just
enter the formula into the Macro/Formula box on the menu
bar area and hit run and it'd run the formula. That's why
it isn't working, I can't believe I did that.
Okay, I shall try this again, with a bit better knowledge
O.o
|
| Back to Top |
|
| |
lizaoreo
Groupie
Joined: February 11 2013
Location: United States
Online Status: Offline
Posts: 75
|
| Posted: September 18 2013 at 14:16 | IP Logged
|
|
|
Yeah, what I tried above
Code:
ph_run("~"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe~" -u user -pPassword homedb -e ~"UPDATE
homedb_status SET `Status`='Off' WHERE `PHID`='BCKPRCHLGT';~"") |
|
|
worked fine when I put it in and triggered the trigger. I feel like a real fool now 
EDIT: and the final product for those interested:
Code:
ph_run("~"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe~" -u user -pPassword homedb -e ~"UPDATE
homedb_status SET `Status`='[LOCAL10]' WHERE `PHID`='[TEMP10]';~"") |
|
|
I was initially going to have it change the status value (0-255) to just say On or Off, but that seemed like
extra work I didn't want to do and it's easy to do it as it pulls it into the page later, I'm doing it already
with my HVAC data.
Thanks for the assist Dave
Edited by lizaoreo - September 18 2013 at 14:40
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: September 18 2013 at 15:39 | IP Logged
|
|
|
lizaoreo,
Glad you got it figured out. After your previous post of even trying to launch notepad didnt work I wasnt sure what to try next.
Honest mistake though because you CAN actually run a formula from the Macro/Formula box. Normally, it wants a macroID to execute. But...if the first character is a double quote, you can execute a raw formula. This double quote is not expected to have a closing quote and has nothing to do with the formula except to say that the characters following it is a raw formula.
You can also "queue" a raw formula by changing that first character to a single quote. Not as useful as the double quote because it doesnt give you a window with the result but if you should need to "queue" the formula rather execute it outright, the single quote will do it.
Dave.
|
| Back to Top |
|
| |
lizaoreo
Groupie
Joined: February 11 2013
Location: United States
Online Status: Offline
Posts: 75
|
| Posted: September 18 2013 at 16:46 | IP Logged
|
|
|
Yeah, when your simple notepad example didn't work, I knew
I had to be doing something wrong. I then tried a plain
old ph_insteon("BDRM2LGT",17,255) and when that didn't do
anything, I knew whatever I was doing in the run box wasn't
working 
Cool, good to know, that'll make testing formulas a little
easier in the future.
|
| Back to Top |
|
| |
BeachBum
Super User
Joined: April 11 2007
Location: United States
Online Status: Offline
Posts: 1880
|
| Posted: September 18 2013 at 22:36 | IP Logged
|
|
|
Good info Dave but that is why I never run formulas from the formula box. My 1st post was because of that. I do all my testing from a test macro sandbox.
__________________
Pete - X10 Oldie
|
| Back to Top |
|
| |
Active Topics 
Memberlist 
Search 
Help
Register 
Login
PowerHome Messageboard : PowerHome Programming
Topic: ph_run and quotes
