@MattFox Instead of starting a new thread I will explain what we are doing here. I don't think too many people are searching for Mango 3 solutions anymore.
Our dataloggers have the ability to store its data locally during a mobile data connection loss. Once the data connection is restored the logger could potentially upload 100K rows of data to the MySQL database in a very short period of time. The other eccentricity of the loggers is that the digital channels only send data on a state change. we do not get an the current digital state value on every record that is sent.
So we need to be able to read the data from earliest to latest, and if there is no new data, mango needs to be able to go back to the last non null digital point found in the already read rows of data.
So to make sure Mango reads the data into the NoSQL database sequentially in timestamp order, we use the flag method to mark which rows have been read and identify the next row to be read.
We use a SQL data source to bring the data into mango, then use a Meta data source to scale the data for display on the dashboards, trigger alarms etc, as you know.
In the SQL data source we have a series of coalesce select statements as shown:
Select date, flag,
coalesce(
(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1),
(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 1 and an1 IS NOT NULL order by date desc limit 1)
) as an1,
coalesce(
(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1),
(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 1 and an2 IS NOT NULL order by date desc limit 1)
) as an2
coalesce(
(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1),
(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 1 and i1 IS NOT NULL order by date desc limit 1)
) as i1,
coalesce(
(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1),
(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 1 and i2 IS NOT NULL order by date desc limit 1)
) as i2
from staging_all
where grd_id = 0100
and flag = 0
order by date asc
limit 1;
This works well for us and as you can see gives us the choice of picking the earliest unread or the latest read row of data.
I then have a data point in the SQL data source called zflag that had this statement:
UPDATE GRDXF.staging_all SET flag= ? WHERE grd_id=0100 AND flag=0 ORDER BY date ASC LIMIT 1;
The question mark is the integer parameter assigned via the point link which just says "Return 1;"
I tried putting that line in the SQL data source code as below without the parameter ?:
Select date, flag,
coalesce(
(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1),
(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 1 and an1 IS NOT NULL order by date desc limit 1)
) as an1,
coalesce(
(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1),
(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 1 and an2 IS NOT NULL order by date desc limit 1)
) as an2,
coalesce(
(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1),
(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 1 and i1 IS NOT NULL order by date desc limit 1)
) as i1,
coalesce(
(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1),
(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 1 and i2 IS NOT NULL order by date desc limit 1)
) as i2
from staging_all
where grd_id = 0100
and flag = 0
order by date asc
limit 1;
UPDATE GRDXF.staging_all SET flag= 1 WHERE grd_id=0100 AND flag=0 ORDER BY date ASC LIMIT 1;
This works manually in the MySQL workbench but this failed in Mango. I think the reason it fails in Mango is that the SQL data source is a read only unless the data point has the Modify Table attribute ticked. So I can't add the update statement to the data source SQL code.
I did some digging in the forum from the time when IAS was going to remove the point links after version 3.7.7 and the only comment I could find was that point links could be accomplished in the Scripting data source but didn't give much more detail on how. I apologise if the "How" was in the early form but i didn't find it.
So I started to look there and first of all I can't see how to create a connection to the MySQL database as in the SQL data source.
Then as I am writing this I started to realise that I think I am supposed to use the Scripting data source to recreate the Source Point and the Target point that was chosen as drop downs in the Point Link. And if that IS the case, that is where I am stuck.
I am researching about using Ecmascript to use target and source points to provide a parameterised integer to my SQL data point that can modify the table.
Once I have that figured out I think our system will be back to normal.
Thank you for your time in reading this lengthy post. :)
