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

[slangley]

Hi @Malcolmlsh.

So from reading through this thread, it appears that you are storing your data in 2 places, thus doubling your storage requirements.  But we haven't seen anything in this discussion to warrant the need to do so.  Some examples where we would recommend storing information in an external database include:

• Offloading query processing to another server for performance reasons
• A pre-existing database existed before the implementation of ThingWorx that would need to interact with

There may be other scenarios, but just wanted to provide some insight as to when it might be necessary to do this.  These are definitely not requirements for communicating with external systems from ThingWorx.

If you have questions on OOTB services for exporting data, or other communication methods from the platform with external systems, please let us know.

Regards.

--Sharon

[Constantine]

Hello @mnaeem,

Take a look at property_vtq table.

/ Constantine

[mnaeem]

Hello @Constantine ,

Thank you.

Still not able to see the data. The property names are Log and Shifts.

Regards,

Naeem

[Constantine]

@mnaeem, So what happens when you execute something like this?

SELECT convert_from(decode(value, 'escape'), 'UTF-8') FROM property_vtq WHERE id = 'AssyMachine1#Thing' AND name = 'Shifts';

I don't have a running Postgres to test it right now, but I hope this gives a direction.

/ Constantine

[mnaeem]

Hello @Constantine ,

Thank you!

It is giving the following error.

ERROR: The function decode(bytea, unknown) doesn't exist

LINE 1: SELECT convert_from(decode(value, 'escape'), 'UTF-8') FROM p... ^

HINT: No function found with name and types of arguments provided. The types may need to be converted explicitly. SQL state: 42883

Character: 21

[Constantine]

Sorry, please try encode instead of decode.

/ Constantine

[mnaeem]

Hello @Constantine ,

Thank you very much for your response.

It is giving me the same error.

Now the only difference from the previous error is as:

with decode: ERROR: The function convert_from(bytea, unknown) doesn't exist

with encode: ERROR: The function convert_from(text, unknown) doesn't exist

If we see the data of property_vtq under the name of column value, it is written bytea.

Regards,

Naeem

[Constantine]

Well, the thing is -- your values are there on that "value" column, encoded as binary bytea format: https://www.postgresql.org/docs/9.1/datatype-binary.html The trick is to convert it into something that you can use (e.g. text, number or JSON).

Given the amount of effort required to make it work I wonder what is your use case, what are you trying to achieve by doing it? Going directly into live ThingWorx database is almost always a bad idea for many different reasons (including performance, security, reliability, etc.)

/ Constantine

[mnaeem]

Than you @Constantine ,

I'm trying to access data directly in database, just to make myself understand that where the data is saved.
In case if I need to use that stored data somewhere else (outside Thingworx platform) or for some other purposes (in those cases I need to know how can I access/see it directly in database.)

Best Regards,

Naeem

[PaiChung]

You can easily create export files or directly expose that to other systems using Thingworx.

You can either create JSON files in file repositories or create Services that pull information which automatically become available as REST API calls to external systems (provided proper auth and permissions)

[Constantine]

In case you need it somewhere else you should rather expose it via some REST endpoint, or push into an external system. Like I said, accessing it directly in the live database is a bad idea (think about in-memory caching, security, data (in)consistency, etc. etc.)

/ Constantine

[mnaeem]

Thank you @Constantine , @PaiChung ,

I can access data on thingworx and also using REST API, all is fine.

I can understand the security issues etc.

I just want to make myself understand that where is the data in database.

[mnaeem]

Attaching the result of persistent property Shifts with the basetype datashape = OutputSHIFT. This is the data that I'd like to see in database.

It has four fields with the following basetypes DATETIME, DATETIME, STRING and NUMBER

Thank You!