Connection Pools

Database connection pools are a common way to improve the performance of database operations. They can be useful because creating a new connection can easily take several seconds to establish; this is a problem when the database operation is one that only needs a few milliseconds. DatabaseLink provides a connection pool mechanism built on top of the Apache Commons DBCP, http://jakarta.apache.org/commons/dbcp/index.html.

Working with Connection Pools

If you find that the examples in this tutorial do not work as shown, you may need to install or restore the example database with the package, as described in "Using the Example Databases".

To create a connection from a pool, you can set the option of OpenSQLConnection. Here is an example.

First, the DatabaseLink package is loaded. Then a connection using a pool is made to the publisher example database.

In[1]:=
Click for copyable input
Out[1]=

Instead of using the option, you could set the default value $SQLUseConnectionPool to True. When DatabaseLink loads it is False.

In[2]:=
Click for copyable input
Out[2]=
OpenSQLConnection[src,UseConnectionPool->True]
connect to a data source using a connection pool
$SQLUseConnectionPoolwhether to always use connection pools
SQLConnectionPools[]information on all active connection pools
SQLConnectionPools[conn]information on pool for connection conn
SetOptions[pool,opts]set options opts for connection pool pool
SQLConnectionPoolClose[pool]close the connection pool pool

Commands for working with connection pools.

This shows all the connection pools that have been created; there is only one.

In[3]:=
Click for copyable input
Out[3]=

This shows the connection pool used to connect to the publisher database. You can see some of the options that the connection pool is using.

In[4]:=
Click for copyable input
Out[4]=

This closes the connection pool, and it also closes any connections that the pool is using.

In[5]:=
Click for copyable input

Connection Pool Options

There are a number of options that control how the connection pool operates. This example shows how to work with them.

First, the DatabaseLink package is loaded. Then a connection using a pool is made to the publisher example database.

In[1]:=
Click for copyable input
Out[1]=

This shows all the connection pools that have been created; there is only one.

In[2]:=
Click for copyable input
Out[2]=

This shows the connection pool used to connect to the publisher database. You can see some of the options that the connection pool is using.

In[3]:=
Click for copyable input
Out[3]=

This sets the option of this connection pool.

In[4]:=
Click for copyable input
Out[4]=
In[5]:=
Click for copyable input
SQLConnectionPools[]information on all active connection pools
SQLConnectionPools[conn]information on pool for connection conn
SetOptions[pool,opts]set options opts for connection pool pool
CloseConnectionPool[pool]close the connection pool pool

Functions for working with connection pool options.

option name
"MaximumActiveConnections"maximum number of connections to keep in the pool
"MinimumIdleConnections"minimum number of idle connections to keep in the pool
"MaximumIdleConnections"maximum number of idle connections to keep in the pool
"Catalog"location of the database catalog
"ReadOnly"set the connection to be read only
"TransactionIsolationLevel"set transaction isolation for the connection

Connection pool options.

New to Mathematica? Find your learning path »
Have a question? Ask support »