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

Steps to connecting to your Relational Database

Highlighted
Topaz I

Steps to connecting to your Relational Database

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.

Tags (1)
5 REPLIES 5
Highlighted

Re: Steps to connecting to your Relational Database

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.

Highlighted

Re: Steps to connecting to your Relational Database

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

Highlighted

Re: Steps to connecting to your Relational Database

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

Highlighted

Re: Steps to connecting to your Relational Database

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

Highlighted

Re: Steps to connecting to your Relational Database

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

Announcements