ThingWorx's JDBC extensions - Relational Database Management System and the JDBC Extensions allow ThingWorx to connect to variety of different databases. With that comes a natural question how and what sort of SQL statements could be executed via these extensions? Note: Importing the JDBC extensions i.e. the RDBMS and JDBC Extensions, creates a Database Template for that particular database. If you are working with RDBMS extension then Template of corresponding Database will be created with similar name e.g. importing the RDBMS Extension for Oracle 12 will create Template named OracleDBServer12. While importing the JDBC driver using the JDBC extension will create Template name based on the JDBC driver used or a custom name could be given. Following examples and SQL statements are adhering to Oracle's SQL*Plus standard, however these can be easily adapted to the type of RDBMS you intend to work with. Topics How to create SQL Service in ThingWorx entity Types of SQL Statements Examples on SQL Service usage and some extended use cases / examples How to create SQL Service in ThingWorx Navigate to the Thing implementing the Database Template, e.g. OracleDBServer12 2. Click on the Services section under the Entity Information and click on Add My Service 3. A new service creation section will come up, change the Service type of JavaScript (this is default selection) to either SQL (Query) or SQL (Command) depending on the type of SQL you are to create under this particular service 4. Here's quick example on creating SQL (Query) service which takes name as input for a select * sql … Statement, i.e. it returns complete set of rows and columns from any given table on which the user has the access to perform Select Note: BaseType defaults to Infotable when creating SQL (Query) service and the returned number of rows are restricted to 500. Therefore, if table contains rows more than 500, ensure to change the Max Rows parameters 5. Example on creating SQL (Command) service that delete all the rows from the database table Note: The Base Type defaults to Number when using SQL (Command) Additional information: When creating a SQL service, apart from providing changing the Service Info and Inputs /Outputs, 3rd section Tables/Columns allows users to explore the Tables and their respective columns as part of that particular user's schema - meaning the objects on which the user has select rights in his schema in the database. Types of SQLs This is not an exhaustive list, rather contains most commonly used types of SQL statements 1. Data Definition Language (DDL) a. Create, Alter and drop schema objects b. Grant and Revoke privileges and roles 2. Data Manipulation Language (DML) a. Insert b. Delete c. Select Examples for SQL Service usage and some extended use cases / examples 1. Data Definition Language (DDL) a. Create statement b. Alter statements c. Drop statement d. Flashback statements (Oracle specific) e. Grant statement f. Rename statement 2. Data Manipulation Language (DML) a. Insert statement b. Delete statement c. Select statements Use cases - Case 1 : Backing up DataTable DataTable objects in ThingWorx are for quick lookup of data and they are most performant till ~100K rows. Exceeding rows over 100K in a DataTable makes it highly susceptible to performance issues in terms of querying or writing to it. Unless, there's sharding on the persistence provider or multiple persistence providers used - JDBC connectivity to external data stores like RDBMS systems could help in keeping up with growing number of rows in DataTables. RDBMS tables are more than capable of storing very large amount of rows without being taxed over the performance. JDBC extension could be used to do just that in a use case requiring backing up DataTable or any Data Storage objects from ThingWorx for that matter. Here's one quick example using one of the Insert SQL service shown above to back up the entire DataTable to the Oracle's DB table. Following ThingWorx JavaScript service wraps the InsertIntoBULKDATAINSERTDT SQL service: // result: INTEGER // getting total row count in the DataTable var totalCount = Things["BulkInsertDT"].GetDataTableEntryCount(); var params = { maxItems: totalCount /* NUMBER */ }; // result: INFOTABLE // DataTable service to fetch all the rows from it var allData = Things["BulkInsertDT"].GetDataTableEntries(params); // looping over the result fetched above to get all the rows for insertion for (var i = 0; i<totalCount; i++) { var result = allData.getRow(i); // mapping the data for insert var params = { LongCol3: result.LongCol3 /* LONG */, numcol1: result.NumCol1 /* NUMBER */, StringCol2: result.StringCol2 /* STRING */, IntCol4: result.IntCol4 /* INTEGER */ }; try { // result: NUMBER // calling the SQL Service InsertIntoBULKDATAINSERTDT created under a DB Thing called OracleDBThingNew var result = Things["OracleDBThingNew"].InsertIntoBULKDATAINSERTDT(params); } catch (err) { Logger.info ("Failed to insert the values" + err) } }
View full tip
