How to "auto refresh or reload" mashup data grid based on the change in the data.

[SS_10617248]

I need to auto refresh or reload a mashup based on the change in the data.

The mashup is made up of a data grid (advanced grid widget) that gets the data using a custom service that calls an REST API. Instead of calling REST API periodically to get the latest data and refresh mashup, I need to "subscribe", probably connecting thru websocket, so the mashup gets updated (advanced grid needs be refreshed) by calling service when there is new data available. Any solution for this mechanism?

I have seen option of automatically updating/refreshing the grid data using the GetProperties "Automatically update values when able checkbox" in combination with ServiceInvokeCompleted event (which is based on websocket connection to property changes that mashup is subscribed to), but even then I am not sure how to update a property based on change in source data.

[Sathishkumar_C]

• What is the actual data source for that custom service?

You can leverage the GetProperties ["Automatically update values when able checkbox" ] service with the actual data source change event.

[SS_10617248]

Thanks! for the response, The actual data source is a Data base which I am accessing it using the REST API which returns data in JSON format. Like I said earlier, instead of periodically calling this API, I would like to notify when the data is changed. I am planning to use thingworx mashup as the GUI to display the changes in Database using the REST API. 

Even if I create a "dummy" property to leverage GetProperties Auto update checkbox, I am not sure how to change automatically the property value on change of data in the data base, without calling the API periodically.

Also, there is message bus on the server hosting REST API which gets notified change in data, is there a way to subscribe to that from thingworx to update the data on mashup?

That's why I am looking to see if there is subscription based mechanism like using WebSocket connection.

[VVM_4]

Hi @SS_10617248 ,

If I understand the question correctly, you want to know how to notify Thingworx that data has changed in the DB.

Unfortunately I don't see a straight forward way to do this.

Typically it would be a middleware that pulls and pushes data (this is something that a lot of people already use Thingworx for. So another middleware might not give you much benefit in what you are trying to achieve, unless it is a microservices approach and there are a lot of tables and data.)

Another option might be to try and install a database utility to make a REST call inside a trigger function which listens to an insert or update action on a table. Personally, I have not tried this.

Check out this stackoverflow query about something similar: calling-restful-web-services-from-postgresql-procedure-function 

This is assuming your DB is PostgreSQL. I'm sure you can find something similar for other DBs as well.

If this works, then all you need to do is create a service on Thingworx which get's notified of a change by the DB trigger function via REST and then pulls the new Data and stores it in a property. You can then use the GetProperties Auto update feature in the mashup by listening to the property value.

Do let me know of the outcome, if you are going to do this. As theoretically it should work, but when fellow community members actually solve it and confirm the solution, then it's always better knowing what can be done next time successfully or whether this approach should be avoided.

[SS_10617248]

Thank you! for the response. Initially, I was looking for a solution for a change of data in DB will automatically refresh the mashup. Here are the steps I have followed:

• I have a created a Thingworx service to call our API to get the data. (Cannot access DB directly.)
• Developed a Thingworx mashup with gridadvance widget that calls the above service (step 1). Also, added a Property and used the GetProperties service to leverage the Thingworx Platform internal runtime WebSockets mechanism to update the mashup. (When a property value changes, the mashup receives the new value and processes it by calling the service based on the binding).
• Now, there is an existing system that puts the data (a new record or updated record) in its message bus sub system (essentially a queue). So, I have written a demo program(C#) to listen the queue and set a Thingworx Property using the Thingworx API (Properties/<property Name>). (This is how I know, if there is any new or updated records in DB)
• So, the Idea is listen to the queue and when there is data/message, then call the Thingworx SetProperty (POST call) using REST API, that will trigger the mashup to call the service from step 1 which will call our API to get that latest data to be displayed.

I understand this is complicated, but I could demo it to work. But this is also not feasible solution as there are lot of moving parts and am not sure how scalable it will be and more importantly, I am calling our API to get all the data (existing and latest data) instead of just getting the latest (changed) data and pushing it to Mashup. (This is why I was looking to have WebSocket connectivity in Thingworx service.)

Thanks! for your solution. Could you please elaborate your last but one paragraph. Based on what I understood, here is the new approach I am thinking of:

update the program (C#) such that it will listen to the queue --

Here is the flow:

• Get the Thingworx Property value using Thingworx REST API, if it has no value, then call our API and get the data and set the Thingworx Property using Thingwork REST API. Otherwise use the data from properties and data from queue (which is just the new or updated record) and build a new data set and set the property using Thingworx REST API.
• Create a Thingworx service that takes the property as Input (JSON) and returns Infotable.
• Create a Mashup to call the above service to render the data from Infotable onto a gridadvance widget. Essentially, use the same mechanism of auto refresh mashups by configuring it to use Thingworx internal runtime WebSockets and receive property updates automatically using the GetProperties Service and checking the “Automaticallyupdate values when able” check box. So, when the property value changes (C# Program calling the Thingworx REST API when there is new data on listening to the queue), the mashup receives this new value and renders/refreshes.

This way, I am not calling the API regularly/periodically and also refreshing the mashup only when there is change in data.

Will this work, at least, theoretically possible?

[VladimirRosu]

I'm jumping in the middle of an existing discussion, because I would like to validate a few assumptions due to the fact I believe there is too much work or complexity and I am not sure if it's really needed.

So:

1. What exactly is the "latest data" you need to retrieve from that Database? Is it something you can determine via SQL queries (like SELECT TOP 1 ...ORDER BY date ASC?) Or you need to always retrieve all the data in the table and display it in the mashup?
2. What exactly is the definition of getting the data "when it changes" ? I have the feeling the need is for real-time, but looking at all past real-time requirements, is that really needed? For example: if the data is always max 1 second old, would that suffice? I assume that an operator is using that mashup, and 1 second would be fine, but I am not aware of the actual workflow there to validate this assumption.
3. If you can live with data of a certain age, you can actually decouple this into two functions. In this way the DB impact is always the same (does not depend on the number of users accessing the mashup), and I would expect a Select would not kill it (of course, depending on how complicated it is, it could).
   1. Create a Scheduler that retrieves the database data at 1 second interval (interval that you would need to tweak to account for DB response time etc). Then store that data in a property
   2. Use the GetProperties Auto update checkbox in the mashup to subscribe to that property.

[SS_10617248]

Thanks! for your interest. There is a different application where, lets say, data gets generated or updated (new records or updates to existing records) and that app also puts that "new" data in the queue. All I am looking is a way to "push" this data from the queue to a mashup for display, and keep refreshing the Mashup when ever the data is available on the queue. That's why in the new approach I laid out, I am seeking whether that is feasible? Essentially, the a C# program listening to the queue and when ever there is a message (data), "push" that record to the mashup for display. Hope this helps.

[VladimirRosu]

You can embed whatever logic you have in the C# program also in a ThingWorx extension, therefore having ThingWorx as a subscriber to that queue.

(If IT infrastructure allows accessing the queue from ThingWorx, otherwise you can effectively build a .NET SDK implementation that can live outside of ThingWorx, which effectively does the same thing).

However, in your workflow, there's no guarantee that in the time between receiving the queue message (trigger to read the DB) and when you access the DB to read the new data somebody did not already write more new data in the DB. You probably need to test this behavior to see if it really works.

[VVM_4]

Hi @SS_10617248 

Yes, this should work. This is exactly the middleware approach I was talking about. In this case the C# program acts as a middleware to listen to the Queue.

However, like I said, a lot of people use Thingworx as a middleware and more often than not, adding one more C# "layer" in between may not make sense. Hence, I would suggest going with @VladimirRosu's options. Either create an extension or use a scheduler thing. On the extension part, Vladmir has already provided good insights.

On the scheduler, if you are worried about pulling all data always from the database and want to avoid this, then you can store a datetime property in your thing which keeps track of the last synced datetime from your table. So essentially, you are just pulling in data after that timestamp every time your scheduler runs. This is better than maintaining a separate C# microservice as well as does not have performance impacts on your DB. You may need to fine tune the interval at which the scheduler is run for optimal performance vs real time data sync latency.

However, I do want to caution you, that while Thing properties can store tens of thousands of rows as an infotable or as a json object, they are not intended to do so (definitely not best practice in my opinion). To avoid thing level performance issues and possible data corruption (rare, but I have seen my share with json and infotables), I would suggest to minimize the number of rows here. Maybe only the latest 25 new rows can be real time and the rest can be on demand via a database query.