Controlling Hardware using Mango

Hi,

I am using Mango above my embedded application. My application monitors the electrical meters and stores it readings to a database. I am using mysql for that. After this I am using mango to view these readings. My application also monitors the relay status.
The database gives the status of the relays to Mango,but my problem is how i can change the relay staus in Datbase from Mango. ex: if I am getting the relay status as ON how i can change this status to OFF from Mango, so that using the current status of relay in Database I can swith ON or OFF my relays.
Also I need help on how to use the the SQL button in Mango to subit and update querry.
Please help me in this.
waiting for a positive response

Thanks n Regards
Gia.

Hi Gia,

There are a few steps to achieving what you want. To be able to change the relay status you must make it "settable". In the SQL data source, you do this by declaring an "Update statement" on the point. The update statement will look something like this: "update statusTable set relayStatus=? where <some condition>", obviously tailoring it to the needs of your database schema.

Then, you need to define the conditions under which the point will be set. I'm not sure what you want this to be. If it is based upon the value of another point, you need to create an event detector on that point. If you want a periodic check (say every 5 minutes or something), you need to create a scheduled event.

Now, you need to hook up your event to the setting of your relay point. This can be done using a "Set point Event handler". Go to the event handler section and find your event in the event tree - select it. In the event handler box, choose the "set point" type, choose your relay point as the target, enter the value you want to have set, and click save.

That should do it.

Oh, the SQL section in Mango is more for technical investigation of any problems you may be having with the software. I can provide the database schema if you like, but it likely won't help you with the general usage of the software.

Hi

Thanks for the quick response and

Thanks for the wonderful explanation.
After following your steps now finally I am able to change the status of relays from Mango UI.

But there is some problem

Here are the details of what I am doing :

I am simulating the control of relays through mango and mysql.

Here is the database table I am using for simulation:

mysql> select * from gateway1;
+--------+---------+
| status | relayno |
+--------+---------+
| 1 | relay1 |
| 1 | relay2 |
| 0 | relay3 |
+--------+---------+
3 rows in set (0.00 sec)

Now in mango I have created three points for three relays with the following properties of the points;

Point Name Relay1
----------------------------
SQL properties:
Query field name : status
Update statement : update gateway1 set status = ? where relayno = "relay1"
Data type : Binary

Event detectors:
Type: Change

Logging properties :
Logging type: Do not log

Text renderer properties:
Type : Binary
Zero: OFF
One: ON

Point Name Relay2
----------------------------
Same settings as above only change is in SQL properties:
Update statement : update gateway1 set status = ? where relayno = "relay2"
Data type : Binary

Point Name Relay3
----------------------------
Same settings as above only change is in SQL properties:
Update statement : update gateway1 set status = ? where relayno = "relay3"
Data type : Binary

After doing this my watchlist is showing these three points with radio buttons in it showing ON and OFF to change the status of relay.

But once I change the status of Relay1 from ON to OFF or OFF to ON, after the refresh time the remaining Relay2 and 3 status also changes to the same state(ON or OFF) as of relay1. But my database status only changes for relay1 not for 2 and 3.
Again changing relay2 status does not effect relay1 and relay3 status and after refresh time it once again come to the status as for relay1 and my database reflects the changes done in relay2.

Please tell me am I missing something? or I am doing some wrong settings.

I am also trying to plot the graph between time and staus of the relay to see the relay status at different times but not able to plot it. I am only getting one point in the graph.

If possible please send me some document on using mango at gia24nov@gmail.com

Thanks and Regards
Gia

Hi,

One more detail I am providing regarding the above mentioned problem:

The above problem occurs when I am using these settings in the SQL properties of main database configuration page:

Name : MangoTest
Update period : 5 seconds
Driver class name: com.mysql.jdbc.Driver
Connection string: jdbc:mysql://localhost:3306/sampleDB(sampleDB is my database name)
Username: root
Password; NONE

Select statement: select * from gateway1

Now here is the main problem
when I am using
Select statement: select * from gateway1
I am getting the above problem but when I am using
Select statement: <NONE>

I am not getting the above mentioned problem but it created another problem

That is when I am changing my database manually ex:

If relay status is showing OFF(i.e.status=0) and I use:
mysql> update gateway1 set status = 1 where relayno = 'relay1';

This change is not reflecting in mango as relay status:ON. but in database I see the status of relay1 as 1 i.e OFF.

Please tell me whats happening I am totally confused.

Thanks and Regards
Gia

This message was edited 1 time. Last update was at 27/10/2007 12:51:41

Hi Gia,

The main problem from your first post is that the SQL data source only works with the first row of the result set. That is, the expectation is that your select statement will only return one row. The reason of course is that the point values are selected by field name, and with multiple rows the fields names are all the same.

To get your updating to work properly, i suggest you change your select statement to something like the following:

This should return all 3 relay states in a single rows with the field names r1status, r2status, and r3status.

Also, the reason why you cannot plot your graphs is probably because you are not logging data.

Mango documentation is available from within the application (although it is currently incomplete). Click the "?" icon in the menu for an application overview.

Finally, in your second post the second select statement is invalid. Your data source is probably failing (you should be seeing an "urgent" event). The behaviour of the data source points under invalid conditions is undefined.

Hi

Thanks for sending me the sql commands now every thing is working fine...

But, yet I am not able to plot the graphs.
I am logging the data but it is showing only one point on the graphical views.
I can see the logged data when I drag my mouse on the chart icon as tabular form.

Does mango provides plotting of graphs. such as : Pie chart, curves, lines etc. For ex: time at x-axis and value of points on y-axis....

Please help me to plot multiple points.

Thanks and Regards
Gia.

I should have mentioned before: image charts only work with numeric and multistate points, not with binary. If you really wanted, you could "fake out" your points into being numeric by, say, reading 0 and 1 as you values rather than true/false. But, i thing the statistics charts would probably give you better information. As you say though, you would not be able to see multiple points at once.

Currently the only facility for plotting multiple points is the chart at the bottom of the watchlist. The contextual documentation on that page should give you the information you need for that.

Thanks for the quick response

Now I am able to plot graphs.

What are the different types of graphs I can plot using mango and how I can do this?
Can I draw pie chart ?

Thanks and Regards
Gia.

Mango supports multiple types of charts (images, tables, statistics), but i think you are only talking about image graphs. Currently, only line graphs (value vs time) are supported.

Hi

I am logging my sensor data with Date & Time. I need to plot the graph/chart with my logged time instead of system time in order to reflect when some change occured in my sensor, relay.

Please let me know how can i implement this thing.

Thanks and Regards,
Gia

What data source are you using?

Thanks for the reply. I am using mySql Database where I log all my data for relay, sensor etc with repect to date and time. I want to reflect time stamp present in mysql database on the charts and not the time stamp of system.

By Default Mango is accepting system time and storing it in Derby database. So there may be a case where my system or web server is not working and at that time I log all my sensor data in my local database.So that When the server is up then I reflect all my data to Mango. but it takes system time and I want to log mysql Time Stamp in derby Database / mango.

regards
gia

I understand. The SQL data source currently doesn't support using assigned timestamps for values, but i can understand how that would be useful. The solution would be to extend the SQL data point to be able to specify the timestamp field within the result set. If you are capable with Java this shouldn't be too difficult a change to make.

Hi

Thanks for the reply

I have downloaded the source code for mango i.e. mango-0.9.2-src.zip
But I don't know how to compile it.
Can you send me the compilation procedure for this.
Also please let me know if some other supporting file needed to compile it and from where I can download these files.

Thanks and Regards
Gia