Community Tip - New to the community? Learn how to post a question and get help from PTC and industry experts! X
Hi, I have a database table which has a column called "type" and there are 7 different type values. I need to count # of each type when user enters a new record in this table. the result of the counts will then put into another table which allows me to use the pie chart widget.
Is this a query to an outside data source (like SQL Server) or from an internal data table?
I am using MSSQL as my database.
Hi Luwen,
If this is a DataTable in Thingworx, you can retrieve the records using the build in services like GetDataTableEntries
If this is in an External Database, then you will need the proper extension first to connect to it using the appropriate JDBC Driver and Connection string.
After that you can set up Services of type SQL Query and SQL Command to interact with that Data.
This would be similar to our MySQL tie in example from the training.
The SQL you write, is essentially the same SQL you would use within that particular database, not sure if there is a specific syntax piece you have a problem with.
After you have the Data retrieved, you can use the Aggregate service which has the ability to give you COUNT. We used this service when we went over InfoTable handling and did the Inventory for our Vending Machines.
The Wiki will have an entry on Aggregate as well in Section 5 and there is also an Example in the Examples section.
Hope that gets you the information you need.
Hi Pai, during the training you showed me a code snipet on how to invoke sql query inline in javascript and I looked everywhere and could not find it. could you please simply provide just that code in this forum?
Thanks, -LuWen
Hi LuWen,
we actually just copied that out of the Workbook, there are no SQL snippets.
It is quite easy to find SQL syntax for which ever type of Database online.