Connecting External Databases and Model Part 1

[#1]

• GUIDE CONCEPT
• Step 1: Examples and Strategy
• Step 2: Database Connections
• Step 3: Query Data from External Database

Connect to an existing database and design a connected data model.

GUIDE CONCEPT

There are times you already have your database designed and only need to integrate the ThingWorx environment.

These concepts and steps will allow you to focus on development of your application while still allowing the ability to utilize the power of ThingWorx!

We will teach you how to create a data model around your database design and connect to that database.

YOU'LL LEARN HOW TO

• How to connect an external database and created services to be used with it
• How to design and implement a new data model based on an external resource
• Using data models with database services

Note: The estimated time to complete this guide is 30 minutes. 

Step 1: Examples and Strategy

If you’d like to skip ahead, download and unzip the completed example of the Aerospace and Defense learning path attached to this guide:  AerospaceEntitiesGuide1.zip.

By now, you likely know how to create a data model from scratch. You have likely already created services that work with Info Tables. What you might not have completed, is combining both a new data model, handling data in services, and connecting it all to an external database.

Our organization, PTC Defense Department, has existed for years and has an existing database setup. Developers in our organization refuse to remodel the database, so we must model the ThingWorx data model to our database schema. With ThingWorx, this is not a difficult task, but there are numerous decisions and options that we will explore in this guide.

Step 2: Database Connections

ThingWorx is based on the Java programming language and can make connections to any database that supports a Java-based connection. Dropping the JAR file for the database JDBC driver to the lib folder of Tomcat is all that is needed for connection to the ThingWorx Platform. Follow the below steps to get started creating the connection.

To establish the connection and begin working with an external database, you will need to create a Database Thing and configure the connection string and credentials. Let us start with our database connection. If you have not done so already, download the Aerospace and Defense database scripts: DatabaseApplication.zip. Use the README.txt file to create the database schema. It is based on Microsoft SQL Server, but you can configure the scripts to your database preferences.

NOTE: You will not need to connect to a database to utilize this guide as a learning utility. For your services to work, you will need to connect to a database.

1. In ThingWorx Composer, click the + New at the top-left of the screen.

2. Select Thing in the dropdown.

3. Name the Thing `DatabaseController.Facilities` and select Database as the Base Thing Template.

4.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 connection Validation String 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)

5. After entering credentials, click Save.

6. Go the Properties and Alerts tab.

The connected Property should be checked. This property informs us of the current connection to the database. The lastConnection Datetime Property should show a current timestamp. This property informs us of the last time there was a valid connection to the database. This is an easy way to confirm the connection to the database.

If you do not have a connection, work on your configurations in the Configurations tab and validate the credentials being used. If you are still having troubles, 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 have just established your first database connection! Now jump to the next section and let us begin to build a data model to match the database schema.

 

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.

1. In the DatabaseController entity, go to the Services tab.
2. 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.

1. In the Services tab of the DatabaseController entity, create a new service of type JavaScript.
2. Name the service JavaScriptQuery_PersonsTable.
3. Set the Output as InfoTable, but do not set the DataShape for the InfoTable.
4. 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.

1. In the Services tab of the DatabaseController entity, create a new service of type JavaScript.
2. Name the service SQLQuery_GetPersonByEmail.
3. Ensure the Output is InfoTable.
4. 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	String	True

      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.