ThingWorx Extensibility > JDBC Extensibility
  
JDBC Extensibility
Overview
ThingWorx can use JDBC drivers to connect to any JDBC compatible database (such as SQL Server, MySQL, Oracle, etc.). It can also be done using the ThingWorx Edge Microserver and the ThingWorx Host/Resource using OLEDB and ODBC if the Database happens to be behind a firewall.
If a JDBC Extension does not exist in ThingWorx for your Database, you can manually add the JDBC driver, by downloading the driver(s) and adding the files to the following location:/Tomcat folder/webapps/Thingworx/WEB-INF/lib
After copying the driver(s), over you must restart ThingWorx (or Tomcat if that is easier).
If you do not want to manually load the JDBC driver to Tomcat and have to restart Tomcat, you can build a Database Extension that includes the JDBC driver, and import that extension. The advantage of this approach is that you do not have to manually load the driver, the class load is handled for you, and it is upgrade safe, so you do not have to redeploy the driver when you update the ThingWorx web application. For more information on creating a Database extension, follow the steps below.
You can also view the following tutorial video on creating a JDBC extension in ThingWorx.
Creating a JDBC Extension for ThingWorx
1. Download the example metadata file on the PTC Marketplace.
2. Download the appropriate JDBC driver.
3. Build the extension structure by creating the directory lib/common
4. Place the JAR file in the following directory location: lib/common/<JDBC driver jar file>
5. Modify the name attribute of the ExtensionPackage entity in the metadata.xml file as necessary
.
6. Point the file attribute of the FileResource entity to the name of the JDBC JAR file.
7. The metadata also contains a Thing Template. The name is set to MySqlServer, but can be modified as needed.
8. Select the lib folder and metadata.xml file and send to a zip archive.
* 
The name of the zip archive should match the name given in the name attribute of the ExtensionPackage entity in the metadata.xml file.
9. Import the newly created extension.
10. To use the JDBC extension, create a new Thing and assign the new Thing Template that was imported with the JDBC extension.
Configuration Field Descriptions
Field Name
Description
JDBC Driver Class Name
Depends on the driver being used.
JDBC Connection String
Defines the information needed to establish a connection with the database. Connection string formatting can be found at connectionstrings.com.
ConnectionValidationString
A simple query that verifies return values from the database (regardless of table names to be executed).
Queries and Commands against the Database
Once you have the configuration set up, in Services you can create SQLQueries and SQLCommands to enact upon the Database you have connected to.
Passing Variables
As you build your Query, use [[Parameter Name]] for parameters/variables substitution and <<string replacement >> for string substitution.
DELETE FROM <> WHERE (FieldName = '[[MatchName]]');
DELETE FROM << TableName >> WHERE ( FieldName = [[MatchNumber]]);
* 
It is extremely DANGEROUS to use << ... >> string substitution, because it puts you at risk of SQL Injection. We recommend you use caution when considering this method of parameter passing. However, to create a very dynamic set of Queries, you need to pass in the Table names as << Name of the Table >>. Also, if you need to use the IN clause, your collection will need to be passed in with << Item1, Item2, Item3,>>