5 Replies Latest reply on Feb 17, 2014 9:14 AM by paic RSS
    luwenya Explorer

    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, 
      • syntax run sql in javascript?
        adamr Creator

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

         

        • syntax run sql in javascript?
          paic Collaborator

          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.