SQL query

[BSUN_RO]

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 !

[c_lowy]

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]

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

[c_lowy]

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]

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

[Jimwang]

@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]

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

[BSUN_RO · Accepted solution]

I fixed it using this article

Thankyou all.