Unable to execute a Thingworx SQL Select query service

Question

Hi folks,

I am not able to to execute a simple select query where the service type is "Query". Even with Insert query as command it is not working out as well. The database is PostgresSQL and the DB thing is connected as well.

But on execute of the select query it is giving, error as: "Unable to Invoke Service test on PostgresDB_Thing : Execute Query failed. Please check Database logs for more details."

It seems there is some permission required on the database, I have checked the owner group in postgres SQL table, where "txadmin" user is selected.

Still not able to figure out where am I going wrong to provide permission.

Please provide some suggestions here.

I have attached the database log error below:

Rocko · Accepted Answer

There are three problems in that log exerpt:

-Wrong Syntax

-Wrong table name

-Permission denied

Since you get to the last one I suppose we can ignore the previous errors as they were fixed.

I suggest to use pgAdmin, or any other tool (e.g. psql) to connect to the database with that txadmin user and try to run your query. Once it runs there, it should run via ThingWorx. This seems to be a DB issue, not a TWX problem.

Here are some links on how to configure your postgres privileges:

https://www.bytebase.com/docs/how-to/postgres/permission-denied-for-table-postgres/

https://stackoverflow.com/questions/15520361/permission-denied-for-relation-in-postgresql

Jimwang · Answer

Per the message "permission denied for table BrinePlant_Stream", it looks like the database user doesn't have permission to execute the query. Login to the database via psql and grant the user permission on the database with below sql command:

sql>grant all privileges on database <database name> to txadmin;

e.g: grant all privileges on database thingworx to txadmin;

Make sure you are using the user "txadmin" to connect to the database, you can check the configuration tab in the database thing

Sign up

Please use your PTC eSupport account.

Welcome to the PTC Community

Please use your PTC eSupport account.

Scanning file for viruses.

This file cannot be downloaded