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

Community Tip - Want the oppurtunity to discuss enhancements to PTC products? Join a working group! X

SQL query

BSUN_RO
7-Bedrock

SQL query

Hi, I am trying to make a query to an sql database. I have Thingworx 8.4.2 and I don't find the option to make an SQL type service, only javascript. I wrote the following code:

 

"try {
var query = "SELECT * FROM `info`";

logger.debug("DatabaseController.JavaScriptQuery_PersonsTable(): Query - " + query);

var result = me.RunDatabaseQuery({query:query});
} catch(error) {
logger.error("DatabaseController.JavaScriptQuery_PersonsTable(): Error - " + error.message);
}"

 

In the application log I get the following error:

[O: c.t.s.a.AuthenticationFilter] [I: ] [U: ] [S: ] [T: http-nio-8015-exec-7] Servlet execution threw an exception

 

Do you have any solution? Thankyou very much !

 

1 ACCEPTED SOLUTION

Accepted Solutions
BSUN_RO
7-Bedrock
(To:BSUN_RO)

I fixed it using this article

Thankyou all.

View solution in original post

7 REPLIES 7
c_lowy
16-Pearl
(To:BSUN_RO)

Hi @BSUN_RO,

 

You can run SQL queries from a ThingWorx service by creating a Thing with Thing Template "Database" assigned and connecting to an external database using the Configuration tab of that Thing. You will find at the following link some JDBC Configuration Examples. Once such a Database Thing is connected to your database you can create and run services of type SQL (query) and SQL (command) :

 

 

You can also refer to How to connect ThingWorx with an external database without extensions.

BSUN_RO
7-Bedrock
(To:c_lowy)

I don't have a dropdown button to select SQL.

c_lowy
16-Pearl
(To:BSUN_RO)

The dropdown list is not available for Things based on Templates like GenericThing :

 

 

It is only available for Things based on the Database ThingTemplate :

 

 

Which ThingTemplate are you using ?

 

BSUN_RO
7-Bedrock
(To:c_lowy)

It's not showing even with a Database ThingTemplate. I even made a ThingTemplate like in this article .

Jimwang
14-Alexandrite
(To:BSUN_RO)

@BSUN_RO ,

 

Which account do you use to create the DB thing based on the default Database Thing Template? If you use Administrator account, do you get the same issue?

Have you tried other Browsers (Chrome or Firefox) and see if it is related to browser?

Can you show the full screen of the Thing and Thing Template?

BSUN_RO
7-Bedrock
(To:Jimwang)

From the Administrator account I can select SQL. What permissions do I need for other user ?

BSUN_RO
7-Bedrock
(To:BSUN_RO)

I fixed it using this article

Thankyou all.

Top Tags