JDBC Extension
The ThingWorx JDBC Extension provides a quick and easy solution to enable ThingWorx administrators to deploy standard JDBC drivers required to support third-party databases for use with a ThingWorx Database Thing. Database Things permit ThingWorx solution developers and architects to incorporate and take advantage of the functionality provided by a database within a solution deployed on ThingWorx.
Support Policy
To use the ThingWorx JDBC extension, administrators must supply the appropriate JDBC driver for the desired database. Although PTC has taken all appropriate measures to ensure the general functionality and security of the JDBC connector extension, PTC cannot verify that ThingWorx is compatible with any specific JDBC driver, nor will PTC process technical support requests related to such drivers or unsupported databases.
Additionally, PTC does not supply and cannot confirm the functionality or security of the third-party JDBC drivers needed to use the extension for unsupported persistence providers. PTC strongly recommends reviewing the extension security recommendations prior to using third-party drivers with the JDBC extension.
Finally, these aforementioned JDBC drivers are only for use with ThingWorx Database Things.
Overview
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), 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.
Creating a JDBC Extension for ThingWorx
1. Obtain the JDBC extension from support.ptc.com.
2. Refer to the example metadata file:
<Entities>
<ExtensionPackages>
<!-- The name attribute of the ExtensionPackage element correlates to the name of the zip
archive for the extension -->
<ExtensionPackage name="DatabaseXYZ_ExtensionPackage" description="DatabaseXYZ JDBC Extension" vendor="Acme Corporation" packageVersion="1.0" minimumThingWorxVersion="5.0.0">
<JarResources>
<!-- The JDBC JAR file being used to connect to DatabaseXYZ -->
<FileResource type="JAR" file="databaseXYZ.jdbc.jar" description="DatabaseXYZ JDBC JAR"/>
</JarResources>
</ExtensionPackage>
</ExtensionPackages>
<ThingTemplates>
<!-- A Database ThingTemplate for the DatabaseXYZ extension -->
<ThingTemplate name="DatabaseXYZ" baseThingTemplate="Database" description="DatabaseXYZ ThingTemplate"/>
</ThingTemplates>
</Entities>
<ExtensionPackages>
<!-- The name attribute of the ExtensionPackage element correlates to the name of the zip
archive for the extension -->
<ExtensionPackage name="DatabaseXYZ_ExtensionPackage" description="DatabaseXYZ JDBC Extension" vendor="Acme Corporation" packageVersion="1.0" minimumThingWorxVersion="5.0.0">
<JarResources>
<!-- The JDBC JAR file being used to connect to DatabaseXYZ -->
<FileResource type="JAR" file="databaseXYZ.jdbc.jar" description="DatabaseXYZ JDBC JAR"/>
</JarResources>
</ExtensionPackage>
</ExtensionPackages>
<ThingTemplates>
<!-- A Database ThingTemplate for the DatabaseXYZ extension -->
<ThingTemplate name="DatabaseXYZ" baseThingTemplate="Database" description="DatabaseXYZ ThingTemplate"/>
</ThingTemplates>
</Entities>
3. Download the appropriate JDBC driver.
4. Build the extension structure by creating the directory lib/common.
5. Place the JAR file in the following directory location: lib/common/<JDBC driver JAR file>.
6. Modify the name attribute of the ExtensionPackage entity in the metadata.xml file as necessary.
7. Point the file attribute of the FileResource entity to the name of the JDBC JAR file.
8. The metadata also contains a Thing Template. The name is set to MySqlServer, but can be modified as needed.
9. 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.
|
10. Import the newly created extension.
11. To use the JDBC extension, create a new Thing and assign the new Thing Template that was imported with the JDBC extension.
|
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.
Example
DELETE FROM <> WHERE (FieldName = '[[MatchName]]');
DELETE FROM << TableName >> WHERE ( FieldName = [[MatchNumber]]);
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 must 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,>>
|