Steps to connecting to your Relational Database

[#1]

Thingworx provides the capability to use JDBC to connect to Relational Databases.

What would be the steps to take?

1. Find the proper JDBC JAR file, this can be easily located by keeping in mind your database and its version and doing an online search.

2. Download the JDBC Extension Creator from the Marketplace

3. Follow the instructions to create the actual JDBC extension you will be using

4. Create a Thing based on the ThingTemplate from the JDBC extension - This represents your actual connection to the database

5. Set up the configuration:

     a. Connection String - Usually I use connectionstrings.com to find that

     b. Validation String - This has to be a VALID SQL statement within the context of the database you are connecting to (Like SELECT SYSDATE FROM DUAL for Oracle)

     c. Proper User Name and Password as defined in the database you are connecting to

6. SAVE

7. To check if you are properly connected, go back into Edit mode and go to Services, create a new SQL Query or Command and check Tables and Columns Tab. Actual Tables should show up now.

8. If it doesn't work, check your application log.

[vmihai]

Thank you for the great tutorial, Pai. I would like to add that you also need to be sure you have enabled access to your database from a remote address if you are connecting to it from an amazon hosted instance for example.

[PaiChung]

Just noticed I forgot to put the JDBC Driver Class Name ... this is automatically generated by the extension Generator I believe.

as for an example btw of a connection string (this one being MySQL):

jdbc:mysql://<ServerAddress>:<DatabaseListeningPort>/<DBName>?allowMultiQueries=true

[EmanusSimeon]

Database validationQuery notes

• hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
• Oracle - select 1 from dual
• DB2 - select 1 from sysibm.sysdummy1
• mysql - select 1
• microsoft SQL Server - select 1 (tested on SQL-Server 9.0, 10.5 [2008])
• postgresql - select 1
• ingres - select 1
• derby - values 1
• H2 - select 1
• Firebird - select 1 from rdb$database

Source: java - DBCP - validationQuery for different Databases - Stack Overflow

[timani]

Can we set setAutoCommit(false)  to use transaction like

BEGIN TRANSACTION

INSERT INTO AAA

UPDATE BBB

INSERT INTO CCC

COMMIT TRANSACTION

(table name and its query above is just a sample. The customer is using MS SQL server to be connected from ThingWorx.)

[BrunoD]

[2020-09-29 Important Update] Extension mentioned in step 2 is no longer available on the marketplace it has been moved to ptc support website in the download section of ThingWorx (link).

Setting up connectivity to a Relational Database can also be done without an extension as described in the following PTC support article "How to connect ThingWorx with an external database without extensions".