Establishing JDBC Connections

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é.

Note:

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);

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.*;
   

   

2. Load the driver, then use CacheDataSource to create the connection and specify username and password:

   try{
      CacheDataSource ds = new CacheDataSource();
      ds.setURL("jdbc:Cache://127.0.0.1:1972/Samples");
      ds.setUser("_system");
      ds.setPassword("SYS");
      Connection dbconnection = ds.getConnection();
   }
   

   
   Note:

   On some systems, Java may attempt to connect via IPv6 if you use localhost in the URL rather than the literal address, 127.0.0.1. 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()
   

   

4. Catch the exceptions thrown by the above methods:

   catch (SQLException e){
   System.out.println(e.getMessage());
   }
   catch (ClassNotFoundException e){
   System.out.println(e.getMessage());
   }
   

   

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://127.0.0.1:1972/SAMPLES";
   String  username = "_SYSTEM";
   String  password = "SYS";
   dbconnection = DriverManager.getConnection(url,username,password);

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

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

   java.util.Properties props = new Properties();
   props.put("user",username);
   props.put("password",password);
   java.sql.Connection dbconnection = drv.connect(url, props);

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 127.0.0.1 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:

   jdbc:Cache://<host>:<port>/<namespace>

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

   jdbc:Cache://127.0.0.1:1972/Samples

Optional Parameters

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

   jdbc:Cache://<host>:<port>/<namespace>/<logfile>:<eventclass>:<nodelay>:<ssl>

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:

   jdbc:Cache://127.0.0.1:1972/Samples/::false

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

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

   String myport = java.lang.System.getProperty ("com.intersys.port");

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:

   jdbc:Cache://<host>:<port>/<namespace>/<options>?username=<string1>&password=<string2>

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

   "jdbc:Cache://127.0.0.1:1972/Samples/::false?username=_SYSTEM&password=SYS"

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.

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

1. Import the needed packages:

   import com.intersys.jdbc.*;
   import java.sql.*;
   

   

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();
      pds.restartConnectionPool();
      pds.setURL("jdbc:Cache://127.0.0.1:1972/Samples");
      pds.setUser("_system");
      pds.setPassword("SYS");
   

   

3. Initially, getPoolCount returns 0.

   System.out.println(pds.getPoolCount()); //outputs 0.
   

   

4. Use CacheConnectionPoolDataSource.getConnection() to retrieve a database connection from the pool.

   Connection dbConnection = pds.getConnection();
   

   
   Caution:

   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.

   dbConnection.close();
   System.out.println(pds.getPoolCount()); //outputs 1
   

   

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:

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]);
      }
   }