Thingworx Log to MsSQL database

[leoRW]

Hi everyone, let me start by saying I am very new in Thingworx.

My goal is to use MsSQL database to log data coming in through Kepware (OPC).

I am trying to do so using a Value Stream, pointing locally to a thing which contains all the data I want to store, and a Persistence Provider that connects it to my database in MsSQL.

All the properties have the checkbox "logged" active, and Data Change Type is set to "Always".

The data is coming in fine, and I also verified connection to the database with an additional thing using the MSSqlServer template. This thing is configured exactly like the persistence provider (using JDBC driver and connection string) and I can successfully send queries to the database.

I do not get any error message in the diagnostic logs, nor configuration errors, but the properties I have configured in the Value Stream are not coming into the MsSQL tables (or no table is being created in the database).

Am I doing something entirely wrong? Or is there some other way to log the data as I am trying to?

Any help or tips is appreciated!

Leo

[raluca_edu]

Hi,

By default, Value Stream data is automatically saved in the database (Persistence Provider set when installing Thingworx). 

At the time you are creating the Value Stream, you can choose the Persistence Provider (e.g. in 8.4, you can set Influx Persistence Provider, in lower versions of Thingworx you cannot choose persistence provider).

So, if you created a second MSSQL database and also made a Thing that connects to this database, it will not act as a Persistence provider, you need to create/write data in that database, nothing will be done automatically.

Taking in consideration the above explanations, can you detail what you want to achieve? You want to use the data already in Persistence Provider? You want to take the Vale Stream entries and copy in another database? 

Regards,

Raluca Edu

[leoRW]

I have created a Persistence Provider using the MssqlPersistenceProviderPackage (the other option being the Influx), and that should be the link between my Mssql database and the value stream.

I simply want to log the properties bind to my remote thing to be stored in a Mssql table.

Using Value Stream with persistence provider is just one of the ways to do it I suppose.

I am now also trying to achieve the same result with a service written for my MSSqlServer thing, as in:

INSERT INTO *TableName* (Col1, Col2, Col3)
VALUES (Value1, Value2, Value3)

However, in this case what I am missing is the syntax to reference a remote thing property (which is the value I want to log) in such query. I was able to write integers or text into the proper columns though.

[raluca_edu]

For getting values of a thing check the service GetPropertyValues, it will return in an infotable the thing name, the properties name and the current values, you can take the values from there and insert in your database.

Regards,

Raluca Edu

[leoRW]

I see that when executing the GetPropertyValues on my remote thing I am getting the properties displayed, but how do get them to be automatically insert them in the Mssql table?

This may be a really simple question, but that's just how new I am to the system.

Regards,

Leonardo

[raluca_edu · Accepted solution]

Hi Leonardo,

Only Value Stream is  automatically logging values.

To aquire same behavior of the logging, you need to create a service, call GetPropertyValues inside, take the result , get the values and insert them into your database. This new service can be executed every time the property values change.

Best regards,

Raluca Edu

[leoRW]

Thank you for your reply.

I was trying to go down the Value Stream approach because I am not so good at coding java, but eventually I put together a couple of services and it worked out fine.

[Malcolmlsh]

 Hi @leoRW  ,

Could you share how you did this? I believe I am stuck at where you were, that is being able to manually write data into my external database (using postgresql in my case) but not sure how to automatically write data in (I have data streaming in from kepware too and can view i in Thingworx).

From my understanding I have to use the AnyDataChange event, but now exactly sure how to configure everything.

Thanks! 

[leoRW]

Hi! I have defined a property as trigger for the data logging and created an event (in your case it may well be AnyDataChange event). Then I have my service to write data in MS SQL as subscription to that event.

Trigger event -> subscribed service.

From your message you said you're getting the message "relation <tablename> does not exist", so before you configure anything make sure you are connected to the SQL database. Try a simple SELECT() query to see if you can read data from it.

Hope it helps.

[Malcolmlsh]

Hi @leoRW  thanks for getting back. 

I am now connected to the database as I managed to manually write data to the schema using INSERT INTO () VALUES(); , 

Now I have a  service under my database thing has the  INSERT INTO() VALUES() sql script. Where do I create the subscription that you mentioned and under what Thing would it be on? What do I write into this subscription?

For more context, my database is streaming data from an industrial connector, I can get values using QueryPropertyHistory as of now. 

Appreciate the help, thanks! 

[leoRW]

Does the script INSERT INTO() VALUES() work properly? Because the subscription will only automate the service execution, not change how it works.

To set up the subscription:

- On Thing1 (does not have to be the DB connector), set Property1 as trigger by creating an Alert. Any event works as well

- On DB connector thing, create a subscription

- In Subscription info, select Thing1 as source, and enable it

- In Inputs, select Alert (or custom event) and Property1 as Property

- The subscription script should run your data insert service: me.DataInsertService()

When the alert on Property1 rises, the DataInsertService is executed.

Also understand that the Value streaming is an entirely different way of logging data, so don't mix them up.

[Malcolmlsh]

Yes the INSERT INTIO() VALUES() work fine because when i manually key in the input and check back on postgresql, i see my new rows generated by the manual input. 

-Instead of an Alert I just create an event using the "AnyDataChangeEvent" Datashape. This works as well right?

-created a subscription on DB thing,

-Selected thing1 as source and enabled, 

-changed input "AnyDataChange" which doesn't require a property selection,

-me.DataInsertService is referring to the INSERT INTO() VALUES() script right? 

How do I check that the "AnyDataChange" event is being 'activated'? any kind of log i can see for a feedback response? My Database still doesn't seem to be automatically streaming new values. 

Thanks! 

[Malcolmlsh]

Hi @leoRW  ,

I managed to stream the data already, thanks a bunch! However it only worked when i created the event on the database Thing itself and attached the "AnyDataChange" Datashape, because  I binded the properties to by db Thing as well I suspect. However I think it won't work if i want to add another machine and therefore a separate Thing altogether might perhaps be a better choice.

Thanks a lot once again! 

[leoRW]

Makes sense, now that you get how it works you know you can reference another Thing for subscriptions. Depends on your use indeed.

Glad that it worked, good job!

[Constantine]

Hello @Malcolmlsh,

The things that actually generate data (those with remotely bound properties) -- they all inherit from some thing template, like MyWorkstationTemplate, right? (if they just use RemoteTemplate, you would need to create your own thing template, which inherits from it)

This thing template is the right place to define your per-device business logic, such as "push all updates to external database". To do this, you need to add a subscription like DataChange or AnyDataChange on that template, and call your database thing's insert service from there. This way, all things that use this template will behave this way, so you won't have to do any additional programming when you create new things.

Check out Help for Thing Templates and Thing Shapes, if you need more details -- they are quite well explained. Also there are tutorials and trainings for that.

Regards,

Constantine

[Malcolmlsh]

Hi @leoRW , 

Thanks once again for your help last week! However now I'm trying to adding a new machine to a new schema in PostgreSQL. I have followed your steps and am stuck at the last portion.

For the me.DataInsertService() you mentioned, I would have to point my service parameters to the properties of Thing1. How do I do that?  For the first database I did last week, it was easy because my properties were in the same database thing, so i had to just type me.[property1] to point to the respective property. Now I have to point to my Thing1's property.

I have a feeling this a javascript syntax that I'm not sure about.

Thanks! 

[Constantine]

Hello @Malcolmlsh,

Try this: Things["Thing1"].property1

Check out Modeling section in ThingWorx Help Center, it explains stuff like that in more detail: http://support.ptc.com/help/thingworx_hc/thingworx_8_hc/en/#page/ThingWorx%2FHelp%2FComposer%2FModeling.html%23wwID0E1EUU

Regards,
Constantine

[Malcolmlsh]

Hi @Constantine , 

Thanks for helping out, this is exactly what I was looking for.

Cheers!