Data storage in Thingworx postgresql database

[mnaeem]

Hi,

I'd like to understand that how can I access or view the data stored in a DataShape, through a service created in thingworx, directly in postgresql database, which has been connected to thingworx when installed thingworx.

Thank You!

Muhammad Naeem Akhtar

[PaiChung]

If you have direct access to the persistence provider, you can find the tables which are storing the data.

You can even add another JDBC connection from Thingworx to the persistence provider to pull that info.

Just be careful since you'd be touching the base Thingworx schema.

[mnaeem]

Thank you @PaiChung.
As you can see in the attached picture, I can see the names of datashapes that have been created on thingworx composer, but I'd like to understand that how is it possible to see the data stored in those datashapes using postgresql.

[PaiChung]

A datashape is just a definition.

Did you tie those datashapes to a Stream or DataTable to store actual rows?

Also what is your use case that you would need to see the data in the persistence provider directly vs. using the OOTB services available on Streams and DataTables?

[mnaeem]

Hello @PaiChung ,

Thank you!

I don't know, whether I'm understanding it well or no. Because I'm a new user on Thingworx.

I've created an entity in which some of the properties set to persistent and base type is infotable.

I'd like to see the data of the persistent property directly in database which has been connected to Thingworx, in my case it is postgresql.

Best Regards,

Naeem

[mnaeem]

This is the property with basetype INFOTABLE and Data Shape is OutputSHIFT and it has 15 rows.

I would like to see those 15 entries directly in psql.

[PaiChung]

You probably will find that in the Thing / Thing Property? table

The DataShape is just a definition that doesn't store the actual rows.

[mnaeem]

I can access and see that data on thingworx. All is fine on thingworx.

But, I'd like to access that data directly in database, in order to understand where the persistent property data is going (Saving) in database.

Thank you!

MNA

[PaiChung]

Right there should be a Thing / ThingProperty or similar named table in the Database, that is where it would be persisted I believe.

[Malcolmlsh]

Hi @mnaeem did you manage to solve this? I am currently in a similar situation where I can see the data streaming into Thinworx but not my postgresql. 

[mnaeem]

Hi @Malcolmlsh ,

I didn't understand that where the persistent data is saving in persistent database.

Instead of stucking here I am using an external database to store data so I'm able to use the stored data in some other programs or platforms if needed.

If you would like to I can explain how I did that.

Best Regards,

Muhammad Naeem

[Malcolmlsh]

Hi @mnaeem , 

Thanks for the quick response. That sounds great, how did you do it ? Im currently connected to postgresql on Thingworx but stuck here. 

[mnaeem]

I have attached the extension that prepared for MSSQL, if you want to use other database you just have to put the jar files in lib\common folders of the extension and then import it in your Thingworx as extension.
Then create an entity in Thingworx using name of extension as base thing template of the entity.

After creating the entity you have to change the configuration parameters as per your database.

These are for MSSQL

You can ask for further assistance.

Best Regards,

Muhammad Naeem

[Malcolmlsh]

Thank you, I've managed to reach to this point, and I am able to stream data into the mashup. The problem now is that I know I'm supposed to 'insert into' the schema in my external database(postgresql) under the database thing services, but am not quite sure how to reference my attributes.

Did you do this as well? 

[mnaeem]

Sorry I didn't understand your point.
If you are able to connect to your external database then you are able to see the isConnected property of your database thing equal to true.
Next you can create services in your database thing to store data in your database and read data from your database if it is available otherwise no.

[Malcolmlsh]

You are correct, however I'm slightly confused as to how to write my script to store data in your database. Would you kindly be able to assist me on this? How does your script look like? 

Thanks

[mnaeem]

This a basic query to get data from your database.

Hope it will help.

[Malcolmlsh]

Hi @mnaeem 

It says that my table name doesn't exist. Can't seem to point correctly to my schema in PostgreSQL. What am I doing wrong here?

Thanks!

[mnaeem]

Hi @Malcolmlsh ,

You are trying to get data from schemas created in the persistent database. I've mentioned that I wasn't able to get persistent data using database queries.

For that reason I used an external database to store data streaming in thingworx in the external database (Created table then inserted values in that table using database queries).

Best Regards,

Naeem

[Constantine]

Hello @Malcolmlsh,

You can use INSERT, UPDATE and DELETE statements in your SQL services, not only SELECTs. Try something like this:

INSERT INTO MyTable (myColumn1, myColumn2) VALUES ([[value1]], [[value2]]);

Where value1 and value2 are service parameters. More info about INSERT syntax is here: https://www.w3schools.com/sql/sql_insert.asp

Regards,
Constantine

[Malcolmlsh]

hi @Constantine ,

it says that my table does not exist when I insert. I don't know what table name to reference to, nor do I know how to point to the fields i have in the database properties.

Thanks.

[Constantine]

@Malcolmlsh, let's take one step back -- can you tell a bit more about your use case? What data do you want to insert, where and why?

/ Constantine

[Malcolmlsh]

Sure thing. I have data streaming in from a machine into Thingworx, but now I wanted to store this data into an external database. I want to insert the data points im getting from the machines into the postgreSQL.  Currently I have values streaming into Thingworx already. 

Thanks 

[Constantine]

@Malcolmlsh, so what schema do you have in that external database? I.e. what tables do you have, what columns, their data types, etc.? Based on this you would write your SQL statements to insert / select / delete / update data there.

/ Constantine

[Malcolmlsh]

I have this in the external database that I manually keyed in. I don’t know what’s wrong.

[mnaeem]

@Malcolmlsh

1- Check whether cncmachine table created in your external database or not

2- There are no inputs in the service that you have created. You need to add inputs of all the parameters that you have written in Values().

[Malcolmlsh]

here it is, sorry i didn't attach it in the previous reply

[mnaeem]

Who has created that table and how it has been created?

[Malcolmlsh]

Done. Still says 'sevenonetwo' does not exist. 

[Malcolmlsh]

Hi, @mnaeem  and @Constantine , thank you for the help thus far. I have managed to connect and write data into the external database currently. If I want to automatically write data in upon any change in the fields, would it be creating a subscription to the database thing with this script : 

var params = { 

             x_pos: me.x_pos, ...

} 

var result = Things['database 1'].InsertRecords(params);

where database 1 is the name of my database thing? 

Thanks!