Skip to main content

This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRISOpens in a new window, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions pageOpens in a new window (login required).

Establishing JDBC Connections

This chapter provides some detailed examples of Java code that uses the Caché JDBC driver to accomplish basic tasks.

Using CacheDataSource to Connect

Use com.intersys.jdbc.CacheDataSource to load the driver and then create the java.sql.Connection object. This is the preferred method for connecting to a database and is fully supported by Caché.


In earlier versions of JDBC, it was necessary to load the driver before connecting. For example:

   Class.forName ("com.intersys.jdbc.CacheDriver").newInstance();
   Connection dbconnection = DriverManager.getConnection(url,user,password);
Copy code to clipboard

JDBC versions 4.0 and higher use driver autoloading, which makes the Class.forName()call unnecessary for either DriverManager or CacheDataSource.

Here are the steps for using CacheDataSource:

  1. Import the java.sql and com.intersys.jdbc packages:

    import java.sql.*;
    import com.intersys.jdbc.*;
    Copy code to clipboard
  2. Load the driver, then use CacheDataSource to create the connection and specify username and password:

       CacheDataSource ds = new CacheDataSource();
       Connection dbconnection = ds.getConnection();
    Copy code to clipboard

    On some systems, Java may attempt to connect via IPv6 if you use localhost in the URL rather than the literal address, This applies on any system where the hostname resolves the same for IPv4 and IPv6.

  3. Create a java.sql.Statement using the connection. The Statement object can be used to execute queries.

    Statement stmt = dbconnection.createStatement()
    Copy code to clipboard
  4. Catch the exceptions thrown by the above methods:

    catch (SQLException e){
    catch (ClassNotFoundException e){
    Copy code to clipboard

Using DriverManager to Connect

The standard DriverManager class can also be used to create a connection. The following code demonstrates one possible way to do so:

   Class.forName ("com.intersys.jdbc.CacheDriver").newInstance();
   String  url="jdbc:Cache://";
   String  username = "_SYSTEM";
   String  password = "SYS";
   dbconnection = DriverManager.getConnection(url,username,password);
Copy code to clipboard

You can also pass connection properties to DriverManager in a Properties object, as demonstrated in the following code:

   String  url="jdbc:Cache://";
   java.sql.Driver drv = java.sql.DriverManager.getDriver(url);

   java.util.Properties props = new Properties();
   java.sql.Connection dbconnection = drv.connect(url, props);
Copy code to clipboard

See Caché JDBC Connection Properties for a complete list of the properties used by the Caché JDBC driver.

Defining a JDBC Connection URL

A java.sql.Connection URL supplies the connection with information about the host address, port number, and namespace to be accessed. The Caché JDBC driver also allows you to use several optional parameters.

Required Parameters

The URL specifies the host address, port number, and namespace to be accessed.

host IP address or Fully Qualified Domain Name (FQDN). For example, both and localhost indicate the local machine.
port TCP port number on which the Caché SuperServer is listening. The default is 1972 (or the first available number higher than 56772 if more than one instance of Caché is installed — see DefaultPort in the Caché Parameter File Reference).
namespace Caché namespace to be accessed. For example, Samples is the namespace containing Caché sample programs.

The minimal required URL syntax is:

Copy code to clipboard

For example, the following URL specifies host as, port as 1972, and namespace as Samples:

Copy code to clipboard

Optional Parameters

The Caché JDBC driver also allows you to specify several optional parameters. The full syntax is:

Copy code to clipboard

where the optional parameters are defined as follows:

logfile specifies a JDBC log file (see “Enabling Logging for JDBC”).
eventclass sets the transaction Event Class for this CacheDataSource object. See the CacheDataSource setEventClass() method for a complete description.
nodelay sets the TCP_NODELAY option if connecting via a CacheDataSource object. Toggling this flag can affect the performance of the application. Valid values are true and false. If not set, it defaults to true. Also see the getNodelay() and setNodelay() methods of CacheDataSource.
ssl enables SSL/TLS for both CacheDriver and CacheDataSource (see “Using SSL/TLS with Caché” in the Caché Security Administration Guide). Valid values are true and false. If not set, it defaults to false.

Each of these optional parameters can be defined individually, without setting the others. For example, the following URL sets only the required parameters and the nodelay option:

Copy code to clipboard

Setting the Port Parameter at the Command Line

The com.intersys.port property can be used to set the port parameter of the URL at the command line. Even if a program hard-codes the port number in the connection string, it can be changed in the command line. For example, assume that program myJdbcProgram sets the port to 1972 in a hard-coded connection string. The following command line will still allow it to run on port 9523:

   java -cp .:../lib/cache-db-2.0.0.jar -Dcom.intersys.port=9523 myJdbcProgram
Copy code to clipboard

The current value of this property can be retrieved programmatically with the following code:

   String myport = java.lang.System.getProperty ("com.intersys.port");
Copy code to clipboard

Alternate Username and Password Parameters

For the preferred ways to specify username and password, see “Using CacheDataSource to Connect” and “Using DriverManager to Connect” at the beginning of this chapter. However, it is also possible to specify the username and password in the URL string, although this is discouraged.

If password and username are supplied as part of the URL string, they will be used in order to connect. Otherwise, other mechanisms already in place will be invoked. The syntax is:

Copy code to clipboard

For example, the following URL string sets the required parameters, the nodelay option, and then the username and password:

Copy code to clipboard

The username and password strings are case-sensitive.

Using a Connection Pool

The com.intersys.jdbc.CacheConnectionPoolDataSource class implements the javax.sql.ConnectionPoolDataSource interface, providing a connection pool for your Java client applications.


This implementation is intended only for testing and development. It should not be used in production.

Here are the steps for using a connection pool with Caché:

  1. Import the needed packages:

    import com.intersys.jdbc.*;
    import java.sql.*;
    Copy code to clipboard
  2. Instantiate a CacheConnectionPoolDataSource object. Use the reStart() method to close all of the physical connections and empty the pool. Use setURL() to set the database URL (see Defining a JDBC Connection URL) for the pool's connections.

    CacheConnectionPoolDataSource pds = new CacheConnectionPoolDataSource();
    Copy code to clipboard
  3. Initially, getPoolCount returns 0.

    System.out.println(pds.getPoolCount()); //outputs 0.
    Copy code to clipboard
  4. Use CacheConnectionPoolDataSource.getConnection() to retrieve a database connection from the pool.

    Connection dbConnection = pds.getConnection();
    Copy code to clipboard

    Caché driver connections must always be obtained by calling the getConnection() method (inherited from CacheDataSource). Do not use the getPooledConnection() methods, which are for use only within the Caché driver.

  5. Close the connection. Now getPoolCount returns 1.

    System.out.println(pds.getPoolCount()); //outputs 1
    Copy code to clipboard

Caché JDBC Connection Properties

The Caché JDBC driver supports several connection properties, which can be set by passing them to DriverManager (as described in Using DriverManager to Connect).

The following properties are supported:

user Required. String indicating Username. Default = ""
password Required. String indicating Password. Default = ""
TCP_NODELAY Optional. Boolean indicating TCP/IP NoDelay Flag. Default = true.
SO_SNDBUF Optional. Integer indicating TCP/IP SO_SNDBUF value (SendBufferSize). Default = 0 (use system default value).
SO_RCVBUF Optional. Integer indicating TCP/IP SO_RCVBUF value(ReceiveBufferSize). Default = 0 (use system default value).
TransactionIsolationLevel Optional. java.sql.Connection constant indicating Transaction Isolation Level. Valid values are TRANSACTION_READ_UNCOMMITTED (the default) or TRANSACTION_READ_COMMITTED.
service principal Optional. String indicating Service Principal Name. Default = null.
connection security level Optional. Integer indicating Connection Security Level. Valid levels are 0, 1, 2, 3, or 10. Default = 0.
0 - Caché login (Password)
1 - Kerberos (authentication only)
2 - Kerberos with Packet Integrity
3 - Kerberos with Encryption
10 - SSL/TLS

Listing Connection Properties

Code similar to the following can be used to list the available properties for any compliant JDBC driver:

   java.sql.Driver drv = java.sql.DriverManager.getDriver(url);
   java.sql.Connection dbconnection = drv.connect(url, user, password);
   java.sql.DatabaseMetaData meta = dbconnection.getMetaData();

   System.out.println ("\n\n\nDriver Info: ==========================");
   System.out.println (meta.getDriverName());
   System.out.println ("release " + meta.getDriverVersion() + "\n");

   java.util.Properties props = new Properties();
   DriverPropertyInfo[] info = drv.getPropertyInfo(url,props);
   for(int i = 0; i < info.length; i++) {
      System.out.println ("\n" + info[i].name);
      if (info[i].required) {System.out.print ("   Required");}
         else {System.out.print ("   Optional");}
      System.out.println (", default = " + info[i].value);
      if (info[i].description != null) 
         System.out.println ("   Description: " + info[i].description);
      if (info[i].choices != null) {
         System.out.println ("   Valid values: ");
         for(int j = 0; j < info[i].choices.length; j++)
            System.out.println ("      " + info[i].choices[j]);
Copy code to clipboard
FeedbackOpens in a new window