Skip to main content
D
Do11-Visitor
1-Visitor
March 9, 2017
Solved

Read data from SQL and insert as property values

  • March 9, 2017
  • 2 replies
  • 5916 views

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.

Best answer by ankigupta

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.

2 replies

A
ankigupta5-Regular Member
5-Regular Member
March 9, 2017

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

A
ankigupta5-Regular Member
5-Regular Member
March 9, 2017

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.

D
Do11-VisitorAuthor
1-Visitor
March 9, 2017

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?

A
ankigupta5-Regular Member
5-Regular Member
March 9, 2017

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.

Terms & ConditionsAccessibility statement