Question about MySQL and script.

Ask and answer questions related to any type of OSA scripting.
Message
Author
Stefan
Posts: 28
Joined: Sun Jan 17, 2016 8:25 am
Location: Sweden

Question about MySQL and script.

#1 Post by Stefan » Mon Feb 08, 2016 11:49 am

Hi everybody.

I'm totally new on this , and i'm trying to understand the logic of this system.

I want to get one field from a table in Mysql osae to osae_object_property.

I've tried everything in scripting and powershell and cant get anything right.

this line works when i do it in mysql, but not in the script of osa.

SELECT @var := B600080036429810 FROM onewire ORDER BY datetime DESC LIMIT 1;
select @var;

I know how to set the property in a script, like this:
set-osa -name "one wire" -property "sensor 1" -value (whatever)

Shall i use Powershell or OSA Default Script Processor ?

Regards
Stefan

Vaughn
Site Admin
Posts: 1432
Joined: Thu May 13, 2010 2:17 pm

Re: Question about MySQL and script.

#2 Post by Vaughn » Mon Feb 08, 2016 11:47 pm

I haven't used Powershell yet, except for a simple test to make sure it is running. I am not sure what you are trying to do.

The script processor does not support variable like powershell yet. But it can check and set properties like :

IF System.Occupants > 0 THEN

and

System.Occupants = 3

in the API you would use:

OSAE.OSAEObjectPropertyManager.GetPropertyValue("System","Occupants")
OSAE.OSAEObjectPropertyManager.PropertyValueSet("System","Occupants","3")

from the DB itself:

CALL osae_sp_object_property_get("System","Occupants");
CALL osae_sp_object_property_set("System","Occupants","3");

Maybe someone else knows the format for Powershell, if not I will look into it in a couple days. I am in the middle the testing cycle to get the next version out right now.

Stefan
Posts: 28
Joined: Sun Jan 17, 2016 8:25 am
Location: Sweden

Re: Question about MySQL and script.

#3 Post by Stefan » Sun Feb 14, 2016 6:36 am

Hi again Vaughn

Is there a simple way to get information from serial ports in to OSA?

I just want to have the possibility to read and write directly to the USB or COM ports.

regards
Stefan

dj-eon
Posts: 89
Joined: Wed Jul 13, 2011 4:03 am
Location: UK

Re: Question about MySQL and script.

#4 Post by dj-eon » Sun Feb 14, 2016 11:57 am

Hi again Stefan.
My plugin 'Thorian' does exactly this.

Do you want a copy of it to play around with?
You will need Visual Studio.

I was inspired by the CM17 plugin (although this doesn't do conventional serial comms, it was my way in since I'd not touched visual basic for about 15 years!)

You can see the CM17 code here:
https://github.com/opensourceautomation ... A/CM17A.vb

You probably won't have much of an issue getting things talking to the serial port in the plugin. The headache for me was understanding how OSA is structured, sending and retrieving data in the environment.

Vaughn is super helpful though. He got me over that hurdle.

Stefan
Posts: 28
Joined: Sun Jan 17, 2016 8:25 am
Location: Sweden

Re: Question about MySQL and script.

#5 Post by Stefan » Tue Feb 16, 2016 10:07 am

Hi Ian.

I will love to try out your plugin.

I have my program ready in the PIC and it's sending information directly to telnet now, thru Bluetooth.

So the only problem is to get it in to OSA.

Then for the 1-wire system i have another program called LOGTEMP, and this program is also updating a new table in MySql with temperature for all one wire.(DS18B20)

Then i'm using 1 minute event updating the Object.Property table in osa.
I don't know if this is the right way, but i works like a clock.

So i'm absolutely interested in your plugin.

Regards
Stefan

Stefan
Posts: 28
Joined: Sun Jan 17, 2016 8:25 am
Location: Sweden

Re: Question about MySQL and script.

#6 Post by Stefan » Tue Feb 16, 2016 10:13 am

So for your information!

This is my way to use 1-wire on the system, since the plugin doesn't work any more!

BEGIN
CALL osae_sp_object_property_set('SYSTEM','Date',CURDATE(),'SYSTEM','osae_ev_minute_maint');
CALL osae_sp_object_property_set('SYSTEM','Day Of Week',DAYOFWEEK(CURDATE()),'SYSTEM','osae_ev_minute_maint');
CALL osae_sp_object_property_set('SYSTEM','Day Of Month',DAYOFMONTH(CURDATE()),'SYSTEM','osae_ev_minute_maint');
CALL osae_sp_run_scheduled_methods;
#CALL osae_sp_debug_log_add('Minute timer','SYSTEM');
SELECT
@var := `B600080036429810`, @var1 := `EF00080040894A10`, @var2 := `17000800472FA110`
FROM
`onewire`
ORDER BY
`DATETIME` DESC
LIMIT 1;
UPDATE
osae_object_property
SET
property_value = @var
WHERE
`object_property_id` = 0000007991;
UPDATE
osae_object_property
SET
property_value = @var1
WHERE
`object_property_id` = 0000007988;
UPDATE
osae_object_property
SET
property_value = @var2
WHERE
`object_property_id` = 0000007987;
SELECT
@var3 := `6500080046D20410`, @var4 := `6600080047498C10`
FROM
`onewire_boa`
ORDER BY
`DATETIME` DESC
LIMIT 1;
UPDATE
osae_object_property
SET
property_value = @var3
WHERE
`object_property_id` = 0000007990;
UPDATE
osae_object_property
SET
property_value = @var4
WHERE
`object_property_id` = 0000007989;

END


Very Simple, I've just included a copy from the last saved value from Onewire table to osae.object.property.

I guess this is not the right way, but it works.

Any suggestions?

Regards
Stefan

Vaughn
Site Admin
Posts: 1432
Joined: Thu May 13, 2010 2:17 pm

Re: Question about MySQL and script.

#7 Post by Vaughn » Tue Feb 16, 2016 2:02 pm

There should never be a need for any table or custom SQL work in the DB. If you are able to write to a table, you can call the proper procedures.

Each proc that writes to tables, not only protects those tables, but also has additional logic that you bypass when writing to the tables directly. Table aside, lets look at the following SQL you are using:

UPDATE
osae_object_property
SET
property_value = @var
WHERE
`object_property_id` = 0000007991;

To set a property value, you should use the CALL osae_sp_object_property_set, like the first 3 lines at the beginning of the SQL you posted. IDs should never have to be referenced either, all the procs translate name fields in OSA.

Bottom line is OSA's DB is not for development and no plugin or code should ever directly access it. There are so many proper ways available, such as the REST interface, you can update any DB values with a simple http call. You can also use a plugin that receives any data, via any method from the pic, and then the plugin makes the API calls to the DB.

The 1-wire plugin is a big piece of code that someone put a lot of effort into it. I would hope it would be more practical to fix whatever issue you are having with that plugin, rather than to chop into the DB. So if you post whatever issues you are having with the plugin, I am glad to help get it running, or even design a new plugin, but I can't condone modifying the DB.

Vaughn
Site Admin
Posts: 1432
Joined: Thu May 13, 2010 2:17 pm

Re: Question about MySQL and script.

#8 Post by Vaughn » Tue Feb 16, 2016 2:43 pm

Also, you can write any ol' standalone app, in any language to communicate with the pic and interface with OSA, then convert it to a plugin later. It can be hard to trouble shoot a plugin since it has no interface. Many plugins like the serial based W800RF, I wrote a full windows forms application first to test it and have a live log, then when done, I copy all the non-graphical parts to a blank plugin template.

I can also help if someone has a windows app like that and do the basic conversion to a plugin for them and get it in the code repository where they and others can help work on it

Vaughn
Site Admin
Posts: 1432
Joined: Thu May 13, 2010 2:17 pm

Re: Question about MySQL and script.

#9 Post by Vaughn » Tue Feb 16, 2016 2:53 pm

I was just thinking, I don't know what is writing to the onewire table, maybe you can't modify that code. If that is the case and you want to leave a onewire table in your MySQL DB, a plugin could convert/manage that data without changing the DB. Just like the timer that is running it once a minute, the plugin could run once a minute and handle the data. I understand this is only a good solution if you don't want to modify the program that is currently writing to that table. Just wanting to point out that a plugin does not have to connect to hardware, it can run any code you want.

dj-eon
Posts: 89
Joined: Wed Jul 13, 2011 4:03 am
Location: UK

Re: Question about MySQL and script.

#10 Post by dj-eon » Fri Feb 19, 2016 5:35 am

Hi Stefan.
I've attached the source for my latest version of the plugin.
I agree with Vaughn though. You might want to write a standalone plugin in your favourite programming language and get this working in that first.

My plugin waits for inbound serial data at 9600 8 N 1.
When data comes in, it expects to see 4 bytes with no delay between them:

Address of module (1-254)
Temperature LSB (See DS18B20 datasheet)
Temperature MSB (See DS18B20 datasheet)
(Last byte not used - can be ignored)

These bytes should be sent to the plugin without alteration.
The plugin does all the conversion of data to temperature (recently including handling negative temperatures correctly)
Note that the plugin is converting the DS18B20 with 12 bit output. This is its default highest resolution, so doesn't need setting up.

The plugin is wrote for OSA version 0.4.5. I'm not sure how it will go with newer versions. (I've not had time to update my OSA)

One last note. My plugin only handles 1x DS18B20 per device. I've not wrote it to read daisy chained sensors as it doesn't fit my design.
Best regards,
Ian.
Attachments
Thorian10temperature sensor - negative values fix.zip
My latest version of the plugin.
(172.42 KiB) Downloaded 139 times

Post Reply