Query the latest data point timestamp from NoSQL DB

BG

Hello All,

Can any one tell me how to query the NoSQL database for a data points latest time stamp?

We are reading data from a MySQL database and I use a flag column to determine if a data point has been read into the Mango NoSQL tables. At the moment I am just triggering the flag to change from a 0 to 1 every 30 seconds. This used to be enough time for Mango to read the MySQL data points with a 0 flag. But as I am adding more data points to the system, the flag is changing to soon and I am missing data points going into Mango. For various reasons, I can't extend the time of the trigger any further.

So now for a change of procedure, I would like to build an update statement in a SQL data point to check if the time stamp of the MySQL data point is greater than the last time stamp in the NoSQL database.

Thank you for your advice

Cheers

Brian

phildunlap

The easiest way to get that information would be to ask Mango for the latest pointValue. It is called NoSQL because there are no SQL statements to get data. Many NoSQL databases do have some kind of query language, but our embedded NoSQL database does not. If you want to query it, query Mango.

To do what you're describing, it sounds like you'll have a Scripting data source something like....

update = updateMySqlReadField
p = Mango's data point you're reading into
execute on whatever cron you like...

update.set(p.time)

And then an update statement (modify table only!) something like....

update myTable set flag=1 where flag=0 and ts<?

with the parameter ? set the appropriate parameter type.

Edit: I seem to remember you using a Point Link, You can do the same thing in a point link, just add the point whose time you wish to pass to the update statement to the link's context.

BG

Hello Phil,
You have a good memory. I am using the point links to set the parameter for the update statement. I will give your solution a try in the next couple of days.

Cheers

Brian

Pedro

@phildunlap Does p.time definitely return the latest logged point, or does it return the latest time of the cached point value?

Do functions like past() last() since() after() etc evaluate points up to the latest logged value, or do they extend through all the cached data that has not been logged yet?

What about the Mango API points? Do they all end at the latest logged point, or do they extend into cached data?

Thanks.

phildunlap

Hi Pedro,

Good considerations! I think....

last() and lastValue() will check the cache for the value(s) requested. The cache will expand to the size requested by filling in values from the databaes. Subsequent data will flow through the cache to the databases. The first call to a higher size can get to the database for the last few, then, but the rest will go to the cache.

since() will query the database, then include any newer values from the cache. The same is true of between().

before() will look in the cache, and not finding it check the database, after() will check the database then check the cache

past(), prev(), and previous() return statistics options, which rely on between()

BG

Hello Phil,
I am sorry to ask for more help on this. I have next to no Java programming experience.

I am using the point links and have a source set up as the timestamp data point, I have the target set as the flag data point. Both of these are on the SQL data source with the flag data point as the modify table only point.

I have managed to pick up the both the time and the value from the source.
Clicking the script validate icon:

var latest = source.value;
return latest;

Script result: 2016-04-01 14:20:00.0

or

var latest = source.time;
return latest;

Script result: 1459520400000

I think the value would be better to work with when comparing to the MySQL select result.
Because of the flag update sequence, the latest value for the timestamp point will be the same as the last value from the MySQL table.

SELECT date FROM staging_all_copy WHERE id = 1 and flag = 0 ORDER BY date ASC LIMIT 1;

Now here is my problem.

Ideally I would like my flag point that updates the table to use the date returned by the point link date in its update statement. The flag point has this statement:

UPDATE staging_all_copy SET flag=1 WHERE id=1 AND date=?;

I have the parameter set as Date and Time and I have tried the default date format (yyyy-MM-dd"T"HH:mm:ss). I have also tried the date format with out the "T" in the middle. I have also tried a date format of yyyy-MM-dd HH:mm:ss.0 since the returned value has the millisecond value.

None of these settings are updating the flag.

Any suggestions would be greatly appreciated.

Thank you for your help.

Cheers

Brian

phildunlap

Hi Brian,

I think i may have not given you a complete answer last time. It slipped my mind that you need to set the sql point with a string like:
('2016-04-01 14:20:00.0') for yyyy-MM-dd HH:mm:ss.0

So, the values to replace the SQL statement need to be in parenthesis and with single quotes around certain types (including 'date and time' parameters, I believe).