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

Community Tip - Did you get called away in the middle of writing a post? Don't worry you can find your unfinished post later in the Drafts section of your profile page. X

Steps to connecting to your Relational Database

No ratings

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.

Comments

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.

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

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

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

[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".

Version history
Last update:
‎Feb 04, 2016 07:04 AM
Updated by:
Labels (1)