cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Thingworx Log to MsSQL database

SOLVED
Regular Member

Thingworx Log to MsSQL database

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Thingworx Log to MsSQL database

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

6 REPLIES 6

Re: Thingworx Log to MsSQL database

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

Re: Thingworx Log to MsSQL database

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.

 

Re: Thingworx Log to MsSQL database

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

Re: Thingworx Log to MsSQL database

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

 

 

Re: Thingworx Log to MsSQL database

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

Re: Thingworx Log to MsSQL database

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.