cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Want the oppurtunity to discuss enhancements to PTC products? Join a working group! X

Working with External Databases Part 1

100% helpful (1/1)

 

 

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
SQLServerDatabaseControllerA connected entity for use with an SQL Server databaseThingWorx Entity
OracleDatabaseControllerA connected entity for use with an Oracle databaseThingWorx Entity
AddStudentToCourse.sqlStored procedure to add students to a course in a SQL Server databaseDatabase Stored Procedure
GetStudentInCourse.sqlStored procedure to information about students in a course in SQL Server databaseDatabase Stored Procedure
Course.sqlSQL Server table representing CoursesDatabase Stored Procedure
Person.sqlSQL Server table representing StudentsDatabase Stored Procedure
PersonCourses.sqlSQL Server table representing the connection between Students and CoursesDatabase 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 DownloadSQL Server InstallationSQL Server Jar File
Oracle DownloadOracle InstallationOracle Jar File

 

 

Please follow the below steps to enable a connection between ThingWorx and your database of choice:

  1. 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.

  2. Download the database driver/.jar file and move it to the lib directory of the Tomcat installation (ie, /lib).

  3. 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.)

 

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

    ext1.png

     

  2. Select Thing in the dropdown.

    ext2.png

     

  3. Name the Thing DatabaseController and select Database as the base type.

  4. Set the Project field (ie, PTCDefaultProject).


    ext3.png

     

  5. 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.

ext4.png

 

 

 Title                                              Description                    Example
JDBC Driver Class NameThe 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 StringThe 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)
connectionValidationStringA simple query that should always work in a database.SELECT GetDate() (SQL Server) or SELECT SYSDATE FROM DUAL (Oracle)


  1. After entering credentials, click Save.

  2. Go the Properties and Alerts tab.

  3. 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.

    ext5.png

 

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.

  1. In the DatabaseController entity, go to the Services tab.

  2. Create a new service of type SQL (Query) called RunDatabaseQuery.

    ext6.png

 

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

4. Add the following parameter:

 Name            Base Type   Required
queryStringTrue

5. Add the following code to the new service:

<<query>>

6. Click Save and Continue. Your service signature should look like the below example.

ext7.png

 

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
emailStringTrue

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]]);

 

 

 

 


ext8.png

 

 

Click here to view Part 2 of this guide.

Version history
Last update:
‎Mar 07, 2023 02:59 PM
Updated by:
Labels (1)
Attachments
Contributors