Working with External Databases Part 1

Yes

GUIDE CONCEPT
Step 1: Completed Example
Step 2: Connect to External Database
Step 3: Query Data from External Database

Learn how to connect, query, insert, and handle data from an external database.

GUIDE CONCEPT

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

Connect ThingWorx to external databases
Query databases and and handling results
Insert data and running stored procedures

NOTE: The estimated time to complete this guide is 60 minutes.

Step 1: Completed Example

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:

Ensure the user you created has read, write, and execute permissions. If you would rather split roles, you can create multiple users to handle each aspect. The execute permission for stored procedures is not necessary.
Download the database driver/.jar file and move it to the lib directory of the Tomcat installation (ie, /lib).
Restart the Tomcat server to load the JDBC driver to make it available for the ThingWorx web application.

Step 2: Connect to External Database

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.

Create 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.
Set the Project field (ie, PTCDefaultProject).
Click Save and go to the Configurations tab.

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)

After entering credentials, click Save.
Go the Properties and Alerts tab.
You'll see properties showing the connection validation similar to the image below. If you don't have a connection as yet, work on your configuration information and validate the credentials being used. If you're still having troubles after that, see the examples section below or use the next section for help to try a query of the database.

Help and Troubleshooting

For help finding the correct configuration for you, check out these JDBC Configuration Examples or try out this Connection String Reference for help with your connection string.

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.

Step 3: Query Data from External Database

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.

Running a Query

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.

In the DatabaseController entity, go to the Services tab.
Create a new service of type SQL (Query) called RunDatabaseQuery.

3. Set the Output as InfoTable, but do not set the DataShape for the InfoTable.

4. Add the following parameter:

Name Base Type Required

query

String

True

5. Add the following code to the new service:

<<query>>

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.

In the Services tab of the DatabaseController entity, create a new service of type JavaScript.
Name the service JavaScriptQuery_PersonsTable.
Set the Output as InfoTable, but do not set the DataShape for the InfoTable.
Add the following code to your new service:

try {
    var query = "SELECT * FROM Persons";

    logger.debug("DatabaseController.JavaScriptQuery_PersonsTable(): Query - " + query);

    var result = me.RunDatabaseQuery({query:query});
} catch(error) {
    logger.error("DatabaseController.JavaScriptQuery_PersonsTable(): Error - " + error.message);
}

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.

This is a simple way to query the database since much of your development inside of ThingWorx was already based in JavaScript.

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.

In the Services tab of the DatabaseController entity, create a new service of type JavaScript.
Name the service SQLQuery_GetPersonByEmail.
Ensure the Output is InfoTable.
Add the following code to your new service:

SELECT * FROM Persons WHERE person_email = [[email]];

5. Add the following parameter:

Name Base Type Required

email