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

Data storage in Thingworx postgresql database

Highlighted
Alexandrite

Data storage in Thingworx postgresql database

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

42 REPLIES 42
Highlighted

Re: Data storage in Thingworx postgresql database

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.

Highlighted

Re: Data storage in Thingworx postgresql database

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.

 
Highlighted

Re: Data storage in Thingworx postgresql database

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?

Highlighted

Re: Data storage in Thingworx postgresql database

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

Highlighted

Re: Data storage in Thingworx postgresql database

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

OutputSHIFT.JPG

 

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

OutputSHIFTpsql.JPG

Highlighted

Re: Data storage in Thingworx postgresql database

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

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

 

Highlighted

Re: Data storage in Thingworx postgresql database

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

Highlighted

Re: Data storage in Thingworx postgresql database

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

Highlighted

Re: Data storage in Thingworx postgresql database

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. 

Highlighted

Re: Data storage in Thingworx postgresql database

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

Highlighted

Re: Data storage in Thingworx postgresql database

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. 

Highlighted

Re: Data storage in Thingworx postgresql database

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

mnaeem_1-1583839086857.png

You can ask for further assistance.

 

Best Regards,

Muhammad Naeem

Highlighted

Re: Data storage in Thingworx postgresql database

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? 

Highlighted

Re: Data storage in Thingworx postgresql database

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.

Highlighted

Re: Data storage in Thingworx postgresql database

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

Highlighted

Re: Data storage in Thingworx postgresql database

This a basic query to get data from your database.

mnaeem_0-1583840382204.png

 

Hope it will help.

Highlighted

Re: Data storage in Thingworx postgresql database

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!

 

 

 

Highlighted

Re: Data storage in Thingworx postgresql database

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

Highlighted

Re: Data storage in Thingworx postgresql database

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

Highlighted

Re: Data storage in Thingworx postgresql database

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.

Highlighted

Re: Data storage in Thingworx postgresql database

@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

Highlighted

Re: Data storage in Thingworx postgresql database

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 

Highlighted

Re: Data storage in Thingworx postgresql database

@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

Highlighted

Re: Data storage in Thingworx postgresql database

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

Re: Data storage in Thingworx postgresql database

@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().

mnaeem_0-1583954593099.png

 

Highlighted

Re: Data storage in Thingworx postgresql database

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

Highlighted

Re: Data storage in Thingworx postgresql database

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

Highlighted

Re: Data storage in Thingworx postgresql database

Done. Still says 'sevenonetwo' does not exist. 

Highlighted

Re: Data storage in Thingworx postgresql database

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!

 

Announcements

Thingworx Navigate content has a new home! Click here to access the new Thingworx Navigate forum!