queries

[i_abidi]

Hello community,

I am just starting with thingworx and i wanted to know how to write queries (using SQL) and not Javascript to fetch some entries from a datatable created previously on thingworx

[raluca_edu]

Hi,

To do this , you should create a new service, when window opens, you have near New Service a dropdown list with: Local (Javascript), SQL( Query), SQL (Command). You should choose SQL (Query) or SQL(Command). Then , you can start writing your SQL query.

For differences between the 2 options are explained here: Difference between SQL (Query) and SQL (Command) service types in ThingWorx

Best regards,

Raluca Edu

[CarlesColl]

Well actually to query data from ThingWorx Storage (ValueStreams, Streams and DataTables) you can't use SQL, you have to rely on ThingWorx querying Snippets. The other way around it's not recommended and it isn't as easy as pointed out by raluca_edu (an data storage for ThingWorx object may change between TW Storage versions and it will fail).

[i_abidi]

CarlesCol, so if i wanted to perform a query on a datatable in Thingworx and let's say i wanted to select the entries HAVING certain conditions, i can not do it unless it's on an SQL server ? or i can still create a javascript (not so good with javascript) service to do the query?

[CarlesColl]

You have a lot of snippets to query DataTables right from Javascript. If you want to work with standard database tables instead of ThingWorx DataTables then you will need a jdbc connection (any type of database engine that suppports jdbc) to the database and then you will be able to use SQL queries.

[i_abidi]

Raluca , the thing is on my THINGWORX Dev server i only have the local javascript  option since it's a datatable on thingworx with datatable thing template and not connected to SQL server.