Skip to content

Database Connection Pools

Database connections allow database servers and software to interact with each other in an efficient manner. Of course, this statement still holds true in TORO Integrate's environment.

Using registered database connection pools, services will be able communicate with your databases and perform operations against them. You're also free to remove and update existing pools, as well as enable or disable them.

More about SQL services...

To learn more about how to execute SQL operations in Gloop, or Groovy Services, see Gloop SQL Services and SqlMethods. Don't forget to check the examples package for examples as well!

Driver Support

TORO Integrate uses JDBC drivers to connect to certain databases. The table below describes which drivers are available in Java and should be compatible with TORO Integrate:

Driver Name Built-in Driver XA Support
com.ashna.jturbo.driver.Driver Microsoft SQL Server (JTurbo Driver) No Emulated
COM.cloudscape.core.JDBCDriver Cloudscape No Natively
com.ibm.db2.jcc.DB2Driver IBM DB2 (jcc4) No Natively
com.inet.tds.TdsDriver Microsoft SQL Server (Sprinta Driver) No Emulated
com.informix.jdbc.IfxDriver Informix Dynamic Server No Natively
com.microsoft.sqlserver.jdbc.SQLServerDriver Microsoft SQL Server Yes Natively
com.mysql.cj.jdbc.Driver MySQL Yes Natively
com.pointbase.jdbc.jdbcUniversalDriver PointBase Embedded Server No Emulated
com.sybase.jdbc.SybDriver Sybase (jConnect 4.2 and earlier) No Emulated
com.sybase.jdbc2.jdbc.SybDriver Sybase (jConnect 5.2) No Emulated
hSql.hDriver Hypersonic SQL (v1.2 and earlier) No Emulated
ids.sql.IDSDriver IDS Server No Emulated
interbase.interclient.Driver Interbase No Emulated
jdbc.idbDriver InstantDB (v3.13 and earlier) No Emulated
net.sourceforge.jtds.jdbc.Driver jTDS (Sybase), jTDS (SQL Server) Yes Natively
oracle.jdbc.driver.OracleDriver Oracle OCI 8, Oracle OCI 9+, Oracle Thin No Natively
org.apache.derby.jdbc.ClientDriver Derby Remote No Emulated
org.apache.derby.jdbc.EmbeddedDriver Derby Embedded No Natively
org.enhydra.instantdb.jdbc.idbDriver InstantDB (v3.14 and later) No Natively
org.firebirdsql.jdbc.FBDriver Firebird No Natively
org.gjt.mm.mysql.Driver MySQL (mm.MySQL Driver) No Emulated
org.h2.Driver h2 No Natively
org.hsql.jdbcDriver Hypersonic SQL (v1.3 and later) No Emulated
org.hsqldb.jdbc.JDBCDriver Hypersonic SQL (v2.0+ Remote), Hypersonic SQL (v2.0+ File) Yes Natively
org.postgresql.Driver PostgreSQL (v7.0 and later) Yes Natively on PostgreSQL 8 only
postgresql.Driver PostgreSQL (v6.5 and earlier) No Emulated
RmiJdbc.RJDriver Cloudscape RMI No Emulated
sun.jdbc.odbc.JdbcOdbcDriver JDBC-ODBC Bridge No Emulated

In addition to the drivers above, TORO Integrate also supports the installation of other database drivers. For more information, please read about maintaining database connection pools. To use XA transactions, the JDBC driver must implement XADataSource.

Check your license!

The availability of database drivers will depend on the type of license you jave installed.

Database Properties

The properties below are used to described database connections. TORO Integrate, which uses Bitronix underneath, will use these configuration fields when connecting to the target database. You can learn more about these properties in Bitronix's JDBC Configuration Pools page.

Field Name Required Default Value Description
Name true null Name of the database connection.
Use XA false false Whether or not to enable XA transactions.
Driver Class true null The JDBC driver for the database you are connecting to.
URL true null The URL of the JDBC database you are connecting to.
Username true null Username to use when connecting to the database.
Password false null Password to use when connecting to the database.
Acquire Increment false 1 Controls how many connections are filled into the pool when the pool is empty but the maxPoolSize has not been reached.
Acquisition Interval false 1 Controls how long (in seconds) the pool waits between attempts to create new connections.
Acquisition Timeout false 30 Controls how long (in seconds) to wait for a pool connection request to succeed before being aborted (and throwing an exception).
Allow Local Transactions false true Whether or not you want to be able to run SQL statements outside of XA transactions scope.
Apply XA Transaction Timeout false false Whether or not the transaction timeout will be passed to the resource via XAResource#setTransactionTimeout(int).
Enlist to XA Automatically false true Controls whether connections from the PoolingDataSource are automatically enlisted or delisted in XA transactions.
Defer Connection Release false false Set this parameter to false if the vendor's XADataSource implementation supports the interleaving of transactions.
Enable JDBC4 Connection Test false false If your JDBC driver supports JDBC4, this method of testing the connection is likely much more efficient than using the testQuery parameter.
Ignore Recovery Failures false false Determines whether recovery errors should be ignored or not. This is useful in development environments.
Maximum Idle Time false 600 When the pool is above the minPoolSize, this parameter controls how long (in seconds) an idle connection will stay in the pool before being retired.
Isolation Level false READ_COMMITED Sets the default isolation level. All of the four standard values (READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ and SERIALIZABLE) are supported.
Minimum Pool Size false 1 The minimum number of active connections the pool will try to keep.
Maximum Pool Size false 5 The maximum number of active connections allowed in the pool.
Prepared Statements Cache Size false 100 Controls how many prepared statements are cached (per connection) by Bitronix.
Share Transaction Connections false true Determines whether or not connections share the same thread context.
Test Query false null In contrast with enableJdbc4ConnectionTest, this parameter is sent to the database to determine whether or not a connection is still usable.
Two-Phase Commit Order Position false 0 Controls the position of this resource during the 2PC protocol execution; used to guarantee the order of commits.
Unique Name false "Name" Required for crash recovery; uses "Name" if empty.
Use Transaction Manager Join false true Set this parameter to false if the vendor's XADataSource implementation does not implement XAResource#isSameRM(...) properly.

Status

A database connection's status indicates the current state of the database connection. It is manually set by starting or stopping a database connection. Certain actions would only be available on certain states; for example, you may configure the properties of a database connection only if it stopped.

The following are the different possible connection statuses:

  • STARTED

    Indicates that there is an established connection between TORO Integrate and the database. Connectivity is required if any of your integrations need to use the database. A database connection with this status is shown with a green visual indicator in the UI.

  • STOPPED

    Indicates that there is no established connection between TORO Integrate and the database. A database connection with this status is shown with a red visual indicator in the UI.

As Seen in Coder

To view the existing database connection pools of your instance, open the Coder Navigator view, navigate to your instance's entry, and under your entry, expand the Databases section.

Databases in *Coder Navigator*

Databases in *Coder Navigator*

To perform an action on a database connection pool, right click its entry and select from the list of actions in the context menu that will appear. Depending on the state of your pool, some actions may or may not be executable.

Database context menu

Database context menu

Managing Database Connections

Aside from Coder, you can alternatively use the CLI tool or TORO Integrate's REST API to manage database connection pools.