Learn how to connect, query, insert, and handle data from an external database.
This project will introduce how to utilize and incorporate an external database with the ThingWorx Platform.
Following the steps in this guide, you will connect the ThingWorx Platform to a database, insert data, execute stored procedures, and query data.
YOU'LL LEARN HOW TO
NOTE: The estimated time to complete this guide is 60 minutes.
Download the attached DatabaseApplication.zip and import it into ThingWorx Composer. This download will provide examples that can be used as a reference or as a final copy as you walk through this guide.
In this tutorial, you will learn how to query data and write data to live external databases. The zip file provided contains the following files and entities as a completed version of the scenario to be covered. Import the .twx files into ThingWorx in any order. Run the SQL Server table scripts in a database you have setup followed by the stored procedures.
Name Description Type
|SQLServerDatabaseController||A connected entity for use with an SQL Server database||ThingWorx Entity|
|OracleDatabaseController||A connected entity for use with an Oracle database||ThingWorx Entity|
|AddStudentToCourse.sql||Stored procedure to add students to a course in a SQL Server database||Database Stored Procedure|
|GetStudentInCourse.sql||Stored procedure to information about students in a course in SQL Server database||Database Stored Procedure|
|Course.sql||SQL Server table representing Courses||Database Stored Procedure|
|Person.sql||SQL Server table representing Students||Database Stored Procedure|
|PersonCourses.sql||SQL Server table representing the connection between Students and Courses||Database Stored Procedure|
This guide will use connections to a SQL Server database, however you can use ANY database that can be used with the Java programming language. Based on the database you will be using, please perform the installation process for that database and create a user with READ, WRITE, and EXECUTE permissions. Ensure the password is set for this user and keep in mind that a UI for the database connection is not needed, but it can make things a bit simpler.
Database Download Installation Documentation Jar File Download
|SQL Server Download||SQL Server Installation||SQL Server Jar File|
|Oracle Download||Oracle Installation||Oracle Jar File|
Please follow the below steps to enable a connection between ThingWorx and your database of choice:
ThingWorx is based on java programming language and can make connections to any database that supports a java-based connection. All that is needed is to add the .jar file for the database to the lib folder of Tomcat folder and setup the connection in the ThingWorx Platform. Follow the below steps to get started creating the connection.
To create a connection and begin working with an external database, you will need to create a database Thing and set up the connection string and credentials. (To see the final version of this entity, you can look at SQLServerDatabaseController.)
In ThingWorx Composer, click the + New at the top of the screen.
Select Thing in the dropdown.
Name the Thing DatabaseController and select Database as the base type.
In this tab, you will enter the class name of your driver, the connection string for that database connection, and the credentials to access the database.
Keep in mind, the JDBC Driver Class Name, JDBC Connection String, and the connectionValidationString values are all database type specific. For example, to connect to a SQL Server database, the below configuration can be used.
Title Description Example
|JDBC Driver Class Name||The specific class name of the driver being used for the connection.||net.sourceforge.jtds.jdbc.Driver (SQL Server) or oracle.jdbc.driver.OracleDriver (Oracle)|
|JDBC Connection String||The connection string to locate the database by host/port/database name.||jdbc:jtds:sqlserver://server:port/databaseName (SQL Server) or jdbc:oracle:thin:@hostname:port:databaseName (Oracle)|
|connectionValidationString||A simple query that should always work in a database.||SELECT GetDate() (SQL Server) or SELECT SYSDATE FROM DUAL (Oracle)|
You've just created your first database connected Thing! Now jump to the next section and let's begin to query the database for information.
Now that you're connected to the database, you can begin querying the database for information and the flow of data. The queries and data shown below are based on the table scripts provided in the download.
Examples of how the ThingWorx entity should look can be seen in the SQLServerDatabaseController and OracleDatabaseController entities.
As you may have noticed by working in ThingWorx and developing applications, an InfoTable is often used to work with large sets of data. An InfoTable is also how data is returned from a database query. If you're expecting only 1 value in the result set, the data will be held in the first index of the InfoTable. If you're expecting rows of data coming back, expect there to be rows of information inside of the InfoTable.
Follow the steps below to set up a helper service to perform queries for the database. While other services might generate the query to be used, this helper service will be a shared execution service.
3. Set the Output as InfoTable, but do not set the DataShape for the InfoTable.
4. Add the following parameter:
5. Add the following code to the new service:
6. Click Save and Continue. Your service signature should look like the below example.
You now have a service that can run queries to the database. This is also a simple method to test/troubleshoot the database connection or a quick query.
Run your service with a simple query. You might notice that no matter the fields in the result set, the InfoTable will match it based on field type and field name.
There are two ways you can go from here. You can either query the database using services that call this service, or you can create more SQL command services that query the database directly. Let's go over each method next, starting with a service to call our helper.
5. Click Save and Continue.
Any parameter, especially those that were entered by users, that is being passed into an SQL Statement using the Database Connectors should be fully validated and sanitized before executing the statement! Failure to do so could result in the service becoming an SQL Injection vector.
Now, let's utilize the second method to create a query directly to the database. You can use open and close brackets to create parameters for your query. You can also use <> as a method to mark a value that will need to be replaced. As you build your query, use [[Parameter Name]] for parameters/variables substitution and <> for string substitution.
SELECT * FROM Persons WHERE person_email = [[email]];
5. Add the following parameter:
6. Click Save and Continue.
An example of using the string replacement is as follows:
DELETE FROM <> WHERE (FieldName = '[[MatchName]]'); DELETE FROM << TableName >> WHERE ( FieldName = [[MatchNumber]]);
Click here to view Part 2 of this guide.