syntax run sql in javascript?

[luwenya]

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.

I'd like to write a javascript service. could you please tell me the correct syntax in javascript for:

1. invoking a raw sql command and query in a javascript?

2. if a sql query result is a infotable, what's the syntax to index each column value? I know how to iterate through each row.

3. do you have any suggestion on how to do this more efficiently?

Thanks,

[AdamR]

Is this a query to an outside data source (like SQL Server) or from an internal data table?

[luwenya]

I am using MSSQL as my database.

[paic]

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.

[luwenya]

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

[paic]

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.