Read data from SQL and insert as property values

[Do1]

I have an external SQL database that records data and I want to add this to a Thingworx thing to be stored in the associated value stream.

How would you do this?

I am able to access the SQL data from ThingWorx and query the table, but I need to assign it as properties of the thing.  The external database remains the master and I want ThingWorx to read updated data from the SQL server every minute.

[ankigupta]

Hu Duan Gauche​,

You can use Scheduler Thing for that. You can setup the Scheduler to generate event every one minute. Now you can subscribe to this event and call your service to update the Property.

I hope it helps.

Thanks,

Ankit Gupta

[ankigupta]

To answer the part how to assign it:

You can create a Service which takes the input from your Sql Service and assign the values to the corresponding Properties. You will have to call this service in Subscription code of the Scheduler Thing.

[Do1]

Hi Ankit Prakash Gupta,

Thanks for the suggestion.

There are probably multiple records added between each scheduled event.

How would you only add the new records and what service would you use to add it so that the timestamp would be the one from the SQL table and not the one from when it was added as a property?

[ankigupta]

Hi Duan Gauche​,

As you mentioned that there is Timestamp field in the DataBase; you can try something like:

select * from Table1 where timestamp = (select max(timestamp) from Table1);

So, we will only have the latest row from the Sql Script.

Now, in your Service; you can use UpdatePropertyValues Service which also takes timestamp as input while updating the values.

[Do1]

Thanks Ankit Gupta​,

This is starting to look more promising...

Just wondering what we do about the records between the previous update and the last row?

[ankigupta · Accepted solution]

Duan Gauche​, So, you want to update all the rows received during that timeframe.

As per my understanding; we can create a Persistent Property which holds the max value of last Read. We have to update this value on each read.

Now instead of reading only the last value we can read all the values which have timestamp greater than the value saved in Persistent Property (i.e. max timestamp of last read).

select * from Table1 where timestamp > [[timestamp_property]];

So, we will have all the data updated after the last read in ThingWorx in an infotable.

Now we can loop in and update the values using UpdatePropertyValues Service.

[Do1]

Awesome!!!

Will give it a shot!

[ankigupta]

I am not sure about the Performance of this Process. I would suggest you to test it if you are using it.