Skip to main content
L
leoRW1-Visitor
1-Visitor
September 2, 2019
Solved

Thingworx Log to MsSQL database

  • September 2, 2019
  • 17 replies
  • 6809 views

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

Best answer by raluca_edu

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

17 replies

raluca_edu
raluca_edu17-Peridot
17-Peridot
September 2, 2019

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

L
leoRW1-VisitorAuthor
1-Visitor
September 2, 2019

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
raluca_edu17-Peridot
17-Peridot
September 2, 2019

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

    Terms & ConditionsAccessibility statement