ThingWorx Model and Data Best Practices > Data Management > Connecting to an External Database
Connecting to an External Database
The ThingWorx Platform supports connecting to an external database through two entities, DatabaseThing and SQLThing. Both entities connect with a database using the Java Database Connectivity (JDBC) and require an appropriate JDBC driver be added to the ThingWorx Platform. A JDBC driver can be added via an extension or directly in the Apache Tomcat deployment.
The DatabaseThing is supported in the ThingWorx Platform for a while and continues to support database access for less complex requirements.
Recently, SQLThing is added to support sharing configuration and connection across multiple entities. When using an SQLThing, the configuration is managed by a PersistenceProvider created with the GenericJdbcPersistenceProviderPackage, where the JDBC connection details are configured. The created PersistenceProvider can then be referenced by name in the configuration of one or more SQLThing entities, sharing the pool of connections across all entities.
SQLThing can optionally participate in ThingWorx Platform transactions via a check-box in the SQLThing configuration. This support enables rolling-back changes to the external database when other actions of the same ThingWorx Platform transaction fail. This also causes multiple SQL services executed from the same JavaScript service to use a single transaction on the external database, which may be desired in some circumstances. Note that any other services which calls such a JavaScript wrapper are affected when the database changes are committed, since they share the same platform transaction.
Name
Default Value
Notes
JDBC URL
jdbc:_DB_://localhost/
The JDBC URL of the database from which connections should be acquired. You can specify multiple schemas in this URL.
Username
dbuser
User name used to acquire a database connection
Password
n/a
Password used to acquire a database connection
Initial Connection Pool Size
0
Number of threads created upon startup to connect to database
Connection Acquire Increment
1
Determines how many connections will be acquired when the pool is exhausted
Max Connection Pool Size
100
Maximum number of connections a pool maintains at any given time
Min Connection Pool Size
0
Minimum number of connections a pool maintains at any given time
Max Cached Statements
100
Size of global PreparedStatement cache
Driver Class
Database JDBC driver class
Acquire Retry Attempts
3
Defines how many times the connection pool will try to acquire a new connection
Acquire Retry Delay
10000
Time in milliseconds in which the connection pool will wait between acquire attempts
Checkout Retry Timeout
1000000
Number of milliseconds a client calling getConnection will wait for a connection to be checked in or acquired when the pool is exhausted
Pooled Connection Idle Re-Test Period (seconds)
60
Max Idle Time
0
Seconds a connection can remain pooled but unused before being discarded. Zero means idle connections never expire.
Max Connection Age
0
Connections older than this time, in seconds, will be destroyed and purged from the pool. Zero means no maximum age is enforced.
Number of Helper Threads
8
Slow JDBC operations are generally performed by helper threads that do not hold contended locks. Spreading these operations over multiple threads can significantly improve performance by allowing multiple operations to be performed simultaneously.
Unreturned Connection Timeout
0
If the application acquires a connection but failed to close it within the specified period of time, in seconds, the pool will destroy the connection. Zero means no timeout, and the applications are expected to close their own connections.
Max Idle Time for Excess Connections
300
Number of seconds that connections in excess of minPoolSize should be permitted to remain idle in the pool before being destroyed. Zero means no enforcement and excess connections are not destroyed.
Fetch Size While Loading All Entities
5000
SSL Connection Mode
disable
SSL connection be enabled or disabled
SSL root certificate path
Path to the SSL certificate if SSL Connection Mode is enabled
Connection Pool Saturation Warn Limit
85
Was this helpful?