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

Community Tip - Did you get called away in the middle of writing a post? Don't worry you can find your unfinished post later in the Drafts section of your profile page. X

syntax run sql in javascript?

luwenya
1-Newbie

syntax run sql in javascript?

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,
5 REPLIES 5
AdamR
12-Amethyst
(To:luwenya)

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

luwenya
1-Newbie
(To:AdamR)

I am using MSSQL as my database.

paic
1-Newbie
(To:luwenya)

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
1-Newbie
(To:paic)

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
1-Newbie
(To:luwenya)

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.



Top Tags